DB link

-------Database link------

To create database link the schema required atleast below privilages/roles.
-CREATE DATABASE LINK
-CREATE PUBLIC DATABASE LINK
-CREATE SESSION

asti:- The database where dblink will be created.
demo:-The database from/to which data will be exported/imported.

Lets have a discussion on db link.

1)Check the connectivity between two database should be fine.
connect to source database server and perform tnsping.
example:-
[oracle@localhost datapump]$ tnsping demo

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 11-OCT-2012 23:22:26

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
/home/oracle/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = demo)))
OK (30 msec)

connect to destination database server and perform tnsping.

[oracle@localhost datapump]$ tnsping asti

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 11-OCT-2012 23:53:43

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
/home/oracle/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = asti)))
OK (60 msec)

2) Check the required privileges present in the schema.Connect to the schema and check.

SQL> show user
USER is "LINK_TEST"

SQL> select * from session_privs where PRIVILEGE IN ( 'CREATE SESSION','CREATE DATABASE LINK','CREATE PUBLIC DATABASE LINK');

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK

3)  create the database link in the source database(asti) to connect the scott schema of demo database.

SQL> show user
USER is "LINK_TEST"

SQL>create database link scott_demo connect to scott identified by tiger using 'demo';
Database link created.
SQL> select * from emp@scott_demo;

you will get the result

====================Expdp and impdp using databaselink(db link)================

1) We have created the db link as scott_demo.
create the dump directory. Grant read,write permission to LINK_TEST schema in asti database.

SQL> show user;
USER is "SYS"

SQL> create directory link_demo as '/home/datapump';

Directory created.

SQL> grant read,write on directory link_demo to "LINK_TEST";

Grant succeeded.

2) export the dumpfile from demo database and store in the asti database server. This will connect to asti server and then using dblink it will extract data from demo database.
$ expdp LINK_TEST/test schemas=scott network_link=scott_demo directory=link_demo dumpfile=scott_demo.dmp logfile=scott_demo.log


3) Import the emp table from asti database to demo database. This will connect to asti database throgh LINK_TEST schema.

$impdp LINK_TEST/test tables=scott.emp network_link=scott_demo directory=link_demo logfile=emp_demo.log remap_schema=scott:link_test

#########################################################################
We have completed the private db link.
In order to create public db link you have to use "public "key word in the syntax
Example:-CREATE DATABASE LINK link_name ...;.
 Below both conditon are same.

Creating a Connected User Database Link

To create a connected user database link, omit the CONNECT TO clause.
For example, to create a connected user database link, use the following syntax:

SQL> create database link my_test using 'asti';

Database link created.

Creating a Current User Database Link

To create a current user database link, use the CONNECT TO CURRENT_USER clause in the link creation statement.

SQL> CREATE DATABASE LINK my_scott CONNECT TO CURRENT_USER using 'asti';

Database link created.

SQL>