how to compare same table data which is in two difiirent database

how to compare same table data which is in two difiirent database


hi i have a situation where a database was moved from one sever to another one somehow during migration the user where able to access to database in old server and able to do transaction now the data is not up to date, so now i what to compare the data from the old database to the new database in the new server



example table employer@db1 and table employer@db2



i what to see those records which diffier and able to identifiy tham how can i do that



am in oracle database 11gR2
chalie001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
If you have Ms Access  then you can link the two tables and compare them...
0
ste5anSenior DeveloperCommented:
Create a database link to the old database. Then you can use the INTERSECT and MINUS operators to determine what's same and what not.
0
MlandaTCommented:
to get records in db1 but not matching all columns with whats in db2
select [columns] from db1.employer
minus
select [columns] from db2.employer

Open in new window


and also, the other way round, to get records in db2 not in db1
select [columns] from db2.employer
minus
select [columns] from db1.employer

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

chalie001Author Commented:
this is how i select value from two diffirent db

selec* from hr.employee i have to put the schema name
0
chalie001Author Commented:
i what to see one column twice from both database e.g

empno_db1,empno_db2,deptno_db1,dept_db2 i what to see the diffirent
0
ste5anSenior DeveloperCommented:
Where is the problem??

SELECT  yourColumns
FROM    srcTable S
        INNER JOIN dstTable D ON S.primaryKeyColumns = D.primaryKeyColumns
                                 AND S.columnX <> D.columnX;

Open in new window

0
chalie001Author Commented:
how can i write this sql in oracle sql
0
ste5anSenior DeveloperCommented:
D'oh? As above?

There is not really an Oracle SQL. It's called PL/SQL. How much experience do you have in PL/SQL?
0
slightwv (䄆 Netminder) Commented:
>>There is not really an Oracle SQL. It's called PL/SQL

Incorrect.  PL/SQL is Oracle's procedural coding language.  SQL is just SQL.

>>how can i write this sql in oracle sql

Create the database link and do the minus above:
select col1,col2,col3 from employer@db1
minus
select col1,col2,col3 from employer@db2
/

Then do the opposite:
select col1,col2,col3 from employer@db2
minus
select col1,col2,col3 from employer@db1
/


If you cannot link the two databases, spool out the results to a flat file and use some file compare tool.

from sqlplus:
spool db1.txt
select * from employer@db1;
spool off

spool db2.txt
select * from employer@db2;
spool off
0
ste5anSenior DeveloperCommented:
The used SQL dialect in Oracle is called PL/SQL. What's incorrect about that??
0
slightwv (䄆 Netminder) Commented:
>>The used SQL dialect in Oracle is called PL/SQL

You can keep saying it but it is still incorrect.  PL/SQL is the Procedural Language used to write stored procedures, etc...

SQL is just SQL.  Until you use a code construct like BEGIN/END, it is NOT PL/SQL.

Believe the online documentation if you do not believe me:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/overview.htm#LNPLS001

PL/SQL, the Oracle procedural extension of SQL, is a portable, high-performance transaction-processing language
0
ste5anSenior DeveloperCommented:
And how do you call the language where MINUS is defined?

ANSI SQL (ISO/IEC 9075:1992, SQL-92)  defines only EXCEPT.
0
slightwv (䄆 Netminder) Commented:
>>And how do you call the language where MINUS is defined?

It is still SQL.  Every database product has extensions to the core ANSI SQL.  It is still called SQL.  The "PL" stands for "Procedural Language"  If you do not believe Oracle's own documentation, I cannot change your mind.

This will be my last off-topic post.  If you wish to continue this discussion please message me.
0
chalie001Author Commented:
correct
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.