oracle db_link dblink - ghdrako/doc_snipets GitHub Wiki

Link types

  • Private database link - belongs to a specific schema of a database. Only the owner of a private database link can use it.
  • Public database link - all users in the database can use it.
  • Global database link - defined in an OID or Oracle Names Server. Anyone on the network can use it.

Privilages

grant create database link to esspa;

Create

CREATE [SHARED] [PUBLIC] DATABASE LINK <link_name>
CONNECT TO CURRENT_USER
USING '<service_name>';

CREATE [PUBLIC] DATABASE LINK <link_name>
CONNECT TO <user_name>
IDENTIFIED BY <password>
USING '<service_name>';


CREATE SHARED DATABASE LINK shared
CONNECT TO scott IDENTIFIED BY tiger
AUTHENTICATED BY uwclass IDENTIFIED BY uwclass
USING 'conn_link';

CREATE PUBLIC DATABASE LINK publink
USING 'conn_link';

CREATE [ SHARED ] [ PUBLIC ] DATABASE LINK dblink
  [ CONNECT TO
    { CURRENT_USER
    | USER IDENTIFIED BY password
      [ dblink_authentication ]
    }
  | dblink_authentication
  ]
  [ USING 'connect_string' ] ;
 
 
conn_link =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = my_host_name)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = name_of_database)
    )
  )

Info

SELECT * FROM user_db_links;
SELECT db_link, username, password, host, created
FROM user_db_links;


SELECT * FROM all_db_links;


select * from user_db_links
select * from all_db_links
⚠️ **GitHub.com Fallback** ⚠️