[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

schema duplicate

Sever A has database X with schemas A,B,C............
Server B has database Y with schemas A,B,D etc

How will you find out the schema that is not B is having ...other than A


A kind of subtract..Minus
0
tonydba
Asked:
tonydba
1 Solution
 
johnsoneSenior Oracle DBACommented:
I am assuming that you don't have a database link set up between the 2 databases that connects as a privileged account.  If you do, you shouldn't.

What I would do is get a list of schemas from database X:

Connect with SQL*Plus as a privileged user and run:
spool db_x_schemas.txt
select distinct owner from dba_objects order by 1;
spool off

Then on database Y:

Connect with SQL*Plus as a privileged user and run:
spool db_y_schemas.txt
select distinct owner from dba_objects order by 1;
spool off

Now you have 2 files:  db_x_schemas.txt and db_y_schemas.txt.  Get those 2 files on the same server (using scp or ftp) and then do:

diff db_x_schemas.txt db_y_schemas.txt

You most likely see differences due to prompts and you can ignore those.
0
 
Devinder Singh VirdiCommented:
Another method with DB link is
Create database link to_another connect to system identified by password using 'CONNTING_STRING';

select username from A, b@to_another
where a.username = b.username(+)
and b.username is null;

OR

you can use full outer join to see which schemas are mismatching

Then drop DB link.
0
 
Geert GruwezOracle dbaCommented:
use toad compare
http://software.dell.com/products/toad-development-suite-for-oracle/

it can generate a script for all the differences
0
 
Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now