Re: Standard procedure to prepare a DB for import?

From: Rick Denoire <100.17706_at_germanynet.de>
Date: Sun, 21 Oct 2001 23:36:13 +0200
Message-ID: <cuf6tt86pug42perik4du8doh6aaklshdk@4ax.com>

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:

>You shouldn't import table and indexes at the same time. The import process
>will be definitely much slower, and will force to rebuild.
>You need to import with indexes=n
>generate the indexfile
>edit the indexfile to add the nologgin clause to every create index
>run it

This is the kind of advice I was looking for!

>I will leave the second set of code to you as an exercise. 3 won't be
>necessary anymore when you follow the above procedure.
>Obviously this code is along the same lines as the *very simple* programs
>above.

Yes, changing "disable" into "enable" (or something similar, I will have to check that out) should not be difficult.

Thanks a lot!

Rick

---

>
>"Rick Denoire" <100.17706_at_germanynet.de> wrote in message
>news:5h75tts1dp8g3f102uo2d33bpasqvuvkte_at_4ax.com...
>> Does anyone know how to do the following in order to prepare a DB to
>> full import a dump file?
>>
>> 1. Truncate all objects
>> 2. Disable triggers
>> 3. Drop all indexes
>>
>> Then, after (hopefully) successfully importing the dump file:
>>
>> 1. Remove all truncated objects that weren't existant in the dump
>> (they are remained empty after the import)
>> 2. Enable triggers
>> 3. Rebuild indexes

>1
>begin
>for r in (select owner, table_name from dba_tables where owner <> 'SYS'
>order by 1,2) loop
>execute immediate 'truncate table '||r.owner||'.'||r.table_name;
>end loop;
>end;
>/
>2
>for r in (select owner, trigger_name from dba_triggers where owner <>'SYS'
>order by 1,2) loop
>execute immediate 'alter trigger '||r.owner||'.'||r.trigger_name||'
>disable';
>end loop;
>end;
>/
>3
>for r in (select owner, index_name from dba_indexes where owner <>'SYS'
>order by 1,2) loop
>execute immediate 'drop index '||r.owner||'.'||r.index_name;
>end loop;
>end;
>/
>
Received on Sun Oct 21 2001 - 16:36:13 CDT