What are the db links?
create a link from one database to another database is called a database links.database link facilities accessing of the data existing in remote server from A server.
- you must need have the CREATE DATABASE LINK system privileges.
- you must need have the CREATE SESSION system privileges on the remote oracle database.
- you need able to connect to the remote database.
Types of database links:
These are two types of db link
- Private dblink
- Public dblink
In order to create a database link,user should have create database link privileges.using db link, we can perform all sorts of DML operations, SELECT operations , but not DDL operations.
CREATION OF DATABASE LINKS: EXAMPLE
NOTE :- remote database = CHSR local database = DBC
In CHRS database, we have to configure listener.ora file and at clients side we are configure tnsnames.ora file. In this example we have CHSR database in this database i have schema called “SCOTT “,in this schema i have table called EMP,In another server I have a database “DBC” , in this database I have schema that is “CHEYSAR” and i have table DEPT, Now my requirement is i want generate a report by joining emp table to dept table to my local database (DBC)
syntax 1: CREATE DATABASE LINK link_name;
syntax 2: CREATE DATABASE LINK link_name CONNECT TO user IDENTIFIED BY password USING ‘service_name’:
Private Database Link
This Steps needs to executed in Local Database
SYS> GRANT CREATE DATABASE LINK TO CHEYSAR;
CHEYSAR> CREATE DATABASE LINK dblink_remote CONNECT TO SCOTT IDENTIFIED BY TIGER USING ‘CHSR’;
CHEYSAR> SELECT * FROM EMP@dblink_remote;
SYS> GRANT CREATE SYNONYM TO CHEYSAR;
CHEYSAR> CREATE SYNONYM EMP FOR EMP@dblink_remote;
CHEYSAR> SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO;
Public Database Link
CREATE PUBLIC DATABASE LINK scott_remote CONNECT TO scott Identified by tiger USING ‘CHSR’;