Import Problems

[alison]

Hi there,

Could anyone help

I am trying to speed up the import of a database (it usually takes over 8 hours).

I am using the following command to perform the import, it runs, however when I do a count on the importing database the indexes are being created, I specifically don't want it to do this.

imp73 username/password@database file=f:\oradba\admin\exp\fullexport.dmp indexes=n full=y buffer=300000000 commit=n ignore=y

Could anyone help

Many thanks

Alison

[brassd]

Alison,

I beleive that when you do a full import (full=y) this will override the indexes=n parameter with indexes=y. One way to overcome this would be to do a full export and bring in the tables a schema at a time. You could then specify indexes=n

Any use?

Dave

[alison]

Thanks for your message,

However in this case importing schema by schema wont work.

What I am doing is importing from production to test, therefor the schema's don't exist in the target yet.

I could create them and then do the import but I am looking for a cleaner solution.

Many thanks

Alison

[rogerF]

Writing a shell script to import the tables 1 by 1 wouldn't be that bad. You just have to output the tables and paste them into a file- then paste in the appropriate imp command that excludes the indexes. This should be too much of an effort.

[url]http://www.timonions.com/[/url]

has some reverse engineering scripts that will spit out the table names for 1 or all users.

Nobody said being a DBA was fun :-)

[alison]

Thanks for that suggestion

Is there really no way of doing a full import but specifying indexes=n

If I use a parfile to specify full and then outside the parfile specify indexes=n would that work (I read that the last option read overwrites previous ones)

Any advice would be great

Many thanks for your time

Alison

[alison]

Could anyone give me any advice on this problem.

Many thanks in advance

Alison

[rogerF]

Alison:

This page sez just about all that can be said about import. Before you get frusterated- read the docs carefully and expereiment with a test DB.

[url]http://www-rohan.sdsu.edu/doc/oracle/server803/A54652_01/import.htm[/url]

[alison]

Hi Friends.

Thank you all for your replies.

I have an extra query to add to the above information.

I ran the import using the following parameters
imp73 username/password@database file=f:\oradba\admin\exp\fullexport.dmp indexes=n full=y buffer=300000000 commit=n ignore=y

I understood that the full=y overwrites the indexes=n

I did get indexes imported however not all of them.

At last import with indexes=y I imported 756

With indexes=n I only got 369

All the tables imported correctly
I got no errors in my import log either time

This is running on a test db, which is a replica of production.

I would be grateful if anyone could shed any light on this

Regard

Alison

[tangqiang]

Hi, Alison

Can you check what kind of indexes created when you specify
indexes=n?

Because when import utility will create system generated indexes such as LOB index, OID index and unique constraint index (include primary key) regardless of the setting of INDEXES parameter. Set INDEXES=N only disable the creation of user generated indexes.

I think this is the reason you got different result when you use different setting.


Tang Qiang

[alison]

Hi Tang,

Many thanks for your help, this makes much more sense to me now.

I have tried many different way's of performing the import.

1. Full
2. Partial with indexes=n then running the createindex script to finish the job.
3. Partial with fromuser touser indexes=n then createindex.

All of these work but in total come back to roughly the same timings.

Can anyone suggest another option which may speed things up.

Cheers

Alison