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 其它細節問題查閱相關文件

Oracle DB Links學習與測試

相關文章