Import large schema - oracle 10G

[Ratzie]

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?

[Ratzie]

We also disabled archiving for the import.

[Patrick Wallek]

I would try removing the indices applicable to this schema and then recreate them, if possible.

[Hein van den Heuvel · Accepted solution]

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]

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

[Frank de Vries]

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

[Ratzie]

Appreciate the help