Solved

schema duplicate

Posted on 2014-04-10
5
224 Views
Last Modified: 2014-06-10
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
Comment
Question by:tonydba
5 Comments
 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
ID: 39991590
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
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 39991691
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
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 39991869
use toad compare
http://software.dell.com/products/toad-development-suite-for-oracle/

it can generate a script for all the differences
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40124042
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle query output question 4 47
constraint check 2 48
Not able to drop or recreate an Oracle stored procedure 1 38
SQL Workhours Count beetween Workhours 3 25
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question