cancel
Showing results for 
Search instead for 
Did you mean: 

Import large schema - oracle 10G

SOLVED
Go to solution
Ratzie
Super Advisor

Import large schema - oracle 10G

What is the best approach to importing a large schema (371GB).
OEM import job keeps failing due to lack of temp space which is set to 50 GB.
Appears to be failing at index creation step...

Does initial index creation use temp space???
How do we work around this?

6 REPLIES
Ratzie
Super Advisor

Re: Import large schema - oracle 10G

We also disabled archiving for the import.
Patrick Wallek
Honored Contributor

Re: Import large schema - oracle 10G

I would try removing the indices applicable to this schema and then recreate them, if possible.
Hein van den Heuvel
Honored Contributor
Solution

Re: Import large schema - oracle 10G

I like to seperate the data import from the index creation, sometimes scripting concurrent index creations once table are loaded. In your case, having run out of resources, I would encourage concurrent work. Having the index creations done seperately may make it more clear where and when it fails... if it still fails.

If it does still fail, maybe report some more pertinent details like the rowcount and indexed defintion.

But really... this is very likely to be a platform independent user issue for which an Oracle forum will be much more appropriate than an HPUX forum.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm#sthref2455

1) Import using the INDEXFILE parameter to create a file of index-creation statements

2) Edit the file, making certain to add a valid password to the connect strings.

3) Rerun Import, specifying INDEXES=n.
(This step imports the database objects while preventing Import from using the index definitions stored in the export file.)

4) Execute the file of index-creation statements as a SQL script to create the index.

The INDEXFILE parameter can be used only with the FULL=y, FROMUSER, TOUSER, or TABLES parameters.

fwiw,
Hein.
Yogeeraj_1
Honored Contributor

Re: Import large schema - oracle 10G

hi,

as mentioned previously, you need to separate your index creation with your table creation.

Obviously, OEM will not help here.

How many objects (tables, indexes) are there?
Can you categorise tables into 3 groups: small, medium and large?


revert

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Frank de Vries
Respected Contributor

Re: Import large schema - oracle 10G

Hein already gave the full recipe,
and indeed it is good practice to bulk load the tables first and then bulk create the indexes after wards. Usually saves time.

But just to answer your question.

Indeed initial index creation uses temp space, as this space is used to sort the index it gets a evenly spread Btree.

a good site to ask Oracle related questions:

www.dba-village.com




Look before you leap
Ratzie
Super Advisor

Re: Import large schema - oracle 10G

Appreciate the help