Oracle database links using full TNS entries

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.