AUNT AUGUSTA's
Problem Page for
troubled Oracle
D B As

November 26 1999

Dear Aunt Augusta,

I am writing a script to daily purge data more than two days old but two WORKING DAYS old. In other words, I want to purge on Wednesday night what was completed on the preceding Monday, keeping Tuesday and Wednesday, but on Monday night what was completed on the preceding Thursday keeping Friday’s work (fortunately we ignore national days and the like).
This in itself would not be much of a concern, if my procedure was not supposed to run in a number of countries and I don’t know what the language setting will be, which prevents me from using a DECODE() on the name of the day. I have of course thought of using the number of the day in the week, but the Oracle documentation states that it depends on the NLS_TERRITORY setting, so it’s just as hazardous as testing on the name of the day. Is there a way to write something which is sure to work even in the remotest of islands and with the most exotic NLS settings without forcing my procedure to run in a specific operating system environment in which NLS_LANG for instance is forced to some predefined value ?

Global DBA

Dear Global DBA,

You have basically two possibilities to force your NLS environment. One is to use an ALTER SESSION SET NLS_LANGUAGE = AMERICAN (as Professor Higgins justly remarks in My Fair Lady, America is a place where they haven’t been using English for years) or the equivalent dbms_session.set_nls(‘NLS_LANGUAGE’, ‘AMERICAN’) in a PL/SQL procedure.

This being done, there is no problem writing, since American day names strangely resemble English ones, a condition such as :

    where completion_date <=
            decode(to_char(sysdate, 'day'),
                   'monday', trunc(sysdate - 3),
                   'tuesday', trunc(sysdate - 3),
                   trunc(sysdate - 1))

But in fact you do not even need the preliminary ALTER SESSION statement, since the TO_CHAR function can take an additional parameter to force the language and you can directly write :

    where completion_date <=
            decode(to_char(sysdate, 'day',
                   'NLS_DATE_LANGUAGE = AMERICAN'),
                   'monday', trunc(sysdate - 3),
                   'tuesday', trunc(sysdate - 3),
                   trunc(sysdate - 1))

Yours sincerely,

~~~~~~~~~~~~

August 30 1999

Dear Aunt Augusta :

I need to consolidate data from several tables through UNIONs, and, in particular, I need to know how many lines I have globally in three tables.
If I write :

       select count(*) from T1
       union
       select count(*) from T2
       union
       select count(*) from T3

I get three separate lines and still have to reach for my pocket calculator to do the sum. The obvious solution is to create the view V as

       select * from T1
       union
       select * from T2
       union
       select * from T3

 
       select count(*) from V

Mary-Ann W.

Dear Mary-Ann,

It is so difficult to achieve a successful UNION, isn't it. I know a lot of people who have trouble with theirs. I think that a reasonably efficient way to get your result would be to write :

     select sum(total)
     from (select count(*) total from T1
           union all
           select count(*) from T2
           union all
           select count(*) from T3)

The 'select in a from' feature is available from Oracle 7.2 and is something very useful to avoid views. As I was explaining the other day to Algie, my nephew, views are nice but they are supposed to be compiled, and, especially with the statistic optimiser, the execution path chosen at compilation time may not happen to be the best one later, when volumes have changed. When of course the statements are not too complicated interpretation is sometimes safer than compilation.
Note that I have used UNION ALL, and not UNION. The reason is that, as you must know, UNION eliminates duplicates.
If you happen to have 500 rows in both T1 and T2 and 600 in T3, UNION without ALL would eliminate one of the 500's and the query would yield 1100 instead of the correct 1600. On the other hand, if you have identical rows in say T2 and T3, you must understand that with the query above they will be counted once in T2 and once in T3, whereas if you were counting from the view you suggested, duplicates would have been eliminated and therefore you would get a lower row count. It really depends on your application and what should be considered the true, correct result. In any case, you always have the possibility of cheating (I am feeling quite ashamed of giving such an advice), keeping the view virtual by putting its text in the FROM clause :

     select count(*)
     from (select * from T1
           union
           select * from T2
           union
           select * from T3)

Yours sincerely,

~~~~~~~~~~~~

July 5 1999

