Re: Importing and indexing large tables

Alan:

When I encountered the same problem some time ago, I had a machine with 6 CPU's and I noticed that if I use divided the number of tables to export by 6 and created, in your case, 6 jobs with 2 tables each, and I did not exported the indexes, but instead created sql scripts to create the indexes I could run the whole process in 1/4 of the regular time it will take to use one job to export everything.

So, export 2 tables per jobs (or whatever number you come up with), and perhaps if you have space rename the tables ( or copy them to other tablespace) or just drop them. Then import the tables with 2 tables per job and as soon as they are finish, run other batch jobs creating all the indexes.

Then finally when you are 100% sure everything is back as it was before drop the renamed tables.

Some other tip, if you could before export, make a copy of the dba_tables, dba_indexes, dba_objects, dba_tab_columns, dba_tab_indexes, dba_views, etc and after you finish your import and indexes have some script make comparison between dba_* views and the copied (before export,etc.) tables to check if everything is back as it was before the changes. Meaning, if you are missing a view, synonym, sequence, trigger, etc.

I hope this information could help you.

	   Best regards, Carlos R. Jimenez.  Sr. Oracle DBA.
		E*mail: Jimenez_Carlos_at_tandem.com

Alan Jeskins-Powell <Apowell.Dgo_at_btinternet.com> wrote in article <5hrqe8$2fp_at_neon.btinternet.com>...
> Consider this: 12 tables, average 3 million rows and 4 indexes per table.
 

> It has become impossible for us to export/import this data in a weekend
> (the only free processing time we have) given the techniques we are
> currently using (std.Oracle exp/imp)
> Does anyone have any ideas on how we can spped the process up so that we
> can a) fit the exp/import into a working weekend and b) still allow time
> for harware/software upgrades as well.
> Thanks
> Alan Powell
>
>
Received on Tue Apr 01 1997 - 00:00:00 CST