COMPARE DATA IN TWO DIFFERENT SCHMAS 1

[mkey]

hI ALL,
I know that I could compare two schemas and the objects in them. For example
select table_name from dba_tables where owner ='schema_1'
minus select table_name from dba_tables where owner = 'schema_2';
Now I need to compare the data in schema_1 vs schema_2. Any ideas or suggestions will be of great help.
Thank you,
sj

 

[cfsenorin]

Try this statement:

Select * from schema2.table1
minus
Select * from schema2.table1;

If you got error, object does not exists, then you don't have any rights to select the tables. You must be grant select privileges in order to do this.

 

[AndyYoung]

This is easy to do. You can even do it across database links. This is actual code that I used to check a database copied from one instance to another. There were differences caused by incompatible charactersets. This doesn't look for things in S2 that are not in S1.

First you want to know if there are many differences or none at all.
select count(*) from (SELECT * FROM RIMS_S1.ACCESSION@d16b MINUS SELECT * FROM RIMS_S2.ACCESSION@d16c);

Then you can get them.
SELECT * FROM RIMS_S1.ACCESSION@d16b MINUS SELECT * FROM RIMS_S2.ACCESSION@d16c;

 

[mkey]

Thank you so much cfsenorin.