information on database links 2

[mkey]

Hi All,
I need to compare tge differences(for example tables, constraints, sequences etc.) between two databases. Now I assume I have to link the database that I want to compare against. For example the two databases that I'm trying to compare are(db1 and db2 are in different schema) My sql statement that created the link looks something like this:

CREATE PUBLIC DATABASE LINK my_link
CONNECT TO db2 IDENTIFIED BY db2
USING 'plc';

Currently I have logged under db1. Then I created my link to db2. What should my next step shoudl be, so that I could compare for example the tables in db1 against the tables in db2?
Any thoughts would be of great help.
sj

 

[Kenrae]

Well, I suppose you should compare tables like user_tables, user_views, user_objects, etc. from one schema to the other one. The exact comparisons to do depend on what you want to compare.
But be sure you'll make a query like:

SELECT * FROM user_tables
MINUS
SELECT * FROM user_tables@db2;

or

SELECT u1.*,u2.* FROM user_tables u1, user_tables@db2 u2
WHERE u1.table_name=u2.table_name;

Or something similar.

 

[mkey]

Hi,
I tryed what you have suggested and I'm getting a TNS error.
Both of my db1 and db2 connection string is the same. What am I doing wrong. The other thing is I cannot seem to find this error in the Oracle 8i documentation.
Thank you

ORA-12154: TNS:could not resolve service name

 

[mkey]

Hi,
I have corrected that error now I am getting another error.
ORA-02019: connection description for remote database not found

thank you

 

[kindus]

Make sure the following and try again.
1) listener at the destination db is configured and running
2) tnsnames.ora is configured at the source db for the destination db
3) database link is created with proper service name as defined in the tnsnames.ora
4) destination database is up and running