Dear Aunt Augusta:

This is the scenario. I have to create two tablespaces, create users, grant privileges and import the data from a *.dmp file. By mistake one tablespace for index is not created but I imported the file. Now I want to create the tablespace and the datafile and move all the indexes there. How can I do that.? Can you help me? Thank you,

Kasi S.

Dear Kasi,

I am so glad to discover I am not alone in forgetting things! Life with Oracle is so frustrating, with all those little details continually putting you in embarrassing situations. Well, I am certain that if my dear late brother Wilberforce, were still with us, the young whippersnapper wouldn't have been able to resist a joking comparison with women.
Anyway, quite obviously you have experienced the well know imp behaviour 'Let's try to create it in the original tablespace ...oh dear, no such tablespace? default tablespace then!'. What you can do, besides doing starting all over again is

1) first use your existing .dmp file to create a file which contains all the 'CREATE INDEX' statements. This is done as follows :

    imp username/password file=yourfile.dmp rows=N indexfile=crindex.sql

          set pause off
          set echo off
          set pagesize 0
          set feedback off
          set recsep off
          spool considx.sql
          --
          --  First disable all foreign keys
          --
          select 'alter table ' || table_name || chr(10) ||
                 'disable constraint ' || constraint_name || ';'
          from user_constraints
          where constraint_type = 'R';
          --
          --  Then disable primary key and unique constraints.
          --  This drops the associated index.
          -- 
          select 'alter table ' || table_name || chr(10) ||
                 'disable constraint ' || constraint_name || ';'
          from user_constraints
          where constraint_type in ('P', 'U');
          --
          --  Re-enable the constraints, recreating the associated
          --  index in the suitable tablespace, eg TSIDX
          --
          select 'alter table ' || c.table_name || chr(10) ||
                 'enable constraint ' || c.constraint_name || chr(10) ||
                 'using index' || chr(10) ||
                 'tablespace TSIDX' || chr(10) ||
                 'storage (initial ' || to_char(i.initial_extent/1024)
                                     || 'K' || chr(10) ||
                 '         next ' || to_char(i.initial_extent/1024/5)
                                     || 'K' || chr(10) ||
                 '         pctincrease 0);'
          from user_constraints c,
               user_indexes i
          where c.constraint_type in ('P', 'U')
            and i.index_name = c.constraint_name;
          --
          --  You can now reenable all foreign keys
          --
          select 'alter table ' || table_name || chr(10) ||
                 'enable constraint ' || constraint_name || ';'
          from user_constraints
          where constraint_type = 'R';
          --
          --  Now drop all the remaining indexes ('normal'
          --  indexes) in the wrong tablespace
          --
          select 'drop index ' || index_name || ';'
          from user_indexes
          where tablespace_name != 'TSIDX';
          spool off
          set feedback on
 

After which you should normally be able to take a well deserved rest and a nice cup of tea.

Yours sincerely,

~~~~~~~~~~~~

May 7 1999

Dear Aunt Augusta,

I am a coward. With my databases, I usually let the situation deteriorate until I have no choice but to act, and, overall, I manage to get by. Not ideal, but bearable. Unfortunately, the other day, a table reached its maximum number of extents. What I wanted to do was export it, drop it and reimport it, to reorganize it. I finally managed to do it, but with much more difficulty than I was expecting - because of constraints, as usual.
Before dropping the table, I disabled all foreign keys referencing the table - but nonetheless, I couldn't drop the table! I still had a puzzling message talking about referencing foreign keys. I had to drop a lot of constraints. By luck I had the scripts to recreate the constraints, so I managed to rebuild everything more or less properly in the end, but the whole mess took the best part of an afternoon, with users complaining. Why these error messages? Was there a better way to do it ?

Regards,

Laid-back DBA

Dear laid-back DBA,

