DBA Hub

📋Steps in this guide1/11

Oracle Database Link

Master the creation of Oracle Database links with clear examples, including TNS entries and methods to verify your link results effectively.

oracle configurationintermediate
by OracleDba
37 views
1

Overview

Database link - Overview - Environment - Add TNS Entry - List db links - Create PUBLIC db link - Create PRIVATE db link - List db links again - Verify the db link results - Drop Public Database link - Drop Private Database link 1.Overview 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.
2

Section 2

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: 192.168.0.197
3

Section 3

DB Name: TESTDB Schema name/password: C##SCOTT/abc123 Target Details Hostname: xx.xxx.xxx.xx DB Name: BATCH46

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
TNS Entry: 

TESTDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.197)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTDB)
    )
  )
4

Section 4

Schema name/password: NASREEN/abc123 TNS Entry: 3.Add TNS Entry Add target db TNS entry in source database tnsnames.ora

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
BATCH46 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xxx.xxx.xx )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BATCH46)
    )
  )

BATCH46 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xxx.xxx.xx ) (PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BATCH46)
    )
  )
5

Section 5

4. List db links 5. Create PUBLIC database link — create public db link On Source db TESTDB We want to access the NASREEN schema objects (resides on BATCH46 database) from source db (TESTDB Database)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> select * from dba_db_links;

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
TESTDB     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>
6

Section 6

— Create Public db link without modify TNS entry — OR — — Create PUBLIC DB Link Using EASY CONNECT 6.Create PRIVATE database Link

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE PUBLIC DATABASE LINK link_three
 CONNECT TO NASREEN IDENTIFIED BY abc123
 using
 ' (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xxx.xxx.xx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BATCH46)
    )
  )'
  /

SQL> show user
USER is "SYS"

CREATE PUBLIC DATABASE LINK link_five
CONNECT TO NASREEN IDENTIFIED BY abc123
USING 'xx.xxx.xxx.xx:1521/BATCH46';
7

Section 7

Private database link belongs to a specific schema of a database. Only the owner of a private database link can use it. . — OR — — Create PRIVATE db link without modify TNS entry

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
SQL> conn C##ABC; <-- If you don't know password then use proxy user . PROXY USER
Connected.
SQL>

SQL> CONN C##ABC/abc123
Connected.

SQL> CREATE DATABASE LINK LINK_PRIVATE1
   CONNECT TO NASREEN IDENTIFIED BY abc123
   USING 'BATCH46';
  2    3
Database link created.
8

Section 8

— OR — 7. List database links again 8. Verify the db link results

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE DATABASE LINK LINK_PRIVATE2
 CONNECT TO NASREEN IDENTIFIED BY abc123
 using
 '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xxx.xxx.xx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BATCH46)
  )
  )'
  /

-- Create DB Link Using EASY CONNECT

CREATE DATABASE LINK LINK_PRIVATE3
CONNECT TO NASREEN IDENTIFIED BY abc123
USING 'xx.xxx.xxx.xx:1521/BATCH46';

set lines 180 pages 999
col owner for a15
col DB_LINK for a15
col USERNAME for a15
col HOST for a39
col CREATION_DATE for a20
select owner, db_link, username,host ,to_char(created,'MM/DD/YYYY HH24:MI:SS') creation_date from dba_db_links;
9

Section 9

— 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 — — verfiy private db links — Private database link belongs to a specific schema of a database.

Code/Command (click line numbers to comment):

1
2
3
SQL> grant connect to C##TWO;

Grant succeeded.
10

Section 10

Please note only the owner of a private database link can use it. 9.Drop Public Database link — Please login as owner of db link 10.Drop Private Database link — Please login as owner of db link

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
drop public database link LINK_TWO;

drop public database link LINK_THREE;

 drop public database link LINK_FOUR;

drop public database link LINK_FIVE;
11

Section 11

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
SQL>

SQL> conn / as sysdba
Connected.

set lines 180 pages 999
col owner for a15
col DB_LINK for a15
col USERNAME for a15
col HOST for a39
col CREATION_DATE for a20
select owner, db_link, username,host ,to_char(created,'MM/DD/YYYY HH24:MI:SS') creation_date from dba_db_links;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!