Home » RDBMS Server » Server Utilities » validation after expdp and impdp (AIX, Oracle 11.2.0.2)
validation after expdp and impdp [message #508873] Tue, 24 May 2011 14:28 Go to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
I did the expdp and impdp of a schema and found that out of total 120 indexes, there were almost 80 indexes missing in the destination schema. How to find out only those 80 missing indexes so that I could recreate them all without taking much time?
Re: validation after expdp and impdp [message #508878 is a reply to message #508873] Tue, 24 May 2011 14:45 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Did you check the datapump import log?
Maybe you will find the reason there...

[Updated on: Wed, 25 May 2011 00:53] by Moderator

Report message to a moderator

Re: validation after expdp and impdp [message #508879 is a reply to message #508878] Tue, 24 May 2011 14:57 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
Is there any SQL query which can retrieve only those 80 index names which are missing?
Re: validation after expdp and impdp [message #508882 is a reply to message #508879] Tue, 24 May 2011 15:51 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Assuming the 2 schemas are in the same DB a simple query against all_indexes would do the job:
SELECT table_name, index_name
FROM all_indexes
WHERE owner = <target_schema>
MINUS
SELECT table_name, index_name
FROM all_indexes
WHERE owner = <source_schema>

Re: validation after expdp and impdp [message #508884 is a reply to message #508882] Tue, 24 May 2011 15:58 Go to previous message
lg123
Messages: 225
Registered: August 2008
Senior Member
Thanks
Previous Topic: unable to create public synonym
Next Topic: SQL Loader's SQL string issue
Goto Forum:
  


Current Time: Fri Mar 29 04:46:03 CDT 2024