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 privilages. 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
As a server in CHRS database, we are confgure listener.ora file and at clients side we are configure tnsnames.ora file . let assume I have CHSR database in this particular database i have scema that is “SCOTT “,in this schema i have table EMP, And I have some another server, In This server I have one database “DBC” , in this database I have schema that is “CHEYSAR” and i have table DEPT, Now my requirement is i want genearte a report by joining emp table to dept table.
syntax 1: CREATE DATABASE LINK link_name;
synatax 2: CREATE DATABSE LINK link_name CONNECT TO user IDENTIFIED BY password USING ‘service’:
SYS> GRANT CREATE DATABASE LINK TO CHEYSAR;
CHEYSAR> CREATE DATABASE LINK dblink.chsr CONNECT TO SCOTT IDENTIFIED BY TIGER USING ‘to_chsr’;
CHEYSAR> SELECT * FROM EMP@dblink.chsr;
SYS> GRANT CREATE SYNONYM TO CHEYSAR;
CHEYSAR> CREATE SYNONYM EMP FOR EMP@dblink.chsr;
CHEYSAR> SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO;