Home » SQL & PL/SQL » Client Tools » Script to compare the table data between two oracle schemas (merged) (Oracle 10g Release 2, Redhat Linux)
Script to compare the table data between two oracle schemas (merged) [message #426049] Wed, 14 October 2009 00:22 Go to next message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
Hi,

I would like to compare the table data between two oracle schemas.

Both the schemas contains almost identical database objects.

Kindly share if you have any SQL or shell script for this.

I would not prefer to use tools like TOAD or SQL developer, as this comparison process needs to be automated in my application.

Thanks,
SAMURAI
Re: Script or tool to compare data in two identical schemas [message #426057 is a reply to message #426049] Wed, 14 October 2009 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you want to compare table definitions or table data?

Regards
Michel
Re: Script or tool to compare data in two identical schemas [message #426062 is a reply to message #426057] Wed, 14 October 2009 00:47 Go to previous messageGo to next message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
It will be great if there is a logic(script) to compare both table definition and data. Laughing

But my prime focus is to compare table data.

[Updated on: Wed, 14 October 2009 01:21]

Report message to a moderator

Script to compare the table data between two oracle schemas [message #426092 is a reply to message #426049] Wed, 14 October 2009 01:24 Go to previous messageGo to next message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
Hi,

I would like to compare the table data between two oracle schemas.

Both the schemas contains almost identical database objects.

Kindly share if you have any SQL or shell script for this.

I would not prefer to use tools like TOAD or SQL developer, as this comparison process needs to be automated in my application.

Note: My prime focus is to compare only data. Not to compare table definition.

Thanks,
SAMURAI
Re: Script to compare the table data between two oracle schemas [message #426094 is a reply to message #426092] Wed, 14 October 2009 01:33 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

If you have to schemas with identical count of objects and want to compare the data between them for each object:

Create a FOR LOOP which gets the list of all objects in one schema and using MINUS command which extracts the rows of the second table from the first (using database link) and write it to another table
Re: Script or tool to compare data in two identical schemas [message #426098 is a reply to message #426062] Wed, 14 October 2009 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read the second part of the following T. Kyte article: http://www.oracle.com/technology/oramag/oracle/05-jan/o15asktom.html

Regards
Michel

[Updated on: Wed, 14 October 2009 01:57]

Report message to a moderator

Re: Script to compare the table data between two oracle schemas [message #426101 is a reply to message #426092] Wed, 14 October 2009 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is useless to duplicates your questions among forums.
1/ It is hard to follow the same discussion across the topics
2/ They will be merged anyway

Regards
Michel

Re: Script to compare the table data between two oracle schemas [message #426231 is a reply to message #426101] Wed, 14 October 2009 08:54 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
you can refer this...
[http://www.oracle-base.com/dba/Script.php?category=miscellaneous&file=compare_schemas.sql

Regards
Sriram.
Previous Topic: Toad error message as No Error
Next Topic: SQL Developer Templates
Goto Forum:
  


Current Time: Sun Dec 04 02:55:34 CST 2016

Total time taken to generate the page: 0.08643 seconds