oracledba.help
Schema

Database Links

Database Link Gotchas

  1. 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'
  2. 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;

<- Schema