Creating DB Links

Scenario:

we have a schema pavan in ORCL(11g) database. A user krishna in another database TEST(12c) want to view the data in pavan schema in ORCL database.

solution:

create DB link in TEST(12c) database to pavan schema in ORCL(11g) database.

 
Steps:
------------

ORCL(11g):
----------------
create user pavan and grant required privileges to the user pavan. connect to pavan and create table EMP and insert rows into the table EMP


SQL> create user pavan identified by oracle;



SQL> grant connect, resource to pavan;

SQL> grant unlimited tablespace to pavan;


SQL> conn pavan/oracle


SQL> create table emp (id number(10), name varchar2(50), dept varchar(20));


SQL> insert into emp values (1, 'krishna','eee');

SQL> insert into emp values (2, 'sai','ece');

SQL> insert into emp values (3, 'trilok','eee');

SQL> insert into emp values (4, 'naresh','eee');

SQL> insert into emp values (5,'lucky','it');

SQL> insert into emp values (6, 'bhanu','ece');

SQL> insert into emp values (7, 'siva','mech');



Tns entries:
-----------------------

ORCL(11g):
---------------
vi tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.104)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )



TEST(12c):
--------------
Add tns entry of ORCL(11g) database to tnsnames.ora file in TEST(12c) database

vi tnsnames.ora

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.104)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )



TEST(12c):
--------------
create user krishna and grant required privileges along with CREATE DATABASE LINK Privilege to the user krishna. connect to krishna and create DB LINK using below command.


SQL> create user krishna identified by oracle;

SQL> grant connect, resource to krishna;

SQL> grant unlimited tablespace to krishna;

SQL> grant create database link to krishna;



Creating DB link:
-----------------------

SQL> create database link pavan_schema connect to "PAVAN" identified by oracle using 'orcl';



SQL> select * from pavan.emp@pavan_schema;

        ID NAME                 DEPT
---------- -------------------- --------------------
########## krishna              eee
########## sai                  ece
########## trilok               eee
########## naresh               eee
########## lucky                it
########## bhanu                ece
########## siva                 mech

7 rows selected.




views:
--------
DBA_DB_LINKS

ALL_DB_LINKS

USER_DB_LINKS