CREATE DATABASE LINK / DROP DATABASE LINK

CREATE DATABASE LINK command


PURPOSE: To create a database link. A database link is an object in the local database that allows you to access objects on a remote database or to mount a secondary database in read-only mode. The remote database can be either an Oracle or a non-Oracle database.

SYNTAX:

CREATE [PUBLIC] DATABASE LINK dblink [CONNECT TO user IDENTIFIED BY password] [USING 'connect_string']

where:

• PUBLIC creates a public database link available to all users. If you omit this option, the database link is private and is available only to you. dblink is the complete or partial name of the database link. For guidelines for naming database links, see the section Referring to Objects in Remote Databases.
• CONNECT TO user IDENTIFIED BY password is the username and password used to connect to the remote database. If you omit this clause, the database link uses the username and password of each user who uses the database link.
• USING specifies either: * the database specification of a remote database * the specification of a secondary database for a read-only mount. For information on specifying remote databases, see the SQL*Net User's Guide for your specific SQL*Net protocol. Read-only mounts are only available in Trusted Oracle and can only be specified for public database links.
  

PREREQUISITES: To create a private database link, you must have CREATE DATABASE LINK system privilege. To create a public database link, you must have CREATE PUBLIC DATABASE LINK system privilege. Also, you must have CREATE SESSION privilege on a remote database. SQL*Net must be installed on both the local and remote databases.

Drop a Database Link.

Syntax:

   DROP [PUBLIC] DATABASE LINK link_name

Restrictions: You cannot drop a database link in another user's schema and you must specify PUBLIC to drop a PUBLIC database link

Sources:
http://www.ss64.com/ora/link_d.html
http://www-eleves-isia.cma.fr/documentation/OracleDoc/CREATE-DATABASE-LINK.html