How to Manage Database Link in Oracle Database




·    What is Database link?
       
       A database link is a schema object in one database that enables you to access objects on another database. The other database needs to be an Oracle Database system. However, to access non-Oracle systems you must have to use Oracle Heterogeneous Services

       Once you have created a database link, you can use it to refer to tabled and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLES statement.

·     Privileges
o          
  •       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.

o    And also you must have the CREATE SESSION system privilege on the remote Oracle database.

GRANT CREATE DATABASE LINK TO ;
GRANT CREATE PUBLIC DATABASE LINK TO ;
GRANT DROP PUBLIC DATABASE LINK TO ;
·     
      Syntax
o    
      Private database link syntax

CREATE DATABASE LINK CONNECT TO IDENTIFIED BY USING ;

o    Public database link syntax

CREATE PUBLIC DATABASE LINK CONNECT TO IDENTIFIED BY USING ;

·    Public Database and Private Database link

      A public database link is available to all users. If you omit this clause, the database link is private and available only to you.

·     Examples
o    
       Create private db link

CREATE DATABASE LINK CONNECT_PROD CONNECT TO VINOD IDENTIFIED BY ORACLE USING ‘PROD’;

Note: connect_prod is the database link name; also this is a private link so only the user who created the link can use it.


o   Create public db link

CREATE PUBLIC DATABASE LINK CONNECT_PROD CONNECT TO VINOD IDENTIFIED BY ORACLE USING ‘PROD’;

Note: any user can use the link.

o  Drop a database link

DROP PUBLIC DATABASE LINK ;
DROP DATABASE LINK ;

o   Close a database link

ALTER SESSION CLOSE DATABASE LINK ;

o   Views:
        
     USER_DB_LINKS
§   ALL_DB_LINKS
§   DBA_DB_LINKS

                                           ------------------------

No comments:

Post a Comment

Execution Plan & Statistics