I am glad you are conscious of your own shortcomings; it is at least a step in the right direction. How do you want to improve yourself otherwise? Sir Reginald, Mabel Waymarsh's husband, was just telling us the other day how disappointed he had been, when he was still something in the Foreign Office, by the natives in some awful place, Samarkand or Baltimore, I have forgotten which, who were totally blind to the beauties of cricket and absolutely clung to their own terrible games. If people do not want to see, it's pointless to bring them the light.
Anyway, I think that you don't fully realise that, what you know as the data definition language (DDL) (the CREATE, DROP and ALTER statements, mainly) is just a layer to hide what is nothing more than actual data manipulation statements (INSERTs, DELETEs and UPDATEs) applied to the data dictionary tables - usually a single DDL statement hides a lot of operations. For instance, when you create a table, you are going to create a new object, hence an insert in the table of objects, which is a table, hence an insert in the table of tables, which has columns, hence several inserts in the table of columns, and which must be stored somewhere, which means you have to update the tables of segments, of extents and of free extents as well - not to mention integrity constraints if some were defined too.
The Oracle kernel doesn't use the identifiers you know most of the time, but rather numerical identifiers it generates as needed; and although no integrity constraint as you know them, is implemented in the data dictionary, the kernel code does all the consistency checks.
Now, when you disable a constraint, you use ALTER which is an UPDATE on the dictionary tables. In other words, the constraint is flagged as disabled, but still exists. And most obviously, it references the table. This why you cannot DROP (a DELETE ...) the table, because even if it is disabled, the constraint is still there and you have a typical occurrence of 'child key found'.
This is why what you should have used is a TRUNCATE, which is more of an UPDATE too, on the dictionary tables - the table is emptied (which means that all foreign keys which reference the table must have previously been disabled) but the table itself remains, and so can be 'referenced' by the disabled constraints - or grants; in fact you have two levels of references, this is what must be understood, one for the contents (the rows), and one for the containers (the tables). Once you have reloaded your data, integrity constraints can be enabled and everything is back in order without having to drop or create anything.

Watch your databases.

Yours sincerely,

~~~~~~~~~~~~

March 20 1999

Dear Aunt Augusta

I have a big problem. My production database, which I thought to be pure and innocent, has been compromised. What happened is that a major bug was discovered in our main application, which the development people could not reproduce in the test database, a rather outdated copy of a database which is very active. As a result, and I am quite ashamed to have to say it, they did a lot of tests on our production database, creating dummy rows almost everywhere. Guess who was then asked to clean-up the database ? Exactly. Well, I absolutely refuse to be dragged to the functional side of things, and if I know which are the most important tables, some relationships are too subtle for me. When I tried to delete some obviously test rows in one of the main tables, I had complaints about existing referencing rows; tried to hunt these, complaints again when trying to delete them (we have a rather complicated network of constraints). I tried to disable some constraints, but it wasn't much better, because in a few cases I couldn't re-enable them. Help! What can I do?

A constraint hater.

Dear constraint hater,

I fully agree with you, carrying out tests in the production database is certainly not the best way to proceed. It reminds me of what I often say to Charlotte :"Charlotte, instead of trying regularly to poison me with your awful cake recipes, why don't you test them at one of our little parish social events first?". People are just so irresponsible.
On the other hand, perhaps they think that the only way to update their test database is a full exp/imp. Perhaps you could tell them about pdqout/SQL*Loader.
Anyway, it happens that those nice Oriole people have recently released a free package which in my view solves your problem perfectly, a package named 'oriclean' which you will find in their quaintly named X-rated section (nice, experienced people, but a questionable sense of humour, if you want my opinion). It is true that I would not advise a beginner to use it, but I have a feeling you are rather experienced yourself. Basically, the package contains two procedures, one which deletes rows (the 'WHERE' clause is one of the arguments), and the other one which truncates tables. Those two procedures follow the full chain of referential integrity constraints and delete (or truncate) recursively. For instance, assume that you have a dummy 'TEST CLIENT' entry in your CLIENTS table, by deleting this entry you will delete all referencing lines in ORDERS, which will trigger in turn the deletion of a number of lines in ORDER_DETAIL and so on. The trick is to choose correctly your entry point, I mean the table(s) in which you explicitly require rows to be deleted. Beware that if you make a mistake, you can delete many more lines than expected, this is why, by default, the procedures only output how many lines they would delete in each table.
The procedure to truncate a table is much simpler than the one to delete rows (if you have some interest in PL/SQL, I am sure you will enjoy reading the code) and can be quite useful just before putting a database into production, to remove any trace of all the hanky-pankying of validation teams while leaving all true reference tables untouched.

