Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 5 of 20
To create a database link. A database link is a schema object in the local database that allows 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 of the following statements:
For information about accessing remote tables or views with PL/SQL functions, procedures, packages, and datatypes, see Oracle8i Application Developer's Guide - Fundamentals. For information on distributed database systems, see Oracle8i Distributed Database Systems.
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.
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.
|
uses 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. |
|
|
creates a public database link available to all users. If you omit this clause, 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". |
|
|
Restrictions:
|
|
|
enables a connection to the remote database. |
|
|
creates 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, |
|
|
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 See Also: "CREATE FUNCTION" for more information on invoker-rights functions. |
|
user |
is 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). |
|
authenticated_clause |
specifies 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 |
|
|
specifies the service name of a remote database. For information on specifying remote databases, see Net8 Administrator's Guide. |
The following statement defines a current-user database link:
CREATE DATABASE LINK sales.hq.acme.com CONNECT TO CURRENT_USER USING 'sales';
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;
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';
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|