Database link A database link (DBlink) is a definition of how to establish a connection from one Oracle database to another. Type of Database Links: 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. How to find Global name? SELECT * FROM global_name; 2. Environment Source Details Hostname: server1.localdomain DB Name: orcl Schema name/password: scott/tiger TNS Entry: orcl = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server1.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) Target Details Hostname: server2.localdomain DB Name: test Schema name/password: test/test TNS Entry: test = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server2.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test) ) ) 3. Add TNS Entry Add target db TNS entry in source database tnsnames.ora test = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server2.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test) ) ) 4. List db links SQL> select * from dba_db_links; no rows selected SQL> 5. Create PUBLIC database link -- create public db link On Source db orcl We want to access the TEST schema objects (resides on test database) from source db (orcl Database) SQL> select name, open_mode from v$database; NAME OPEN_MODE --------- ---------- orcl READ WRITE SQL> show user USER is "SYS" SQL> CREATE PUBLIC DATABASE LINK link_name CONNECT TO remote_user_name IDENTIFIED BY remote_user_password USING 'remote_service_name'; SQL> CREATE PUBLIC DATABASE LINK test_remote CONNECT TO test IDENTIFIED BY test USING 'test'; 2 3 Database link created. SQL> --- OR --- -- Create Public db link without modify TNS entry CREATE PUBLIC DATABASE LINK test_remote1 CONNECT TO test IDENTIFIED BY test using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server2.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test) ) )' / SQL> show user USER is "SYS" SQL> CREATE PUBLIC DATABASE LINK test_remote1 CONNECT TO test IDENTIFIED BY test 2 3 using 4 '(DESCRIPTION = 5 (ADDRESS = (PROTOCOL = TCP)(HOST = server2.localdomain)(PORT = 1521)) 6 (CONNECT_DATA = 7 (SERVER = DEDICATED) 8 (SERVICE_NAME = test) 9 ) 10 )' 11 / Database link created. SQL> -- OR -- -- Create PUBLIC DB Link Using EASY CONNECT SQL> CREATE PUBLIC DATABASE LINK test_remote2 CONNECT TO test IDENTIFIED BY test USING 'server2.localdomain:1521/test'; 2 3 Database link created. SQL> 6. Create PRIVATE database Link Private database link belongs to a specific schema of a database. Only the owner of a private database link can use it. CREATE DATABASE LINK link_name CONNECT TO remote_user_name IDENTIFIED BY remote_user_password USING 'remote_service_name'; SQL> grant create database link to scott; Grant succeeded. SQL> conn scott/tiger; <-- If you don't know password then use proxy user . PROXY USER Connected. SQL> SQL> CREATE DATABASE LINK REMOTE_PRIVATE1 CONNECT TO test IDENTIFIED BY test USING 'test'; 2 3 Database link created. SQL> -- OR -- -- Create PRIVATE db link without modify TNS entry CREATE DATABASE LINK REMOTE_PRIVATE2 CONNECT TO test IDENTIFIED BY test using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server2.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test) ) )' / SQL> CREATE DATABASE LINK REMOTE_PRIVATE2 CONNECT TO test IDENTIFIED BY test 2 3 using 4 '(DESCRIPTION = 5 (ADDRESS = (PROTOCOL = TCP)(HOST = server2.localdomain)(PORT = 1521)) 6 (CONNECT_DATA = 7 (SERVER = DEDICATED) 8 (SERVICE_NAME = test) 9 ) 10 )' 11 / Database link created. SQL> -- OR -- -- Create DB Link Using EASY CONNECT SQL> CREATE DATABASE LINK REMOTE_PRIVATE3 CONNECT TO test IDENTIFIED BY test USING 'server2.localdomain:1521/test'; 2 3 Database link created. SQL> SQL> conn / as sysdba Connected. SQL> revoke create database link from scott; Revoke succeeded. SQL> 7. List database links again SQL> set lines 180 pages 999 SQL> col owner for a15 SQL> col DB_LINK for a15 SQL> col USERNAME for a15 SQL> col HOST for a39 SQL> col CREATION_DATE for a20 SQL> select owner, db_link, username, host , to_char(created,'MM/DD/YYYY HH24:MI:SS') creation_date from dba_db_links; OWNER DB_LINK USERNAME HOST CREATION_DATE --------------- --------------- --------------- --------------------------------------- -------------------- PUBLIC TEST_REMOTE TEST test 11/12/2021 21:02:22 PUBLIC TEST_REMOTE1 TEST (DESCRIPTION = 11/12/2021 21:37:58 (ADDRESS = (PROTOCOL = TCP)(HOST = server2.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDItestED) (SERVICE_NAME = test) ) ) PUBLIC TEST_REMOTE2 TEST server2.localdomain:1521/test 11/12/2021 21:43:00 SCOTT REMOTE_PRIVATE1 TEST test 11/12/2021 22:10:47 SCOTT REMOTE_PRIVATE2 TEST (DESCRIPTION = 11/12/2021 22:11:25 (ADDRESS = (PROTOCOL = TCP)(HOST = server2.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDItestED) (SERVICE_NAME = test) ) ) SCOTT REMOTE_PRIVATE3 TEST server2.localdomain:1521/test 11/12/2021 22:12:13 6 rows selected. SQL> 8. Verify the db link results -- Since it is public db link any user in source database can access the TEST schema objects of targert db -- Even new user can access. eg.... create new user --- verfiy public db links --- SQL> create user one identified by one; User created. SQL> grant connect to one; Grant succeeded. SQL> conn one/one; Connected. SQL> select count(*) from sales@TEST_REMOTE; <-- with TNS entry COUNT(*) ---------- 1211231 SQL> select count(*) from sales@TEST_REMOTE1; <-- with TNS Connect String COUNT(*) ---------- 1211231 SQL> select count(*) from sales@TEST_REMOTE2; <-- Easy connect string COUNT(*) ---------- 1211231 SQL> --- verfiy private db links --- Private database link belongs to a specific schema of a database. Please note only the owner of a private database link can use it. SQL> conn scott/tiger; Connected. SQL> select count(*) from sales@REMOTE_PRIVATE1; <-- With TNS Entry COUNT(*) ---------- 1211231 SQL> select count(*) from sales@REMOTE_PRIVATE2;<-- TNS connect string COUNT(*) ---------- 1211231 SQL> select count(*) from sales@REMOTE_PRIVATE3; <-- Easy connect COUNT(*) ---------- 1211231 SQL> 9. Drop Public Database link -- Please login as owner of db link SQL> drop public database link TEST_REMOTE; Database link dropped. SQL> drop public database link TEST_REMOTE1; Database link dropped. SQL> drop public database link TEST_REMOTE2; Database link dropped. SQL> 10. Drop Private Database link -- Please login as owner of db link SQL> conn scott/tiger; <---- Connected. SQL> drop database link REMOTE_PRIVATE1; Database link dropped. SQL> drop database link REMOTE_PRIVATE2; Database link dropped. SQL> drop database link REMOTE_PRIVATE3; Database link dropped. SQL> SQL> conn / as sysdba Connected. SQL> select * from dba_db_links; no rows selected <---- SQL>