Yours sincerely,

~~~~~~~~~~~~

March 15 1999

Dear Aunt Augusta

I just don't know how to communicate with Oracle. When I say something to it, sometimes it's just fine, and the next day, when I express myself barely any differently, it just seems to infuriate the optimizer and the whole system seems to dig in its heels. Why is it so complicated ? All, I want to do is to return the list of customers who have bought a given product from us. How must I couch my request so that I won't upset it ?

Bewildered Mildred

Dear Mildred (or may I call you Millie ?),

Your problem is so reminiscent of the problem I sometimes had with my dear late brother, particularly in the Monsoon season. Somedays the most innocuous remark would have him reaching for his gun and off he would stamp, dogs at his heels to shoot some poor unfortunate tiger. You certainly have to learn the best way to talk to Oracle; but in fact you must also learn to listen. All relationships require a little give and take. What you sometimes perceive as puzzling slowness is just that the old system hasn't much choice and is doing its best with the information you've provided.

Let's assume that you want to return the names of customers who have ordered one of the articles you sell, an article the code of which is 'DEF1726'.
The most obvious way to write the query is a straight join such as :

SELECT  c.customer_name
FROM    customers c,
        orders o
WHERE   o.product_id='DEF1726'
  AND   c.customer_id=o.customer_id

If product_id is indexed in ORDERS, it is likely that Oracle will scan the index to find the addresses of blocks in ORDERS which refer to this product_id, then will fetch each block, get customer_id and use the (presumably) primary key to retrieve the customer name.
This is wonderful if buying product DEF1726 is a once-in-a-lifetime treat. You will have more problems if it is, say, a printer toner cartridge which is likely to be a recurring purchase (or if customers are so enthused with your product that they buy many of them as presents for every social event).
In that case, the same customer name could be repeated several times and you may very well be tempted to write :

SELECT  DISTINCT c.customer_name
FROM    customers c,
        orders o
WHERE   o.product_id='DEF1726'
  AND   c.customer_id=o.customer_id

to eliminate duplicates. Yield not to temptation. By doing so, you will add a sort to the processing of your query. If you happen to return a large number of rows, it may become very costly indeed and moreover, if you try to plan ahead, the response times will increase faster than the number of rows to be processed - so it will get slower and slower over time.

Rather than a DISTINCT on the result of a join, you should contemplate subqueries, either correlated or not.

The un-correlated subquery would be :

SELECT  c.customer_name
FROM    customers c
WHERE   c.customer_id in (select o.customer_id
                          from orders o
                          WHERE   o.product_id='DEF1726')

and the correlated subquery :

SELECT  c.customer_name
FROM    customers c
WHERE   exists (SELECT 'x'
                FROM  orders o
                WHERE o.product_id='DEF1726'
                  AND o.customer_id=c.customer_id)

What will happen? When you have a correlated subquery the result of the (nested) subquery depends on the value for each row of the outer query. In other words (unless a number of other factors have induced Oracle to process the query differently from the way you have written it), Oracle has no other choice but to scan the CUSTOMERS table and, for each row, check if we find the relevant line in ORDERS. The ideal case would then be to have a concatenated index on product_id and customer_id, because we could then check the existence of the row just by consulting the index (otherwise we have to check the table block pointed to by the index).
With the un-correlated subquery, the reverse is true : the inner query must be processed first to be able to process the outer query. But then, we shall be able to use the primary key on CUSTOMERS.

So, how must we write the query? I shall give the easy answer : it all depends.

