DATABASE LINK IN ORACLE
What is database link?
A database link is a schema object in a database.
The main purpose of the database link is to access database objects present in another database or remote database.
A database link creates a connection between a local database and a remote database.
A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server.
The remote database can be same oracle or it can be non-oracle database.
To access non-Oracle systems you must use Oracle Heterogeneous Services.
Database links dependencies
Database link having following dependencies those are.
- Server level networking.
- Database level networking.
1. Server level networking.
Let’s assume we have two servers server1 and server2 both are having the databases. In order to create the database link first there should be a networking between both servers.
2. Database level networking.
In order to create the database link first we have to configure and start the oracle net services using listener.ora and tnsnames.ora.
Configure the database level networking (Oracle server to Oracle server):
SERVER1:
|
1 2 |
[oracle@server1 ~]$ hostname –i 192.168.0.1 |
SERVER2:
|
1 2 |
[oracle@server2 ~]$ hostname –i 192.168.0.2 |
SERVER1:
|
1 |
[oracle@server1 ~]$ ping 192.168.0.2 |
It should communicate with server2
SERVER2:
|
1 |
[oracle@server2 ~]$ ping 192.168.0.1 |
It should communicate with server1
SERVER1:
|
1 |
[oracle@server1 ~]$ sqlplus / as sysdba |
Please start the database.
|
1 2 |
SQL>> startup; SQL>> exit |
Configure the listener:
|
1 2 3 |
[oracle@server1 ~]$ cd $ORACLE_HOME/network/admin [oracle@server1 ~]$ ls –l [oracle@server1 ~]$ vi listener.ora |
Add the below contents:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
list1 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521)) ) ) ) SID_LIST_list1= (SID_LIST = (SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome) (SID_NAME = db1) ) ) |
Save the file
Start listener:
|
1 |
[oracle@server1 ~]$ lsnrctl start list1 |
Check listener status:
|
1 |
[oracle@server1 ~]$ lsnrctl status list1 |
SERVER2:
|
1 |
[oracle@server2 ~]$ sqlplus / as sysdba |
Please start the database.
|
1 2 |
SQL>> startup; SQL>> exit |
Configure the listener:
|
1 2 3 |
[oracle@server2 ~]$ cd $ORACLE_HOME/network/admin [oracle@server2 ~]$ ls –l [oracle@server2 ~]$ vi tnsnames.ora |
Add the below contents:
|
1 2 3 4 5 6 7 8 9 |
list1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521)) ) (CONNECT_DATA = (SID = db1) ) ) |
Save the file
Test the net services communication:
|
1 |
[oracle@server2 ~]$ tnsping list1 |
It should communicate with server1
Oracle Net services are configured and started successfully.
Types of Database Links:
Following are the different database links.
- Private
- Public
- Global
Private database link:
Private database link created by a specific schema of the local database. Only the owner can use this link to access database objects in the corresponding remote database. This link is more secure than a public or global link.
Command:
|
1 |
CREATE DATABASE LINK <link_name> CONNECT TO <username> IDENTIFIED BY <password> USING '<alias_name>'; |
Example:
|
1 |
CREATE DATABASE LINK dblink1 CONNECT TO scott IDENTIFIED BY tiger USING 'list1'; |
Public database link:
Public database link is a database-wide link.
Means all users in the local database can use the link to access database objects in the corresponding remote database.
Command:
|
1 |
CREATE PUBLIC DATABASE LINK <link_name> CONNECT TO <username> IDENTIFIED BY <password> USING '<alias_name>'; |
Example:
|
1 |
CREATE PUBLIC DATABASE LINK dblink1 CONNECT TO scott IDENTIFIED BY tiger USING 'list1'; |
Global database link:
Global database link is nothing but a network-wide link. When an Oracle network uses a directory server, the directory server automatically creates and manages global database links (as net service names) for every Oracle Database in the network.
Using oracle net service administrator can easily manage global database links. Global Database link management is centralized and simple.
Data Dictionary Views:
Using following data dictionary view we can find the information of database links.
- DBA_DB_LINKS
- ALL_DB_LINKS
- USER_DB_LINKS
Uses with Database links:
Using the database link local database user can perform following tasks.
- DRL Operations
- DML Operations
DRL Operations:
User can select / retrieve the data and can generate reports.
Command:
|
1 |
SELECT * FROM <OBJECT_NAME>@<DB_LINK_NAME>; |
Example:
|
1 |
SELECT * FROM EMP@dblink1; |
DML Operations:
User can perform insert, update and delete operations on remote database objects.
Command:
|
1 |
INSERT INTO <OBJECT_NAME>@<DB_LINK_NAME> VALUES (VAL1, VAL2, VAL3, ....); |
Example:
|
1 |
INSERT INTO DEPT@dblink1 VALUES (50,'management','dallas'); |
|
1 |
Commit; |
Loading...
