Sunday, January 2, 2011

Comparing two schemas

Hi,
We can compare two schemas with the help of tools like toad,But what if you don't have tool,We can do this with the help of the below queries:

/* Query 1:To compare the two users in terms of tables: */

SELECT TABLE_NAME FROM ALL_TABLES
WHERE TABLE_NAME NOT IN
(SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER='EMP1')
AND OWNER='EMP2';

/* Query 2:To compare the two users in terms of objects */

SELECT OBJECT_NAME||' '||OBJECT_TYPE FROM ALL_OBJECTS
WHERE OBJECT_NAME NOT IN
(SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER='EMP1')
AND OWNER='EMP2';

Above queries can be very much helpful,when we transfer data from one schema to other schema and we want to verify them after successful transfer(i.e) after doing datapump or export import activities.Hope it helps.


Best regards,

Rafi.

1 comment:

  1. hai rafi just I'm changing my blogspot name please add my blogspot to your friends list:http://oracledbastudio.blogspot.in

    ReplyDelete