Select records on one database that match on another database? 1

[Glowworm27]

Hi all u Gurus

I have 2 databases, my local development database, and a Testing database at a remote location.

I want to query a table on my local database (TESTORACLE) and Join it with a Table on the Remote Database (MTADEV)

What I want to do is find the records that do Not match up on the remote database. To try to figure out what is missing on the remote system, to diagnose a bug.

Table layout is like this
Table Name = Labor_Dist
Fields =(Labor_Dist_ID -- (is the Key field)
, Company_Code
, SSN
, Acct_Unit
, Hours
, Pay_Sum_group
, Check_Date
, Universal_ID
, Work_Week_Ending_Date
, Create_Date
, Pay_Amt)

The tables are identical on both systems

Thanks in advance

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[Turkbear]

Hi,
After creating a database link to the remote table,
use the MINUS operator to determine what is not there:

Select * from Remote_table@dblink
MINUS
Select * from Local_table

will show all records that are in the Remote table but not in the Local one..
Reverse the order to see what is in the Local but not the remote.

You can use this method to create an exceptions table by using the Create Table <Tablename> as select * from
(Select * from Remote_table@dblink
MINUS
Select * from Local_table)
method.








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Glowworm27]

OK, thank you,

How do you set up a database link?

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[Turkbear]

Hi,
Read the docs about Create dblink:
Try here ( from

http://tahiti.oracle.com,

a combined Oracle version help site):

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_56a.htm#2061620

You may have to join Oracle's TechNet to view this - do so - it is free and does not spam and is a great service.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Glowworm27]

Yeah I read the Docs, And I am not an Oracle Person, So some of it is hard to understand. Im a microsoft SQL guy that got stuck on this project.

And I have tried to set one up.

And I am receiving an error that ORA-12154: TNS:could not resolve service name

I can successfully connect using SQL plus to both my local database, and the remote database.

I have tried to create the link using this sql

CREATE DATABASE LINK Remote
CONNECT TO mtadev
IDENTIFIED BY mtadevpass
USING 'MTADEV.BFR.BFRCO.COM';

and here is what is in my tnsnames.ora file

MTADEV.BFR.BFRCO.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Devtest.BFR.bfrco.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = mtadev)
    )
  )

and I receive the above error message.

I have even tried to create a link using this sql

CREATE DATABASE LINK MTADEV.BFR.BFRCO.COM
CONNECT TO mtadev
IDENTIFIED BY mtadevpass
USING 'MTADEV.BFR.BFRCO.COM';

With the same results
Thanks for the Help

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[Turkbear]

Hi,
Welcome to the most 'popular' error when first using Database links...

The link uses the TNSNAMES.ORA file on the database server to resolve the MTADEV.BFR.BFRCO.COM alias, not the one on your or any client's workstation..


If this is not the cause, let us know..



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Glowworm27]

OK I will get with the DBA to see about the database server tnsnames.ora file.

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[sem]

The same way you may create a link from your local database to remote one.

Regards, Dima

http://seminihin.narod.ru

 

[Glowworm27]

Thanks Turkbear,

I was able to get it working. My local database is only local in that it resides on our network, it was not on my local machine. So I spoke with another DBA and he said I needed to add the Remote Database info to the TNSnames.ora file on my local database server, and not my machine, like you had said.

Once that was done, it works perfectly now. Thanks and Star for you.

I thank you for your comments, and if I can return the favor in the future, I will be here for you.

Good day everyone.

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips