DBA Hub

📋Steps in this guide1/9

Database link

Database link 1. Overview 2. Environment 3. Add TNS Entry 4. List db links 5. Create PUBLIC db link 6. Create PRIVATE db link 7. List db links again 8. Verify the db link results 9. Drop Public Database link 10. Drop Private Database link 1. Overview A database link (DBlink) is a definition of … Continue reading Database Link →

oracle clusteringintermediate
by OracleDba
14 views
1

Overview

1. Overview Type of Database Links: 2. Environment

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
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

Section 2

Source Details Target Details 3. Add TNS Entry

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
26
27
28
29
30
31
Hostname:
rac1.rajasekhar.com
DB Name:
w148p
Schema name/password:
scott/tiger
TNS Entry:
w148p =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = w148p)
    )
  )

Hostname:
rac2.rajasekhar.com
DB Name:
CAT
Schema name/password:
test/test
TNS Entry:
CAT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cat)
    )
  )
3

Section 3

Add target db TNS entry in source database tnsnames.ora 4. List db links 5. Create PUBLIC database link

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Add target db TNS entry in source database tnsnames.ora
CAT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cat)
    )
  )

SQL>
select * from dba_db_links;
no rows selected

SQL>
4

Section 4

5. Create PUBLIC database link We want to access the TEST schema objects (resides on CAT database) from source db (w148p Database) --- OR --- -- OR -- -- Create PUBLIC DB Link Using EASY CONNECT

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
-- create public db link On Source db W148P
We want to access the TEST schema objects (resides on CAT database) from source db (w148p Database)
SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
W148P     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 'CAT';
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 = rac2.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cat)
    )
  )'
  /
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 = rac2.rajasekhar.com)(PORT = 1521))
  6      (CONNECT_DATA =
  7        (SERVER = DEDICATED)
  8        (SERVICE_NAME = cat)
  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 'rac2.rajasekhar.com:1521/CAT';
2    3

Database link created.

SQL>
5

Section 5

6. Create PRIVATE database Link PROXY USER -- 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
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
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 'CAT';
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 = rac2.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cat)
    )
  )'
  /
SQL> CREATE DATABASE LINK REMOTE_PRIVATE2
 CONNECT TO test IDENTIFIED BY test
  2    3   using
  4   '(DESCRIPTION =
  5      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
  6      (CONNECT_DATA =
  7        (SERVER = DEDICATED)
  8        (SERVICE_NAME = cat)
  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 'rac2.rajasekhar.com:1521/CAT';
2    3

Database link created.

SQL>

SQL>
conn / as sysdba
Connected.
SQL>
revoke create database link from scott; <----
Revoke succeeded.

SQL>
6

Section 6

-- OR -- -- Create DB Link Using EASY CONNECT 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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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            CAT                                     11/06/2016 21:02:22
PUBLIC
TEST_REMOTE1    TEST            (DESCRIPTION =                          11/06/2016 21:37:58
                                                    (ADDRESS = (PROTOCOL = TCP)(HOST =
                                                rac2.rajasekhar.com)(PORT = 1521))
                                                    (CONNECT_DATA =
                                                      (SERVER = DEDICATED)
                                                      (SERVICE_NAME = cat)
                                                    )
                                                  )
PUBLIC
TEST_REMOTE2    TEST            rac2.rajasekhar.com:1521/CAT            11/06/2016 21:43:00
SCOTT
REMOTE_PRIVATE1 TEST            CAT                                     11/06/2016 22:10:47
SCOTT
REMOTE_PRIVATE2 TEST            (DESCRIPTION =                          11/06/2016 22:11:25
                                                    (ADDRESS = (PROTOCOL = TCP)(HOST =
                                                rac2.rajasekhar.com)(PORT = 1521))
                                                    (CONNECT_DATA =
                                                      (SERVER = DEDICATED)
                                                      (SERVICE_NAME = cat)
                                                    )
                                                  )
SCOTT
REMOTE_PRIVATE3 TEST            rac2.rajasekhar.com:1521/CAT            11/06/2016 22:12:13

6 rows selected.

SQL>
7

Section 7

8. Verify the db link results --- verfiy public db links --- --- verfiy private db links --- 9. Drop Public 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
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
--
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(*)
----------
    918843

SQL>
select count(*) from sales@
TEST_REMOTE1
; <-- with TNS Connect String
COUNT(*)
----------
    918843

SQL>
select count(*) from sales@
TEST_REMOTE2
; <-- Easy connect string
COUNT(*)
----------
    918843

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(*)
----------
    918843

SQL>
select count(*) from sales@
REMOTE_PRIVATE2
;<-- TNS connect string
COUNT(*)
----------
    918843

SQL>
select count(*) from sales@
REMOTE_PRIVATE3
; <-- Easy connect
COUNT(*)
----------
    918843

SQL>
8

Section 8

10. Drop Private Database link -- Please login as owner of db link Caution : Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- 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>

-- 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>
9

Section 9

Caution : Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using. Reference http://www.morganslibrary.org/reference/db_link.html https://oracle-base.com/articles/misc/managing-database-links

Comments (0)

Please to add comments

No comments yet. Be the first to comment!