Home » RDBMS Server » Server Utilities » Impdp - Index creation (Oracle 11.2.0.4 (3 node RAC))
Impdp - Index creation [message #626603] Wed, 29 October 2014 17:16 Go to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Hello everyone,

We are currently migrating data from Oracle 10.2.0.5 database to Oracle 11.2.0.4 database.

We are using datapump for export and import.
1- export meta and data
2- file transfer
3- import data
4- create sqlfile for indexes using "impdp sqlfile=cr_index.sql include=index dumpfile....." from export meta dump file
5- run the sqlfile scripts generated to create indexes
-we are dividing the create index script into 3 scripts, and running from each node (since we have 3 nodes)
6- gather stats

So far so good, but lately I am observing that creating indexes is going really slow.

For creating indexes, which method is faster from the below?
-create sqlfile using "impdp sqlfile=cr_index.sql include=index dumpfile.....", and run the sql script (like we are currently doing)
OR
-just import them using "impdp include=index dumpfile....."


Please advise.

Thank you,
Ricky
Re: Impdp - Index creation [message #626604 is a reply to message #626603] Wed, 29 October 2014 17:21 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
You can't push a string.

>6- gather stats
statistics are computed when INDEX is made
Re: Impdp - Index creation [message #626605 is a reply to message #626604] Wed, 29 October 2014 17:50 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Thank you BlackSwan.

So, all we could do is wait until the index creation script is complete?

>statistics are computed when INDEX is made
oh wow, that's good to know, meaning we don't have to gather stats again, that would save us some time for sure.
However, once the index creation is complete, I will check the "LAST_ANALYZED" column of dba_tables specific to the tables that were imported, to make sure that the stats are also updated
Re: Impdp - Index creation [message #626606 is a reply to message #626605] Wed, 29 October 2014 18:21 Go to previous message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
when all else fails, Read The Fine Manual

http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#ADMIN11728
Previous Topic: Import taking forever to finish
Next Topic: Using VERSION keyword while export.
Goto Forum:
  


Current Time: Mon Sep 21 14:42:32 CDT 2020