ITPub博客

首页 > 数据库 > Oracle > oracle dblink 学习

oracle dblink 学习

原创 Oracle 作者:LeiDong12 时间:2011-11-03 00:33:38 0 删除 编辑


CREATE DATABASE LINK

下面对CREATE DATABASE LINK的(Purpose,Prerequisites,syntax,keyword and parameters)进行了描述

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.

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 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 [ SHARED ] [ PUBLIC ] DATABASE LINK dblink
[ CONNECT TO
{ CURRENT_USER
| user IDENTIFIED BY password [ dblink_authentication ]
}
| dblink_authentication
]...
[ USING connect_string ] ;
AUTHENTICATED BY user IDENTIFIED BY password

Keyword and Parameters

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.
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.
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.
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.

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.

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.)

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.

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 jane and the procedure executes with Jane's privileges.

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.

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.

自己动手练习

练习环境:
cource database(windows):
db_name=donglei
ORACLE_SID=donglei
server_name=donglei
version:
C:Userscpc-1>sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on 星期三 11月 2 23:37:28 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

target database(vm):
db_name=ora144
ORACLE_SID=ora144
server_name=ora144
version:
SQL*Plus: Release 10.2.0.2.0 - Production on 星期三 11月 2 23:41:51 2011

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

1)使用CONNECT TO Clause建立一个PUBLIC database linke

SQL> create public database link ora144.scott connect to scott identified by tiger using 'ora144';——这个link只能访问scott用户下的对象。

Database link created
SQL> select * from dba_db_links;

OWNER DB_LINK USERNAME HOST CREATED
-------------------- -------------------- ------------------------------ -------------------- -----------
PUBLIC ORA144.SCOTT SCOTT 2011/11/2 2

SQL> select count(*) from emp@ora144.scott;

COUNT(*)
----------
14

2)使用USING 'connect string'建立一个database link

SQL> create public database link ora144 using 'ora144';

Database link created

SQL> select * from dba_db_links where db_link='ORA144.SCOTT';

OWNER DB_LINK USERNAME HOST CREATED
-------------------- -------------------- ------------------------------ -------------------- -----------
PUBLIC ORA144.SCOTT SCOTT ora144 2011/11/3 0

SQL> commit;

SQL> select count(*) from scott.emp@ora144;——可以访问target数据库的所有对象。

COUNT(*)
----------
14

3)使用CURRENT_USER Clause建立一个database link

SQL> conn scott/tiger@donglei
已连接。
SQL> CREATE DATABASE LINK my.dblink.com CONNECT TO CURRENT_USER USING 'ora144';

数据库链接已创建。

SQL> select count(*) from emp@my.dblink.com;
select count(*) from
emp@my.dblink.com
*
第 1 行出现错误:
ORA-01017: 用户名/口令无效; 登录被拒绝


SQL> select count(*) from scott.emp@my.dblink.com;
select count(*) from
scott.emp@my.dblink.com
*
第 1 行出现错误:
ORA-01017: 用户名/口令无效; 登录被拒绝

下面是oracle 11.2g官方文档的建立方法

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:

CREATE SYNONYM emp_table
FOR
oe.employees@remote.us.example.com;

[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26179867/viewspace-1056112/,如需转载,请注明出处,否则将追究法律责任。

上一篇: dbms_logmnr.的使用
请登录后发表评论 登录
全部评论

注册时间:2011-08-17

  • 博文量
    28
  • 访问量
    262957