Re: Standard procedure to prepare a DB for import?

"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
>
> All this without knowing anything particular to the target DB (all
> objects should be handled via generic sql scripts). This is because we
> are dealing with a kind of "black box" DB, where we are not supposed
> to know anything about internals of the DB.
>
> If you can provide a reference to some document in the Internet
> containing this info, this would be even better for me. I could not
> find anything else than trivial procedures for doing the import, like
> specifying import options etc., but in general, such instructions are
> not enough. Import is prone to different kind problems that people
> usually can solve if they are acquainted with the DB they are dealing
> with - this is not the case here.
>
> Thanks a lot!
>
> Rick
>
>

Assuming 8.1.5+ (I know people never post a version here, and I can't provide solutions for all different versions)

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;
/

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

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.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Sun Oct 21 2001 - 05:43:42 CDT