It depends on the selectivity of your index on product_id in ORDERS. If your product is enormously popular, most of your customers will have bought it and then the index on the product_id will not be particularly selective - unless the average shopping-cart contains a huge number of items.
Assume that your popular product appears in 15% of your orders, in the un-correlated subquery case, the search in ORDERS on the product_id may prove rather costly, because an index search is usually slower than a direct scan of the table when you return more than 10% of the rows. This means that in fact we would get the list of customer ids faster by dropping or deactivating the index on the product id in this case, and scanning ORDERS. It is likely than CUSTOMERS will contain fewer lines than ORDERS, and therefore scanning CUSTOMERS (which is what we do in the query with the correlated subquery) should be the fastest.
In other words, if the product is bought often, then the correlated subquery will probably be much more efficient than the un-correlated subquery (assuming the existence of the concatenated index).
On the other hand, if your product is only bought by a handful of eccentrics, then the un- correlated subquery will be very efficient and will, moreover, return few rows. In that case, it will be much better than the correlated subquery. To be honest, one must also say that the SELECT DISTINCT with a join would also give quite decent results in this case as the number of rows to be sorted would be rather trivial.

Yours truly,

~~~~~~~~~~~~

February 14 1999

Dear Aunt Augusta :

I want to write an SQL statement to give me the top 10 customers by spending. My first thought is to write

         SELECT cust_id, amt
         FROM cust_table
         WHERE ROWNUM < 11
         ORDER BY amt
 

Then I thought about embedding one SELECT into another with the inner one having the ORDER BY clause. that is

 
        SELECT cust_id, amt
        FROM (SELECT cust_id, amt FROM cust_table  ORDER BY amt)
        WHERE ROWNUM < 11

So, what can I do?

Thanks,

John M., Atlanta, GA

Dear John,

Oracle can be so tiresome. Your problem has been a problem which has confronted Oracle programmers for years and unfortunately there has never been a totally satisfying solution. As you observed the rownum is computed when the row is returned, before the sort, and ORDER BY in a view or in a nested query in a FROM clause is not supported by Oracle (at least not before Oracle 8.1)
I was having tea with my dear friend Charlotte Bracknell and the subject of your little problem came up. She was quite enthralled by it and chanced to remark that if a value was to be one of the top ten values it would also follow that fewer than ten rows must have values greater than itself. Oh dear, I've even confused myself now but what I'm trying to say is probably more clearly explained by the following expression:

SELECT c1.cust_id, c1.amt
FROM cust_table c1
WHERE 10 > (SELECT count(c2.amt)
            FROM cust_table c2
            WHERE c2.amt > c1.amt)
ORDER BY 2 desc;

set serveroutput on
set feedback off
declare
  cursor c is select cust_id, amt
              from cust_table
              order by 2 desc;
  v_cust_id   number;
  v_amt       number;
  i           number;
begin
  open c;
  -- Just fetch the 10 first lines
  for i in 1 .. 10
  loop
    fetch c into v_cust_id, v_amt;
    dbms_output.put_line(to_char(v_cust_id)
              || chr(9) || to_char(v_amt));  -- chr(9) is a tab
  end loop;
  close c;
end;
/
set feedback on

Yours sincerely,

~~~~~~~~~~~~

January 18 1999

Dear Aunt Augusta,

After years of making the romance with VMS, we wish dumping the old system in favour of a younger DEC/Unix. Break-ups being rarely a thing easy, we shall have at the same time to migrate from Oracle 7.1.6 to Oracle 8.0.4 (straight) and here is my problem : our language of work is not the English but a language with éverywhère the wonderful accents. However, we are finding for years that the easiest way to deal with the accents was to declare at the creation time, the character set as US7ASCII; mysteriously, Oracle then was quite tolerant and we had no problem to retrieve the stored accentuated letters. But it has become a little more strict recently (from 7.2, one has said to me), this is why I have created my v8 database with WE8ISO8859P1, and when I have exported/imported some table as a test, I have lost all my accents! (worse than that, in fact, accentuated letters were replaced by letters or signs which were not the unaccentuated equivalent. Merde alors). What can I do?

Continental DBA

Dear Continental DBA,

I don't really see why you need to use accents at all, except perhaps for clichés such as la crème de la crème or storing recipes of crêpes flambées in Oracle.
However, I have learned from experience that people are usually extremely fond of this kind of thing. Dear poor old Charlotte, who was once a long, long time ago a governess on the Continent and seems to have maintained since that time an interest in foreign languages - Mabel Waymarsh once suggested she'd had an affair with a native but I would certainly never stoop to such gossip - has suggested to me at least three ways to deal with your problem :

