Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
SQL Statements:
CREATE CLUSTER to CREATE JAVA, 6 of 12
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:
|
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.
Oracle Net must be installed on both the local and remote Oracle databases.
To access non-Oracle systems you must use Oracle Heterogeneous Services.
create_database_link::=
create_database_link
authenticated_clause
Specify SHARED
to use a single network connection to create a public database link that can be shared between multiple users.
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.
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 a Real Application Clusters 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:
|
The CONNECT
TO
clause lets you enable a connection to the remote database.
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.
Specify the username and password used to connect to the remote database using a 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. This is called a connected user database link.
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.
Specify the service name of a remote database.
See Also:
Oracle9i Net Services Administrator's Guide for information on specifying remote databases |
The following statement defines a current-user database link using the demo database:
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 hr IDENTIFIED BY hr 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 employees@sales.hq.acme.com;
You can also use DML statements to modify data on the remote database:
INSERT INTO orders@sales.hq.acme.com (customer_id, order_id, order_total) VALUES (5001, 1235, 2000); UPDATE orders@sales.hq.acme.com SET order_total = order_total + 500; DELETE FROM order_id@sales.hq.acme.com WHERE order_id = 2443;
You can also access tables owned by other users on the same database. This statement assumes that the current user has SELECT
privileges on the hr.departments
table:
SELECT * FROM hr.departments@sales.hq.acme.com;
The previous statement connects to the user scott
on the remote database and then queries Adam's dept
table.
You can create a synonym to hide the fact that the departments
table is on a remote database. The following statement causes all future references to dept
to access a remote departments
table owned by hr
:
CREATE SYNONYM dept FOR hr.departments@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 hr IDENTIFIED BY hr AUTHENTICATED BY anupam IDENTIFIED BY bhide USING 'sales';
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|