Oracle-DB LINK
CREATE DATABASE LINK
Purpose
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.
After you have created a database link, you can use it in SQL statements to refer to tables, views, and PL/SQL objects in the other database by appending @dblink to the table, view, or PL/SQL object name. You can query a table or view in the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.
使用该CREATE DATABASE LINK语句创建数据库链接。一个数据库链接是在一个数据库中，使您能够访问其它数据库对象的模式对象。另一个数据库不必是Oracle数据库系统。但是，要访问非Oracle系统，必须使用Oracle异构服务。
创建数据库链接后，可以在SQL语句中使用它来通过附加@dblink到表，视图或PL / SQL对象名称来引用其他数据库中的表，视图和PL / SQL对象。您可以使用该SELECT语句查询其他数据库中的表或视图。您也可以访问使用任何远程表和视图INSERT，UPDATE，DELETE，或LOCK TABLE语句。
Prerequisites
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 LINKsystem privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle Database.
要创建专用数据库链接，您必须具有CREATE DATABASE LINK系统特权。要创建公共数据库链接，您必须具有CREATE PUBLIC DATABASE LINK系统特权。此外，您必须具有CREATESESSION远程Oracle数据库的系统特权。必须在本地和远程Oracle数据库上安装Oracle Net。
Oracle Net must be installed on both the local and remote Oracle Databases.
Syntax
create_database_link::=
Description of the illustration ''dblink_authentication.gif''
Semantics
PUBLIC
Specify PUBLIC to create a public database link visible to all users. If you omit this clause, then the database link is private and is available only to you.
指定PUBLIC创建对所有用户可见的公共数据库链接。如果省略此子句，则数据库链接是私有的，仅对您可用。
远程数据库上可访问的数据取决于数据库链接在连接到远程数据库时使用的标识：
The data accessible on the remote database depends on the identity the database link uses when connecting to the remote database:
If you specify CONNECT TO user IDENTIFIED BY password, then the database link connects with the specified user and password.
如果指定CONNECT TO user IDENTIFIED BY password，则数据库链接将与指定的用户和密码连接。
If you specify CONNECT TO CURRENT_USER, then the database link connects with the user in effect based on the scope in which the link is used.
如果指定CONNECT TO CURRENT_USER，则数据库链接将根据使用链接的范围与有效用户连接。
If you omit both of those clauses, then the database link connects to the remote database as the locally connected user.
如果省略这两个子句，则数据库链接将作为本地连接的用户连接到远程数据库。
SHARED
Specify SHARED to create a database link that can be shared by multiple sessions using a single network connection from the source database to the target database. In a shared server configuration, shared database links can keep the number of connections into the remote database from becoming too large. Shared links are typically also public database links. However, a shared private database link can be useful when many clients access the same local schema, and therefore use the same private database link.
指定SHARED使用从源数据库到目标数据库的单个网络连接创建可由多个会话共享的数据库链接。在共享服务器配置中，共享数据库链接可以使进入远程数据库的连接数变得太大。共享链接通常也是公共数据库链接。但是，当许多客户端访问同一本地模式时，共享私有数据库链接可能很有用，因此使用相同的私有数据库链接。
In a shared database link, multiple sessions in the source database share the same connection to the target database. Once a session is established on the target database, that session is disassociated from the connection, to make the connection available to another session on the source database. To prevent an unauthorized session from attempting to connect through the database link, when you specify SHARED you must also specify the dblink_authentication clause for the users authorized to use the database link.
在共享数据库链接中，源数据库中的多个会话共享与目标数据库相同的连接。在目标数据库上建立会话后，该会话将与该连接取消关联，以使该连接可用于源数据库上的另一个会话。为防止未经授权的会话尝试通过数据库链接进行连接，在指定SHARED时还必须dblink_authentication为有权使用数据库链接的用户指定子句。
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 is TRUE, then the database link must have the same name as the database to which it connects. If the value of GLOBAL_NAMES is FALSE, 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 an Oracle RAC configuration depends on the value of the OPEN_LINKS and OPEN_LINKS_PER_INSTANCE initialization parameters.
Restriction on Creating Database Links 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 Database 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.)
指定数据库链接的完整或部分名称。如果仅指定数据库名称，则Oracle Database会隐式附加本地数据库的数据库域。
仅使用ASCII字符dblink。不支持多字节字符。数据库链接名称不区分大小写，并以大写ASCII字符存储。如果将数据库名称指定为带引号的标识符，则会以静默方式忽略引号。
如果GLOBAL_NAMES初始化参数的值为TRUE，则数据库链接必须与其连接的数据库具有相同的名称。如果值GLOBAL_NAMES是FALSE，如果你已经改变了数据库的全局名称，那么您可以指定全局名称。
可以在一个会话或一个Oracle RAC配置实例中打开的最大数据库链接数取决于OPEN_LINKS和OPEN_LINKS_PER_INSTANCE初始化参数的值。
CONNECT TO Clause
The CONNECT TO clause lets you specify the user and credentials, if any, to be used to connect to the remote database.
该CONNECT TO子句允许您指定用于连接到远程数据库的用户和凭据（如果有）。
CURRENT_USER Clause
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.
If the database link is used directly rather than 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 name of the user that owns the stored object, and not the name of the user 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, then the current user is scott.
However, if the stored object is an invoker-rights function, procedure, or package, then 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 janeand the procedure executes with Jane's privileges.
指定CURRENT_USER以创建当前用户数据库链接。当前用户必须是远程数据库上具有有效帐户的全局用户。
如果直接使用数据库链接而不是存储对象中的数据库链接，则当前用户与连接的用户相同。
执行启动数据库链接的存储对象（例如过程，视图或触发器）时，CURRENT_USER是拥有存储对象的用户的名称，而不是调用该对象的用户的名称。例如，如果数据库链接出现在过程scott.p（创建者scott）和用户jane调用过程中scott.p，则当前用户是scott。
但是，如果存储的对象是调用者权限函数，过程或包，则调用者的授权ID用于作为远程用户进行连接。例如，如果权限的数据库链接出现内部程序scott.p（通过创建一个调用权限程序scott），并且用户Jane调用过程scott.p，然后CURRENT_USER是jane和程序简的权限执行。
user IDENTIFIED BY password
Specify the user name and password used to connect to the remote database using a fixed user database link. If you omit this clause, then the database link uses the user name and password of each user who is connected to the database. This is called a connected user database link.
dblink_authentication
You can specify this clause only if you are creating a shared database link—that is, you have specified the SHARED clause. 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.
只有在创建共享数据库链接时才能指定此子句 - 也就是说，您已指定了该SHARED子句。在目标实例上指定用户名和密码。此子句向远程服务器验证用户身份，并且是安全性所必需的。指定的用户名和密码必须是远程实例上的有效用户名和密码。用户名和密码仅用于身份验证。不代表此用户执行任何其他操作。
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.
指定远程数据库的服务名称。如果仅指定数据库名称，则Oracle数据库会隐式将数据库域附加到连接字符串以创建完整的服务名称。因此，如果远程数据库的数据库域与当前数据库的数据库域不同，则必须指定完整的服务名称。
Examples
The examples that follow assume two databases, one with the database name local and the other with the database name remote. The examples use the Oracle Database domain. Your database domain will be different.
下面的示例假设有两个数据库，一个具有数据库名称local，另一个具有数据库名称remote。这些示例使用Oracle数据库域。您的数据库域将不同。
Defining a Public Database Link: Example The following statement defines a shared public database link named remote that refers to the database specified by the service name remote:
CREATE PUBLIC DATABASE LINK remote
USING 'remote';
This database link allows user hr on the local database to update a table on the remote database (assuming hr has appropriate privileges):
UPDATE [email protected]
SET salary=salary*1.1
WHERE last_name = 'Baer';
Defining a Fixed-User Database Link: Example In the following statement, user hr on the remote database defines a fixed-user database link named local to the hr schema on the local database:
CREATE DATABASE LINK local
CONNECT TO hr IDENTIFIED BY password
USING 'local';
After this database link is created, hr can query tables in the schema hr on the local database in this manner:
SELECT * FROM [email protected];
User hr can also use DML statements to modify data on the local database:
INSERT INTO [email protected]
(employee_id, last_name, email, hire_date, job_id)
VALUES (999, 'Claus', '[email protected]', SYSDATE, 'SH_CLERK');
UPDATE [email protected] SET min_salary = 3000
WHERE job_id = 'SH_CLERK';
DELETE FROM [email protected]
WHERE employee_id = 999;
Using this fixed database link, user hr on the remote database can also access tables owned by other users on the same database. This statement assumes that user hr has SELECT privileges on the oe.customers table. The statement connects to the user hr on the local database and then queries the oe.customers table:
SELECT * FROM [email protected];
使用此固定数据库链接，数据库上的用户hr remote还可以访问同一数据库上其他用户拥有的表。此语句假定用户hr具有SELECT该oe.customers表的权限。该语句连接到数据库hr上的用户local，然后查询oe。customers表：
Defining a CURRENT_USER Database Link: Example The following statement defines a current-user database link to the remote database, using the entire service name as the link name:
CREATE DATABASE LINK remote.us.example.com
CONNECT TO CURRENT_USER
USING 'remote';
The user who issues this statement must be a global user registered with the LDAP directory service.
You can create a synonym to hide the fact that a particular table is on the remote database. The following statement causes all future references to emp_table to access the employees table owned by hr on the remote database:
发出此语句的用户必须是使用LDAP目录服务注册的全局用户。
您可以创建同义词以隐藏特定表在remote数据库上的事实。下面的语句会导致未来的所有引用emp_table来访问employees所拥有的表hr上的remote数据库：
CREATE SYNONYM emp_table
FOR [email protected];