Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

To find Missing DB Objects from one schema,sid to other schema,sid

user11384749Dec 27 2011 — edited Dec 28 2011
Hi Friends..

I have an requirement as bellow...

Recently we migrated from one schema to other ,some of the DB objects are missed while migrating.
After Migration now the schema and SID are Different for newly created schema

while comparing the newly created schema2 with old schema1 found that some of the DB objects are missed.
So i need to find out the difference's from Schema1 to Schema2.
But now schema 2 is having different sid and schema name and to make the changes in schema2 with respect to schema1.

Please let me know how to go ahead with this....

Thanks in advance...

Comments

sb92075
connect new_schema/password
select object_name from user_objects
minus
select object_name from user_objects@OLD_SID_SCHEMA
union all
select object_name from user_objects@OLD_SID_SCHEMA
minus
select object_name from user_objects;
Frank Kulash
Hi,

Here's one way: 
SELECT	object_name
,	object_type
FROM	all_objects
WHERE	owner	= 'SCHEMA1'	-- Case-sensitive
    MINUS
SELECT	object_name
,	object_type
FROM	all_objects
WHERE	owner	= 'SCHEMA2'	-- Case-sensitive
;
Use a database link for one half of the MINUS, if necessary.

This will not detect objects that have the same name and type, but are different. For example, if both schemas have a table called TABLE_X, the query above will not display TABLE_X, even if the two tables have different rows and/or columns.
user11384749
hey that's looks good but how can i give the condition for other SID in schema2

I need to check with diff schema and SID
user11384749
Looks good but i Need to check with Old schema which is in other SID..
Frank Kulash
Hi,
user11384749 wrote:
Looks good but i Need to check with Old schema which is in other SID..
What do you mean by "other SID"?
Are the two schemas in different databases? If so, use a database link in one database referencing the other. Use this database link in one branch of the MINUS: 
...
FROM    all_objects@db_link_name
...
YaoDong PENG
Another way, if possiblely, try to use Toad for Oracle to do the job(the compare schema menu).
It's a good tool for these issues!
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 25 2012
Added on Dec 27 2011
6 comments
1,105 views