Oracle7 Server SQL Reference

CREATE DATABASE LINK

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 Oracle7 or a non-Oracle7 database.

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.

Syntax

Keyword and Parameters

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 "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 Oracle7 and can only be specified for public database links. For more information on specifying read-only mounts, see Trusted Oracle7 Server Administrator's Guide.

Usage Notes

You cannot create a database link in another user's schema and you cannot qualify dblink with the name of a schema. Since periods are permitted in names of database links, Oracle7 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.

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 command. If you are using Oracle7 with the distributed option, you can also access remote tables and views in any of the following commands:

DELETE command

INSERT command

LOCK TABLE command

UPDATE command

The number of different database links that can appear in a single statement is limited to the value of the initialization parameter OPEN_LINKS .

When you create a database link in Trusted Oracle7, it is labeled with your DBMS label.

Example

The following statement defines a database link named SALES.HQ.ACME.COM that refers to user SCOTT with password TIGER on the database specified by the string D:BOSTON-MFG:

CREATE DATABASE LINK sales.hq.acme.com 
	CONNECT TO scott IDENTIFIED BY tiger 
	USING 'D:BOSTON-MFG'

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 Data Manipulation Language commands 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 example 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.