Oracle DB Links学习与测试

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/