Oracle DB Links学习与测试
P893<>
目录
1 concepts
1.1 Database links concept, advantages
1.2 Privileges necessary for creating Database links
1.3 Global_name enforce
2 Create database link
2.1 create private connected user
2.2 create private fixed user
2.3 create private current_user
2.4 create public connected user
2.5 shared database links
3 Manageing database link
3.1 close database link
3.2 drop database links
3.3 view db_link information
4 Notes
1 concepts
1.1 Database links concept, advantages
concept:
The database link is the central concept in distributed database syste. A database link is a connection between two physical database servers that allows a client to access them as one logical database.A database link is a pointer that defines a one-way communication path from an Oracle database server to another database server. The link pointer is actually defined as an entry in a data dictionary table.
(Database Link用于分布式数据库系统中,用来连接两个物理数据库服务器。Database link是单向连接)
advantages:
The great advantage of database links is that they allow users to access another user’s objects in a remote database so that they are bounded by the privilege set of the object’s owner. In other words, a local user can access a link to a remote database without having to be a user on the remote database.
1.2 Privileges necessary for creating Database links
Pirvilege database required for
Create Database Link Local Creation of a private database link
Create Public Database Link Local Creation of a public database link
Create Session Remote Creation of any type of database link.
1.3 Global_name enforce
refer to document<>
2 Create database link
make sure configure sales tnsname in tnsnames.ora firstly.
sales =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.245.13)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sale)
)
)
Database Link types: private,public,global.
link users: connected user, fixed user, current user
default is private connected user;
2.1 create private connected user
(只有创建者自己可以使用。且remote端必须有相应的用户)
conn / as sysdba
SQL> CREATE DATABASE LINK sale using 'sales';
Database link created.
SQL> select * from test@SALE.US.ORACLE.COM;
select * from test@SALE.US.ORACLE.COM
*
ERROR at line 1:
ORA-01005: null password given; logon denied
表明没有把用户密码传过给remote database.
conn mouse/mouse
SQL> CREATE DATABASE LINK sale using 'sales';
Database link created.
SQL> select count(*) from test@SALE.US.ORACLE.COM;
COUNT(*)
----------
28026
2.2 create private fixed user
(只有创建者自己可以使用, 使用固定的connect to test identified by test来连接Remote Database,在remote端以connect to userid 的固定身份出现)
SQL> conn / as sysdba
Connected.
SQL> CREATE DATABASE LINK sale connect to test identified by test using 'sales';
Database link created.
SQL> select count(*) from test@SALE.US.ORACLE.COM;
COUNT(*)
----------
28026
SQL> disc
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> conn mouse/mouse
Connected.
SQL> select count(*) from test@SALE.US.ORACLE.COM;
select count(*) from test@SALE.US.ORACLE.COM
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
2.3 create private current_user
( 同样只有创建者自己可使用, 使用当前创建者的userid/password 来连接Remote Database)
SQL> conn / as sysdba
Connected.
SQL> CREATE DATABASE LINK sale_current CONNECT TO CURRENT_USER USING 'sales';
Database link created.
SQL> select count(*) from test@sale_current;
select count(*) from test@sale_current
*
ERROR at line 1:
ORA-28030: Server encountered problems accessing LDAP directory service
(遇到LDAP Directory service 问题,不能访问,原因现在未知 待日后再check ???)
2.4 create public connected user
(所有用户可用,在local端,谁使用,对端就必须有相应的用户,对端也以该用户的身份出现)
SQL> conn / as sysdba
Connected.
SQL> create public database link sale_public USING 'sales';(sys 创建者)
SQL> select count(*) from test@hq_sales_tru;
select count(*) from test@hq_sales_tru
*
ERROR at line 1:
ORA-01005: null password given; logon denied
SQL> conn sys/sys as sysdba
Connected.
SQL> select count(*) from test@hq_sales_tru;
select count(*) from test@hq_sales_tru
*
ERROR at line 1:
ORA-01005: null password given; logon denied
SQL> conn mouse/mouse as sysdba
Connected.
SQL> select count(*) from test@hq_sales_tru;
select count(*) from test@hq_sales_tru
*
ERROR at line 1:
ORA-01005: null password given; logon denied
(表明,当link user type为 connected user时,必须提供用户密码,且不能以as sysdba 方式登录,如conn sys/sys as sysdba,conn mouse/mouse as sysdba ,当在本地以as sysdba登录时,在远端也尝试用as sysdba登录,当然是不允许的)
SQL> conn test/test
Connected.
SQL> select count(*) from test@sale_public; (test/test使用)
COUNT(*)
----------
28026
还有其它的各种情形,可作类似测试。
2.5 shared database links
Look carefully at your application and shared server configuration to determine whether to use shared links. A simple guideline is to use shared database links when the number of users accessing a database link is expected to be much larger than the number of server processes in the local database.
(主要用于,连接database link数量比较多的时候)
CREATE SHARED DATABASE LINK dblink_name
[CONNECT TO username IDENTIFIED BY password]|[CONNECT TO CURRENT_USER]
AUTHENTICATED BY schema_name IDENTIFIED BY password
[USING 'service_name'];
SQL> conn / as sysdba
Connected.
SQL> CREATE SHARED PUBLIC DATABASE LINK sales_public_shared AUTHENTICATED BY mouse IDENTIFIED BY mouse USING 'sales';
SQL> conn test/test
Connected.
SQL> select count(*) from test@sales_public_shared;
COUNT(*)
----------
28026
(两端都必须有test用户,且remote端必须有test 表,且remote端的mouse user 必须可以访问test.test表)
3 Manageing database link
closing, drop database, view,limiting the number of active connection.
3.1 close database link
ALTER SESSION CLOSE DATABASE LINK sales;
3.2 drop database links
删除的时候分private,和public.且不能删除别的用户的private db_link,即使是sys用户不行。
select 'drop public database link '|| db_link || ';' from dba_db_links;
3.3 view db_link information
#The following script. queries the DBA_DB_LINKS view to access link information:
COL OWNER FORMAT a10
COL USERNAME FORMAT A8 HEADING "USER"
COL DB_LINK FORMAT A30
COL HOST FORMAT A7 HEADING "SERVICE"
SELECT * FROM DBA_DB_LINKS
/
#For example, you can create and execute the script. below to determine which links are open (sample output included):
COL DB_LINK FORMAT A25
COL OWNER_ID FORMAT 99999 HEADING "OWNID"
COL LOGGED_ON FORMAT A5 HEADING "LOGON"
COL HETEROGENEOUS FORMAT A5 HEADING "HETER"
COL PROTOCOL FORMAT A8
COL OPEN_CURSORS FORMAT 999 HEADING "OPN_CUR"
COL IN_TRANSACTION FORMAT A3 HEADING "TXN"
COL UPDATE_SENT FORMAT A6 HEADING "UPDATE"
COL COMMIT_POINT_STRENGTH FORMAT 99999 HEADING "C_P_S"
SELECT * FROM V$DBLINK
/
这是连接级的查询,只有当前连接中存在db_link会话时,才有记录。
OPEN_LINKS:This parameter controls the number of remote connections that a single user session can use concurrently in distributed transactions.(控制连接到远程的用户并发数)
select * from user_db_links;
DB_LINK
--------------------------------------------------------------------------------
USERNAME PASSWORD
------------------------------ ------------------------------
HOST
--------------------------------------------------------------------------------
CREATED
---------
ANOTHER
LOOK SEE
another
28-OCT-09
4 Notes
4.1 Note that when you issue a SELECT statement across a database link, a transaction lock is placed on the rollback segments. To re-release the segment, you must issue a COMMIT or ROLLBACK statement.(Select statement also will lock the the rollback)
select count(*) from test@fixed_dblink;
ALTER SESSION CLOSE DATABASE LINK sales;
ERROR:
ORA-02080: database link is in us
commit;
ALTER SESSION CLOSE DATABASE LINK sales;
Session altered
4.2 The name that you give to a link on the local database depends on whether the remote database that you want to access enforces global naming.
(到底是远端的Global_names 必须为false,还是local端,或者两端都要呢。从文档的理解上看是远端的。但实际中,必须本地端,与user type 无关.)
4.3在建立Database link时并不检查名字的合法性。只有在使用时才检查。
select * from dual@xxx
ERROR at line 1:
ORA-02085: database link SALES connects to SALE.US.ORACLE.COM
4.4 conn / as sysdba
SQL> CREATE DATABASE LINK sale using 'sales';
Database link created.
SQL> select * from test@SALE.US.ORACLE.COM;
select * from test@SALE.US.ORACLE.COM
*
ERROR at line 1:
ORA-01005: null password given; logon denied
表明没有把用户密码传过给remote database.
conn mouse/mouse
SQL> CREATE DATABASE LINK sale using 'sales';
Database link created.
SQL> select count(*) from test@SALE.US.ORACLE.COM;
COUNT(*)
----------
28026
4.5 Database link name由 schema.db_link组成,不同的schema中可以有相同的db_link名。
4.6 在DBlink中,不能试图通过DBlink获取动态性能视图来得到理想的数据.
如:
SQL> select * from v$seesion@monitorlink;
select * from v$seesion@monitorlink
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from MONITORLINK
4.7 其它细节问题查阅相关文档
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10248702/viewspace-624033/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10248702/viewspace-624033/