Database Links
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;