Friday , July 21 2017
Home / Oracle DBA / DATA BASE LINKS

DATA BASE 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 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;

 

 

 

Comments

comments

Check Also

How to Change in SQL Prompt with your name ?

how to change SQL prompt: [rootdbcentre5 ~]# su – oracle [oracle@dbcentre5 ~]$ cd $ORACLE_HOME/sqlplus/admin [oracle@dbcentre5 …

Leave a Reply

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