- The first one is to change the character set of your 7.1.6 database. To do this, you must connect as SYS. Check first with :

    select value$
    from props$
    where name = 'NLS_CHARACTERSET';

    update props$
    set value$ = 'WE8ISO8859P1'
    where name = 'NLS_CHARACTERSET';

- The second one is to pretend the data comes from a WE8ISO8859P1 database by patching the .dmp file. The third byte (should normally be 0x01 for US7ASCII) should be replaced with 0x1f for WE8ISO8859P1.
There is a little free C program on this site which does just that. Have a look at :- patchdmp.c
Unfortunately I've been unable to find codes for other character sets.

- The third and probably easiest way to do it, is not to use exp/imp but an unloader such as pdqout (from those nice people at Oriole). Please be sure that your unloader deals properly with accentuated letters, as pdqout does, and make it generate both a control file and a datafile for SQL*Loader. It'll be easy for you to check that your data file really contains what you expect, and you can easily change the 'LOAD DATA CHARACTERSET US7ASCII' in the control file into 'LOAD DATA CHARACTERSET WE8IOS8859P1' (or perhaps, in your case, WE8DEC). You will have moreover, the advantage of loading your data much faster than with imp, which is always a good thing.

Yours sincerely,

~~~~~~~~~~~~

 

October 10 1998

Dear Aunt Augusta ,

I find it very difficult to bring myself to confess it but I've gotten myself involved with a number of databases at the same time, each with its own SQL*Plus (my favourite tool for most things) session opened and I just keep switching from one to the other. I live in perpetual fear of making a devastating gaffe and confusing one database with the other and doing wild kinky things (which were intended for a test database) to my straight-laced main production database . I'm at my wits end, what can I do ?

Bill C., Washington DC

Dear Bill,

First let me say that if you were faithful to one database you would never have this kind of dilemma in the first place. I realise that I'm an old fuddy-duddy who must try to move with the times, so I'll try to be more tolerant of the fecklessness of some DBAs. In this case, I think that what you should do is set up the SQL*Plus prompt so that it automatically indicates which database you are fooling around with. This can easily be done by adding the following lines to the

$ORACLE_HOME/sqlplus/admin/glogin.sql file: 

set pagesize 0
set feedback off
set termout off
set pause off
set echo off
spool $HOME/.prompt.tmp
select 'set sqlprompt ''' ||
       d.name || '@' ||
       substr(s.machine, 1, decode(instr(s.machine, '.'), 0,
       length(s.machine),
                                   instr(s.machine, '.') - 1)) ||
       '-SQL> '''
from v$session s,
     v$database d
where s.sid = 1
/
spool off
@$HOME/.prompt.tmp
!rm -f $HOME/.prompt.tmp 2>/dev/null
set pagesize 24
set feedback on
set termout on

As you must know, glogin.sql is executed each time a user connects to Oracle with SQL*Plus. This will automatically replace the default SQL> prompt by <database name>@<host>-SQL> which should help to prevent you from making a mistake. Note that you can just have <database name>-SQL> by replacing the SQL query by a much simpler

select 'set sqlprompt ''' ||
       name  || '-SQL> '''
from  v$database
/

or, alternatively, you can also add the current user name to the prompt by referring to the USER pseudo-column. Beware, however, of two things : First, v$session and v$database used in this example cannot, usually, be queried by the layman and therefore the SELECT will fail when a non-DBA uses SQL*Plus. This is why you must log on as SYS and execute

     grant select on v_$session to public;

and

     grant select on v_$database to public;

on each of your databases (take care that it really is v_$xxxx you must refer to and not v$xxxx). Secondly, it is important to remember that glogin.sql is not run if you use the SQL*Plus connect command from within a session, so you'd better not use it to connect to a different database, because then the prompt would become very misleading indeed.

With all my sympathy,

 

~~~~~~~~~~~~

 

 

 

September 4 1998

Dear Aunt Augusta ,

