-
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
-
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
-
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
-
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 :-)
-
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
-
Could anyone give me any advice on this problem.
Many thanks in advance
Alison
-
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]
-
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
-
RE: import question
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|