Monday , September 25 2017
Home / Oracle DBA / Oracle Database Links

Oracle Database Links

DATABASE LINKS:

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.

PREREQUISITES:

  1. you must need  have the CREATE DATABASE LINK system privileges.
  2. you must need  have the CREATE SESSION system privileges on the remote  oracle database.
  3. you need able to connect to the remote database.

Types of database links:

These are two types of db link

  1. Private dblink
  2. 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’;

 

 

 

 

Comments

comments

Check Also

How to switch on primary database to physical standby database

After configuration data guard then data is switching  into primary database  to standby database : …

Leave a Reply

Your email address will not be published. Required fields are marked *