oracle database link的管理和使用注意事项

1、创建database link

Use the CREATE DATABASE LINK statement to create a database link. Adatabase link is a schema object in one database that enables you to access objects on another database.

After 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 theSELECT statement. You can also access remote tables and views using anyINSERT,UPDATE, DELETE, or LOCKTABLE 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 theCREATEPUBLIC DATABASE LINK system privilege.

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

Syntax :

下面是一些关键字的说明。

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.

dblink

Specify the complete or partial name of the database link. If you specify only the database name, then Oracle Database implicitly appends the database domain of the local database.

Use only ASCII characters for dblink. Multibyte characters are not supported. The database link name is case insensitive and is stored in uppercase ASCII characters. If you specify the database name as a quoted identifier, then the quotation marks are silently ignored.

If the value of the GLOBAL_NAMES initialization parameter isTRUE, then the database link must have the same name as the database to which it connects. If the value ofGLOBAL_NAMES isFALSE, and if you have changed the global name of the database, then you can specify the global name.

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 theOPEN_LINKS and OPEN_LINKS_PER_INSTANCE initialization parameters.

NOTE:

You cannot create a database link in another user's schema, and you cannot qualifydblink with the name of a schema. Periods are permitted in names of database links, so Oracle Database interprets the entire name, such asralph.linktosales, as the name of a database link in your schema rather than as a database link namedlinktosales in the schemaralph.)

CONNECT TO

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

user IDENTIFIED BY password

Specify the user name and password used to connect to the remote database using afixed user database link.

USING 'connect string'

Specify the service name of a remote database. If you specify only the database name, then Oracle Database implicitly appends the database domain to the connect string to create a complete service name. Therefore, if the database domain of the remote database is different from that of the current database, then you must specify the complete service name.

2、删除database link

 Use the DROPDATABASE LINK statement to remove a database link from the database.

To drop a PUBLIC database link, you must have the DROPPUBLIC DATABASE LINK system privilege.

Syntax :

下面是一些关键字的说明。

PUBLIC

You must specify PUBLIC to drop a PUBLIC database link.

dblink

Specify the name of the database link to be dropped.

NOTE:

You cannot drop a database link in another user's schema, and you cannot qualifydblink with the name of a schema, because periods are permitted in names of database links. Therefore, Oracle Database interprets the entire name, such asralph.linktosales, as the name of a database link in your schema rather than as a database link namedlinktosales in the schema ralph.

3、与database link相关的视图

USER_DB_LINKS describes the database links owned by the current user.

ALL_DB_LINKS describes the database links accessible to the current user.

DBA_DB_LINKS describes all database links in the database.

V$DBLINK describes all database links opened by the session.The database links with IN_TRANSACTION=YES must be committed or rolled back before being closed.

4、database link用完之后，要记得及时关闭

When you issue a statement that uses a database link, Oracle Database creates a session for you on the remote database using that link.

The connection remains open until you end your local session or until the number of opened database links for your session exceeds the value of the initialization parameterOPEN_LINKS.

you can use the following statement to close the link explicitly if you do not plan to use it again in your session.

sql> ALTER SESSION CLOSE DATABASE LINK db_link;