Schema

Database Links

Create
Display
Drop
Usage

Database Link Gotchas

Database Links rely on the tnsnames.ora entry on the database server when accessing a remote servers data. Set using this part of command:
USING 'LocalSystem_tnsnames_ServiceName'
In some cases you may need to CREATE\DROP the database link logged in as the user\schema that hosts the data for it. SYS may not work!
Example: SCOTT> CREATE DATABASE LINK...

Create

CREATE [public] DATABASE LINK [schema.]<LinkName>
CONNECT TO RemoteDB_Username IDENTIFIED BY "RemoteDB_UserPassword"
USING 'LocalSystem_tnsnames_ServiceName';

CREATE DATABASE LINK srv1_hr
CONNECT TO hr IDENTIFIED BY "password"
USING 'srv1_hr';

Display

COL owner     FORMAT a15
COL db_link   FORMAT a15
COL username  FORMAT a15
COL host      FORMAT a15
COL created   FORMAT a15
SELECT owner,db_link,username,host,created FROM dba_db_links;

Drop

DROP DATABASE LINK [schema.]<LinkName>;

DROP DATABASE LINK srv1_hr;
DROP PUBLIC DATABASE LINK srv1_hr;

Usage

SELECT col1,col2,col3... FROM RemoteDBTableName@LinkName;

Creation Session

From local schema-user@database (AUTOMIC@ORADB1) perform the following.

 --If Needed
 AUTOMIC@ORADB1> DROP DATABASE LINK AWBATCH;

 -- Make tnsnames.ora Entry to Connect to Remote DB (DNATRA)
 DNATRA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.42 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dnatra)
    )
  )

 -- Create DB Link to use Above tnsnames.ora Entry
 AUTOMIC@ORADB1> CREATE DATABASE LINK AWBATCH 
                 CONNECT TO AWBATCH IDENTIFIED BY "********" 
                 USING 'DNATRA';

 -- Note
 CREATE DATABASE LINK AWBATCH, could have used any name (MY_DB_LINK) etc.
 Using the user-schema name on the remote DB (AWBATCH) for the link name is self documenting.

 -- Test Connecting to Remote DB From Current
 AUTOMIC@ORADB1> SELECT count(*) FROM OSI_PRIMARY@AWBATCH;

<- Schema