CREATE DATABASE LINK

Purpose

Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in the local database that enables you to access objects on a remote database. The remote database need not be an Oracle system.

Once you have created a database link, you can use it to refer to tables and views on the remote database. You can refer to a remote table or view in a SQL statement by appending @dblink to the table or view name. You can query a remote table or view with the SELECT statement. If you are using Oracle with the distributed option, you can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.

See Also: Oracle8i Application Developer's Guide - Fundamentals for information about accessing remote tables or views with PL/SQL functions, procedures, packages, and datatypes Oracle8i Distributed Database Systems for information on distributed database systems Oracle8i Reference for descriptions of existing database links in the ALL_DB_LINKS, DBA_DB_LINKS, and USER_DB_LINKS data dictionary views and to monitor the performance of existing links through the V$DBLINK dynamic performance view DROP DATABASE LINK for information on dropping existing database links INSERT, UPDATE, DELETE, and LOCK TABLE for using links in DML operations

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 the remote Oracle database.

Net8 must be installed on both the local and remote Oracle databases.

To access non-Oracle systems you must use the Oracle Heterogeneous Services.

Syntax

authenticated_clause::=

Keyword and Parameters

SHARED

Specify SHARED to use a single network connection to create a public database link that can be shared between multiple users. This clause is available only with the multi-threaded server configuration.

See Also: Oracle8i Distributed Database Systems for more information about shared database links

PUBLIC

Specify 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.

See Also: The "PUBLIC Database Link Example"

dblink

Specify the complete or partial name of the database link. The value of the GLOBAL_NAMES initialization parameter determines whether the database link must have the same name as the database to which it connects.

The maximum number of database links that can be open in one session or one instance of an Oracle Parallel Server configuration depends on the value of the OPEN_LINKS and OPEN_LINKS_PER_INSTANCE initialization parameters.

Restriction: You cannot create a database link in another user's schema, and you cannot qualify dblink with the name of a schema. (Periods are permitted in names of database links, so Oracle interprets the entire name, such as ralph.linktosales, as the name of a database link in your schema rather than as a database link named linktosales in the schema ralph.)

See Also: "Referring to Objects in Remote Databases" for guidelines for naming database links Oracle8i Reference for information on the GLOBAL_NAMES, OPEN_LINKS, and OPEN_LINKS_PER_INSTANCE initialization parameters

CONNECT TO

The CONNECT TO clause lets you enable a connection to the remote database.

CURRENT_USER	Specify CURRENT_USER to create a current user database link. The current user must be a global user with a valid account on the remote database for the link to succeed. If the database link is used directly, that is, not from within a stored object, then the current user is the same as the connected user.
	When executing a stored object (such as a procedure, view, or trigger) that initiates a database link, CURRENT_USER is the username that owns the stored object, and not the username that called the object. For example, if the database link appears inside procedure scott.p (created by scott), and user jane calls procedure scott.p, the current user is scott.
	However, if the stored object is an invoker-rights function, procedure, or package, the invoker's authorization ID is used to connect as a remote user. For example, if the privileged database link appears inside procedure scott.p (an invoker-rights procedure created by scott), and user jane calls procedure scott.p, then CURRENT_USER is jane and the procedure executes with Jane's privileges. See Also: - CREATE FUNCTION for more information on invoker-rights functions - "CURRENT_USER Example"
user IDENTIFIED BY password	Specify the username and password used to connect to the remote database (fixed user database link). If you omit this clause, the database link uses the username and password of each user who is connected to the database (connected user database link). See Also: The "Fixed User Example"

authenticated_clause

Specify the username and password on the target instance. This clause authenticates the user to the remote server and is required for security. The specified username and password must be a valid username and password on the remote instance. The username and password are used only for authentication. No other operations are performed on behalf of this user.

You must specify this clause when using the SHARED clause.

USING 'connect string'

Specify the service name of a remote database.

See Also: Net8 Administrator's Guide for information on specifying remote databases

Examples

CURRENT_USER Example

The following statement defines a current-user database link:

CREATE DATABASE LINK sales.hq.acme.com
   CONNECT TO CURRENT_USER
   USING 'sales';

Fixed User Example

The following statement defines a fixed-user database link named sales.hq.acme.com:

CREATE DATABASE LINK sales.hq.acme.com 
   CONNECT TO scott IDENTIFIED BY tiger 
   USING 'sales';

Once this database link is created, you can query tables in the schema scott on the remote database in this manner:

SELECT *
   FROM emp@sales.hq.acme.com;

You can also use DML statements to modify data on the remote database:

INSERT INTO accounts@sales.hq.acme.com(acc_no, acc_name, balance)
   VALUES (5001, 'BOWER', 2000);

UPDATE accounts@sales.hq.acme.com 
   SET balance = balance + 500;

DELETE FROM accounts@sales.hq.acme.com 
   WHERE acc_name = 'BOWER';

You can also access tables owned by other users on the same database. This statement assumes scott has access to Adam's dept table:

SELECT *
   FROM adams.dept@sales.hq.acme.com;

The previous statement connects to the user scott on the remote database and then queries Adam's dept table.

A synonym may be created to hide the fact that Scott's emp table is on a remote database. The following statement causes all future references to emp to access a remote emp table owned by scott:

CREATE SYNONYM emp 
   FOR scott.emp@sales.hq.acme.com;

PUBLIC Database Link Example

The following statement defines a shared public fixed user database link named sales.hq.acme.com that refers to user scott with password tiger on the database specified by the string service name 'sales':

CREATE SHARED PUBLIC DATABASE LINK sales.hq.acme.com 
   CONNECT TO scott IDENTIFIED BY tiger 
   AUTHENTICATED BY anupam IDENTIFIED BY bhide
   USING 'sales';