Use the CREATE
DATABASE LINK statement to create a database link. A database link
is a schema object in one database that enables you to access objects on
another database. The other database need not be an Oracle Database system.
However, to access non-Oracle systems you must use Oracle Heterogeneous
Services.
Once you have created a database
link, you can use it to refer to tables and views on the other database. In SQL
statements, you can refer to a table or view on the other database by appending
@dblink to the table or view name. You can query a table or view on
the other database with the SELECT statement. You can also access remote tables and views
using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.
To create a private database link,
you must have the CREATE DATABASE LINK system privilege. To create a public database link, you
must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE
SESSION system privilege on the remote Oracle database.
Oracle Net must be installed on both
the local and remote Oracle databases.
Syntax:
CREATE DATABASE LINK
link_name
CONNECT TO user_id IDENTIFIED BY pass_word
USING 'query_string';
Types of Database links
Private database link
|
This link is more secure than a public or global link,
because only the owner of the private link, or subprograms within the same
schema, can use the link to access the remote database.
Syntax: sql> CREATE DATABASE LINK link_name CONNECT
TO user_id
IDENTIFIED BY pass_word USING 'query_string'; |
Public database link
|
When many users require an access path to a remote
Oracle Database, you can create a single public database link for all users
in a database.
Syntax: SQL>
CREATE PUBLIC DATABASE LINK publink
2 USING 'orcl1';
|
Global database link
|
When an Oracle network uses a directory server, an
administrator can conveniently manage global database links for all databases
in the system. Database link management is centralized and simple.
|