My poor database is showing signs of ageing and to put her out of her misery I've decided to make the jump to the Parallel Server. That's fine, but it also means I have to migrate my database files, hitherto using Unix filesystems, to raw devices. I'm supposed, I've been told, to recreate the database and then exp/imp it. I'm not saying I dislike exp/imp, on the contrary, I positively adore them. There's nothing I like more than spending a few days (my database is around 100GB) quietly sitting by the fire in my slippers, smoking my pipe with a good book and a bottle of port wine by my side, while Oracle trundles away importing and indexing millions of lines. As you can imagine I am positively bubbling at the idea of then spending endless hours trying to fix the numerous import errors, disabling and re-enabling integrity constraints, and all the while users ringing me every 10 minutes to ask when the database will be available. I 've seen interesting tools such as PDQOUT to replace exp/imp on your site. Would you recommend them in my case?

Datamart Joe

Dear Datamart Joe,

Not necessarily. As the Reverend Starr was saying during his so uplifting sermon last Sunday 'All things are allowed, but not all things are advantageous' - I think he was quoting St Paul but I must confess to some temporary lapses these days - age, most probably. I absolutely must remember to ask him for the text of his sermon when I see him. Anyway, pdqout is certainly an excellent utility to use with sqlldr to replace exp/imp on most occasions but in this case you can do much better. You can copy your filesystem files directly to the raw devices using the Unix dd command; the only trick is to skip the first block of the source files. Here is what you should do :

First take your database offline (shut it down).

Then, for each database file (which may of course include the redo log files) run :

dd if=Source_database_file.dbf of=/dev/raw_device_file.dbf bs=64k seek=1


     if = input file 
     of = output file 
     bs = buffer size (an old DBA friend of mine told me that left at the
     default 512k it took her 11 hours in parallel , and with a 64k buffer
     2.5 hours) seek = If you do not use this parameter you will get
     'invalid header' upon startup of the database 

This skips one block for os usage which permits the copy.

For example you can run :

dd if=/u105/ORA_DB/BEN1/BEN1_REDO_11.dbf of=/dev/rvol/raw_5dg/redo_11
bs=64k seek=1

and

dd if=/u105/ORA_DB/BEN1/BEN1_REDO_21.dbf of=/dev/rvol/raw_5dg/redo_21

bs=64k seek=1

Yours faithfully,

 

~~~~~~~~~~~~

 

 

 

August 14 1998

Dear Aunt Augusta,

My database just won't stop complaining. You know how it is with Oracle, even during the honeymoon, when you run catalog.sql, it just can't help but nag you with ORA-nnnnn error messages. But what you can put up with in private quickly becomes embarrassing in public. I have to ship sql scripts which are supposed to run on databases I am not very sure of. As I like to write clean scripts which can be run several times, I would like to write a DROP before each CREATE. Unfortunately, these scripts will be run by Oracle-illiterate people who are going to panic at the very first error message. How can I suppress expected messages of the ORA-0942 type while still letting real error messages go through?

Abused DBA

 

Dear Abused DBA,

Well, I have heard many tales of this type over the years and I have found that the blame is rarely all on one side, you must realise that an Oracle database has to be treated with forbearance. You won't stop it complaining simply with a kind word (not even with a kind word and a gun); quite obviously, the creation of all stored objects (and by that I mean views, packages, triggers etc.) should not be preceded by DROP, but simply by a CREATE OR REPLACE - as soon as your code is correct, you will have no error whether it already exists or not. Whilst the case of other objects, such as tables, sequences or synonyms provides a somewhat more engaging little conundrum, there is however, a way to handle errors. Rather than ignoring them: just trap them. You must have already tried all of the resources of SQL*Plus and realised that it can offer no solution apart from spooling the result to a file and suppressing screen-output. Not much of a solution, as I imagine you already spool to a file and you expect users to look there for errors. However, all is not quite lost, why not try a little PL/SQL?

'Oh no!' I hear you cry 'That silly old aunt Augusta's gone completely gaga this time, everybody knows you can't have DDL in PL/SQL!'.

Well, not quite completely gaga, and certainly not yet, young man!

In fact, you can execute DDL, such as a DROP statement, if you execute it dynamically with the Oracle-provided DBMS_SQL package. Granted, you will find a lot of warnings in this package against doing any such thing but, whilst problems can occur in a stored procedure; an anonymous PL/SQL block is quite safe - and moreover, as my dear departed father often used to remark, rules are for the guidance of wise men and the adherence of fools, and I do so agree with him, don't you?.

