Wednesday, November 29

Oracle database links using full TNS entries

Database links can be most useful, say you want to extract data to a reporting system or something like that. This is a quite common scenario people have problems with and come to the Oracle support IRC channel and ask about, especially developer with just a user account on the remote database and plain user account in the target database. They want to create a database link but don't have access to tnsnames.ora file at the OS level on the server, they may not even have SSH access to the server.
No problem, you can use the full TNS entry pretty much everywhere you can use the TNS alias, just remember to single quote it.

Database link example:
[hlinden@whale dblink]$ rsqlplus /

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Nov 29 11:17:26 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> select db_link from user_db_links;

no rows selected

SQL> CREATE DATABASE LINK prodsyslink
CONNECT TO ro_user IDENTIFIED BY ro_user_pass USING
'(DESCRIPTION=(ADDRESS_LIST=(
ADDRESS=(PROTOCOL=TCP)(HOST=prodserver)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=prod_db)))'
/

Database link created.

SQL> select db_link from user_db_links;

DB_LINK
------------------------------
PRODSYSLINK

SQL> select * from t@prodsyslink;

A B
---------- ----------
1 2
2 1

SQL>
Cool, now it's possible to create a nice materialized view or whatever is needed.

Oracle docs about CREATE DATABASE LINK.

7 comments:

Anonymous said...

Nice tip... Thanks a lot.

Anonymous said...

hi,

can you help with database links?

I need to query TWO tables between DB1 and DB2 but i keep getting an error message that I cannot resolve.
Can you pls help?
Both DB1 and DB2 are in the tnsnames.ora file.

CONNECTED TO DB2 using SQLPLUS OR SQL DEVELOPER UTIL

DB LINK:
CREATE DATABASE LINK DB_1 CONNECT TO 'username' IDENTIFIED BY 'password' USING 'DB1';

TEST LINK:
select sysdate from dual@DB_1

Error report:
SQL Error: ORA-12154: TNS:could not resolve the connect identifier specified
12154. 00000 - "TNS:could not resolve the connect identifier specified"


DB LINK:
CREATE DATABASE LINK DB_1 CONNECT TO 'username' IDENTIFIED BY 'password'
USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST='FullyQualifiedDomainName')(PORT=1521))) (CONNECT_DATA=(SID=DB1)))'

TEST LINK:
select sysdate from dual@DB_1

Error report:
SQL Error: ORA-02085: database link DB_1.XX.YYY.COM connects to DB1
02085. 00000 - "database link %s connects to %s"
*Cause: a database link connected to a database with a different name.
The connection is rejected.
*Action: create a database link with the same name as the database it
connects to, or set global_names=false.

Anonymous said...

hi,

can you help with database links?

I need to query TWO tables between DB1 and DB2 but i keep getting an error message that I cannot resolve.
Can you pls help?
Both DB1 and DB2 are in the tnsnames.ora file.

CONNECTED TO DB2 using SQLPLUS OR SQL DEVELOPER UTIL

DB LINK:
CREATE DATABASE LINK DB_1 CONNECT TO 'username' IDENTIFIED BY 'password' USING 'DB1';

TEST LINK:
select sysdate from dual@DB_1

Error report:
SQL Error: ORA-12154: TNS:could not resolve the connect identifier specified
12154. 00000 - "TNS:could not resolve the connect identifier specified"


DB LINK:
CREATE DATABASE LINK DB_1 CONNECT TO 'username' IDENTIFIED BY 'password'
USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST='FullyQualifiedDomainName')(PORT=1521))) (CONNECT_DATA=(SID=DB1)))'

TEST LINK:
select sysdate from dual@DB_1

Error report:
SQL Error: ORA-02085: database link DB_1.XX.YYY.COM connects to DB1
02085. 00000 - "database link %s connects to %s"
*Cause: a database link connected to a database with a different name.
The connection is rejected.
*Action: create a database link with the same name as the database it
connects to, or set global_names=false.

Vasily Ivanov said...

Oh, you saved my life with this post. :-)

Unknown said...

Very useful. Thanks.

Yaju said...

Very useful post. :)

We were having issues with asking DBA team to add tns entries to the database, and required something to be done in a jiffy. This was when your page helped.

Keep it up!

Jason Smith said...

Hello,
These are amazing tips found here.... great to be here.... Thanks very much for the share.

Regards,
Astermeds.com