Home » SQL & PL/SQL » SQL & PL/SQL » Comapring tables from two databases
Comapring tables from two databases [message #248221] Thu, 28 June 2007 05:58 Go to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Hi All

I just want to compare some 100 tables from two databases one on version 8.1.7.4 and other on oracle 10gr2. Tell me most efficient way to do it except minus and union all method.

Do anyone have a script which will compare all tables in two databases?

Re: Comapring tables from two databases [message #248258 is a reply to message #248221] Thu, 28 June 2007 08:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What if minus and union all IS the most efficient way?
Re: Comapring tables from two databases [message #248260 is a reply to message #248221] Thu, 28 June 2007 08:17 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Compare what ? Data or table structures ?

[Updated on: Thu, 28 June 2007 08:20]

Report message to a moderator

Re: Comapring tables from two databases [message #248365 is a reply to message #248260] Thu, 28 June 2007 14:15 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
More info on getting table data comparison to perform...
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:24371552251735
Re: Comapring tables from two databases [message #248399 is a reply to message #248260] Thu, 28 June 2007 21:43 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Comparing data only.As this is a migration from 8i to 10g.So structure of the tables will be same. But need to check the dataa in these tables.
Re: Comapring tables from two databases [message #248400 is a reply to message #248221] Thu, 28 June 2007 21:52 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
In the surface the goal is admirable, but the devil is in the details.

0) I contend the comparison is not needed & a waste of time.

1) How much data is involved?
2) How much application downtime is allowed to tranfer the data & complete the compare?
[Having application online one of the databases has changing content.]
3) Enumerate all the different datatypes in the application.

After responding with 3 answers, we can discuss alternative(s).
Re: Comapring tables from two databases [message #248401 is a reply to message #248400] Thu, 28 June 2007 21:56 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Total data size would be 100 GB.
The data need to comapred through database link.
All data types are involved including LOB's.

I have one method of group by. But i was just looking whether we have soem more alternatives for that.
Re: Comapring tables from two databases [message #248402 is a reply to message #248221] Thu, 28 June 2007 22:07 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
2) How much application downtime is allowed to tranfer the data & complete the compare?

Do you REALLY intend to compare every field in every record between BOTH databases?
Re: Comapring tables from two databases [message #248405 is a reply to message #248402] Thu, 28 June 2007 22:33 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
thinking if feasible...

If not feasible then only important tables will be comapred.
Re: Comapring tables from two databases [message #248408 is a reply to message #248221] Thu, 28 June 2007 22:41 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
Since you have chosen to twice to not answer my question, I am choosing to not answer yours.

You're On Your Own (YOYO)!
Re: Comapring tables from two databases [message #248708 is a reply to message #248408] Sat, 30 June 2007 22:57 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
CAN TAKE IDEA FROM THIS
Re: Comapring tables from two databases [message #248712 is a reply to message #248708] Sat, 30 June 2007 23:14 Go to previous message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
This may also help: http://www.orafaq.com/node/59

"In this article James continues to explore the Oracle's Metadata API and provides a powerful function to compare objects and schemas and print the DDL required to bring them in sync."
Previous Topic: "smart" Alpha-Numeric Sorting
Next Topic: Remote Database exception handling
Goto Forum:
  


Current Time: Wed Dec 07 14:15:51 CST 2016

Total time taken to generate the page: 0.10916 seconds