Dblink in oracle

   DATABASE LINK IN ORACLE

1. A database link is a schema object in one database that enables you to access objects on another database.
2. CREATE DATABASE LINK statement to create a database link.
3. The other database need not be an Oracle Database system.
4.  However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

•  After created a database link, you can use it to refer to tables and views on the other database.
•  You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.

Prerequisites:-

• To create a private database link, you must have the CREATE DATABASE LINK system privilege. 
• To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege.
• you must have the CREATE SESSION system privilege on the remote Oracle database.

NOTE:-
Oracle Net must be installed on both the local and remote Oracle databases. (tns and Listener)

Type of dblink:-
1.Public.
2.Private.
3.Global.

GRANT PRIVILEGES:- 
Sql>grant create database link to username;
Sql>grant create public database link to username;

TYPES:-

1.private database link:-
Create link in specific schema of the local database.
May be used only by the link owner.

Syntax:-
Sql>create  database link <db link name>  connect to <username> identified by <passwd> using ‘tnsname’;

Example:-
Sql>create   database link xla1 connect to u1 identified by u1 using ‘tnsxla12’;

2.public database link:-
A   public database link that can be used by any user.
You can access remote objects using insert,update and  delete statement.
PUBLIC to create a public database link available to all users.
If you omit this clause, the database link is private and is available only to you.

Syntax:-
Sql>Create public database link<db link name> connect to <username> identified by <passwd> using ‘tnsname’;

Example:-
Sql>create public database link xla1 connect to u1 identified by u1 using ‘tnsla1’;

DATABASE LINK RESTRICTIONS:- 

• Grant privileges on remote  objects.
• Do support describe operations –tables,views,functions,procedures,mviews,packages.
• You cannot create a database link in another user's schema, and you cannot qualify dblink with the name of a schema. 

CLOSE DATABASE LINK:-

Syntax:-
Sql>alter session close database link  <link name>;

Example:-
Sql>alter session close database link xla1  ;

DROPPING A DATABASE LINK:-

Sql>drop database link <linkname>;
Sql>drop public database link <linkname>;

VIEWS:-

All_db_links
Dba_db_links
User_db_links

The local  database user can view the object.

Sql>select *  from tab@<dblinkname>;
Sql>select * from tab@xla1;


col username for a9
col host for a10
col DB_LINK for a10

 select owner,db_link,username,host,created from DBA_DB_LINKS where db_link='DB';