What I suggest is that, at the beginning of your scripts, you insert something which resembles the following:

rem
rem =============================================================
rem
rem        UNDO THE CHANGES BROUGHT BY THE SCRIPT
rem
rem =============================================================
rem
--
--    Special anonymous PL/SQL block to drop anything created
--    by the script. If one attempts to drop something which
--    does not already exists, the exception normally raised
--    is trapped and no error is generated.
--
declare
  type StmtTyp is       table of varchar2(1000)
                        index by binary_integer;
  Stmt                  StmtTyp;
  i                     binary_integer;
  cid                   number;
  dummy                 number;
  table_doesnt_exist    exception;
  cons_doesnt_exist     exception;
  obj_doesnt_exist      exception;
  trig_doesnt_exist     exception;
  privsyn_doesnt_exist  exception;
  pubsyn_doesnt_exist   exception;
  clust_doesnt_exist    exception;
  dblink_doesnt_exist   exception;
  index_doesnt_exist    exception;
  role_doesnt_exist     exception;
  seq_doesnt_exist      exception;
  snap_doesnt_exist     exception;
  PRAGMA EXCEPTION_INIT(table_doesnt_exist, -942);
  PRAGMA EXCEPTION_INIT(cons_doesnt_exist, -2443);
  PRAGMA EXCEPTION_INIT(obj_doesnt_exist, -4043);
  PRAGMA EXCEPTION_INIT(trig_doesnt_exist, -4080);
  PRAGMA EXCEPTION_INIT(privsyn_doesnt_exist, -1434);
  PRAGMA EXCEPTION_INIT(pubsyn_doesnt_exist, -1432);
  PRAGMA EXCEPTION_INIT(clust_doesnt_exist, -943);
  PRAGMA EXCEPTION_INIT(dblink_doesnt_exist, -2024);
  PRAGMA EXCEPTION_INIT(index_doesnt_exist, -1418);
  PRAGMA EXCEPTION_INIT(role_doesnt_exist, -1919);
  PRAGMA EXCEPTION_INIT(seq_doesnt_exist, -2289);
  PRAGMA EXCEPTION_INIT(snap_doesnt_exist, -12003);
begin
  --
  --   STORE HERE THE STATEMENTS TO BE EXECUTED
  --   DO NOT FORGET TO INCREASE THE INDEX !
  --
  Stmt(1) := 'DROP TABLE TABLE_1';
  Stmt(2) := 'ALTER TABLE TABLE_2 DROP CONSTRAINT PK_TABLE_2';
  --
  --   NO CHANGE REQUIRED BELOW THIS POINT
  --
  cid := dbms_sql.open_cursor;
  i := 1;
  loop
    begin
      dbms_sql.parse(cid, Stmt(i), dbms_sql.native);
      dummy := dbms_sql.execute(cid);
    exception
      --
      --   End of the list of statements
      --
      when no_data_found then
          exit;
      --
      --   Errors to ignore
      --
      when table_doesnt_exist then
          null;
      when cons_doesnt_exist then
          null;
      when obj_doesnt_exist then
          null;
      when trig_doesnt_exist then
          null;
      when privsyn_doesnt_exist then
          null;
      when pubsyn_doesnt_exist then
          null;
      when clust_doesnt_exist then
          null;
      when dblink_doesnt_exist then
          null;
      when index_doesnt_exist then
          null;
      when role_doesnt_exist then
          null;
      when seq_doesnt_exist then
          null;
      when snap_doesnt_exist then
          null;
      --
      --   Unexpected errors
      --
      when others then
          if (dbms_sql.is_open(cid))
          then
            dbms_sql.close_cursor(cid);
          end if;
          raise_application_error(-20000, Stmt(i) || chr(10) || SQLERRM);
    end;
    i := i + 1;
  end loop;
  dbms_sql.close_cursor(cid);
end;
/   

You can then have all the CREATE statements you need - whether the objects did or did not exist before running the script, you will have no error message.

 

Best wishes

 

~~~~~~~~~~~~