ORACLE实例与DBLINK相关的参数

open_links --限制每个session可以打开的database link 数量
open_links_per_instance --指定每个数据库实例全局可迁移(可共享并缓存)的最大打开连接数，供XA transactions使用。

SQL> @p open_links

NAME                                     VALUE
---------------------------------------- ----------------------------------------
open_links                               4
open_links_per_instance                  4

SQL>

测试当session中使用dblink后，什么时侯关闭?

1.测试发现应用session 关闭时，同时会关闭session open的dblink。
2.或者可以使用以下sql主动关闭：alter session close database link TEST_LINK;

SQL> select sysdate  from dual@TEST_LINK;

SYSDATE
-----------------
20200506 09:25:35

---执行使用DBLINK的查询后，创建了一个DBLINK SESSION
SQL> select sid,module,username from v$session where module  like 'oracle%';

       SID MODULE                           USERNAME
---------- -------------------------------- --------------------
       263 oracle@perf-monitor (TNS V1-V3)  GM

---退出重新登录

SQL> select sid,module,username from v$session where module  like 'oracle%';

no rows selected

SQL>

public daabase link 重用是否会重用？减少DBLINK连接数占用

当多个session使用相关的dblink时，不会共用，每个session只能使用自己的打开DBLINK.
并且下面5个session打开了5个dblink，也可以验证open_links（=4）限制的不是实例级的。

---session 1

alter session set container=perfdb;

SQL> create public database link pub_test_link connect to gm identified by gm using '//192.168.56.1:1521/perfdb';

Database link created.

SQL> select sid,module,username from v$session where module  like 'oracle%';

no rows selected


SQL> select sysdate  from dual@TEST_LINK;

SYSDATE
-----------------
20200506 09:56:31

SQL> select sid,module,username from v$session where module  like 'oracle%';

       SID MODULE                                                           USERNAME
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
       795 oracle@perf-monitor (TNS V1-V3)                                  GM

SQL>

--- session 2

alter session set container=perfdb;

SQL> select sid,module,username from v$session where module  like 'oracle%';

       SID MODULE                                                           USERNAME
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
       795 oracle@perf-monitor (TNS V1-V3)                                  GM

SQL> 

SQL> select sysdate  from dual@TEST_LINK;

SYSDATE
-----------------
20200506 09:57:29

SQL> select sid,module,username from v$session where module  like 'oracle%';

       SID MODULE                                                           USERNAME
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
       150 oracle@perf-monitor (TNS V1-V3)                                  GM
       795 oracle@perf-monitor (TNS V1-V3)                                  GM

SQL> 

SQL> commit;

Commit complete.

---session 3

SQL> alter session set container=perfdb;

Session altered.

SQL> 
SQL> select sid,module,username from v$session where module  like 'oracle%';

       SID MODULE                                                           USERNAME
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
       150 oracle@perf-monitor (TNS V1-V3)                                  GM
       795 oracle@perf-monitor (TNS V1-V3)                                  GM

SQL> select sysdate  from dual@TEST_LINK;

SYSDATE
-----------------
20200506 09:59:10

SQL> select sid,module,username from v$session where module  like 'oracle%';

       SID MODULE                                                           USERNAME
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
       150 oracle@perf-monitor (TNS V1-V3)                                  GM
       519 oracle@perf-monitor (TNS V1-V3)                                  GM
       795 oracle@perf-monitor (TNS V1-V3)                                  GM

SQL>

--- session 5

SQL> alter session set container=perfdb;

Session altered.

SQL> select sysdate  from dual@TEST_LINK;

SYSDATE
-----------------
20200506 10:09:43

SQL> select sid,module,username from v$session where module  like 'oracle%';

       SID MODULE                                                           USERNAME
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
       150 oracle@perf-monitor (TNS V1-V3)                                  GM
       263 oracle@perf-monitor (TNS V1-V3)                                  GM
       519 oracle@perf-monitor (TNS V1-V3)                                  GM
       681 oracle@perf-monitor (TNS V1-V3)                                  GM
       795 oracle@perf-monitor (TNS V1-V3)                                  GM

SQL>

测试验证open_links限制的是每个session打开的dblink的数量

当一个session打开>open_links个dblink时，报错ORA-02020: too many database links in use

SQL> create public database link pub_test_link2 connect to gm identified by gm using ‘//192.168.56.1:1521/perfdb’;

Database link created.

SQL> create public database link pub_test_link3 connect to gm identified by gm using ‘//192.168.56.1:1521/perfdb’;

Database link created.

SQL> create public database link pub_test_link4 connect to gm identified by gm using ‘//192.168.56.1:1521/perfdb’;

Database link created.

SQL> create public database link pub_test_link5 connect to gm identified by gm using ‘//192.168.56.1:1521/perfdb’;

Database link created.

SQL> @dblinks

OWNER DB_LINK USERNAME HOST CREATED

SYS SYS_HUB SEEDDATA 20180207 20:11:23
PUBLIC PUB_TEST_LINK2 GM //192.168.56.1:1521/perfdb 20200506 10:15:31
PUBLIC PUB_TEST_LINK3 GM //192.168.56.1:1521/perfdb 20200506 10:15:34
PUBLIC PUB_TEST_LINK4 GM //192.168.56.1:1521/perfdb 20200506 10:15:39
PUBLIC PUB_TEST_LINK5 GM //192.168.56.1:1521/perfdb 20200506 10:15:44

8 rows selected.

SQL> select sysdate from dual@TEST_LINK;

SYSDATE
20200506 10:17:14

SQL> select sysdate from dual@PUB_TEST_LINK2;

SYSDATE
20200506 10:17:27

SQL> select sysdate from dual@PUB_TEST_LINK3;

SYSDATE
20200506 10:17:32

SQL> select sysdate from dual@PUB_TEST_LINK4;

SYSDATE
20200506 10:17:34

---当前SESSION打开第5个DBLINK时报错

SQL> select sysdate from dual@PUB_TEST_LINK5;
select sysdate from dual@PUB_TEST_LINK5
*
ERROR at line 1:
ORA-02020: too many database links in use

---测试如果dblink session被kill掉，再次使用时会报错，不是自动重连。

SQL> alter session set container=perfdb;

Session altered.

SQL> select sid,module,username from v$session where module  like 'oracle%';

no rows selected

SQL> select sysdate  from dual@TEST_LINK;

SYSDATE
-----------------
20200506 10:29:54

SQL> select sid,module,username from v$session where module  like 'oracle%';

       SID MODULE                                                           USERNAME
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
       551 oracle@perf-monitor (TNS V1-V3)                                  GM

SQL> @kill sid=551

COMMANDS_TO_VERIFY_AND_RUN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter system kill session '551,14996'immediate -- GM@perf-monitor (oracle@perf-monitor (TNS V1-V3));

SQL> 
SQL> alter system kill session '551,14996'immediate -- GM@perf-monitor (oracle@perf-monitor (TNS V1-V3));

System altered.

SQL> select sid,module,username from v$session where module  like 'oracle%';

no rows selected

SQL> select sysdate  from dual@TEST_LINK;
select sysdate  from dual@TEST_LINK
                     *
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.LOGON_DENIED_TO_ALERT'
ORA-00604: error occurred at recursive SQL level 1
ORA-02051: another session or branch in same transaction failed or finalized
ORA-02051: another session or branch in same transaction failed or finalized
ORA-02063: preceding line from TEST_LINK

--- session commit（没有事务也需要commit）之后kill dblink session,再次使用时不会报错

SQL> select sysdate  from dual@TEST_LINK;

SYSDATE
-----------------
20200506 10:43:49

SQL> select sid,module,username from v$session where module  like 'oracle%';

       SID MODULE                                                           USERNAME
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
       645 oracle@perf-monitor (TNS V1-V3)                                  GM

SQL> @kill sid=645

COMMANDS_TO_VERIFY_AND_RUN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter system kill session '645,54133'immediate -- GM@perf-monitor (oracle@perf-monitor (TNS V1-V3));

SQL> commit;

Commit complete.

SQL> alter system kill session '645,54133'immediate -- GM@perf-monitor (oracle@perf-monitor (TNS V1-V3));

System altered.

SQL> select sid,module,username from v$session where module  like 'oracle%';

no rows selected

SQL> select sysdate  from dual@TEST_LINK;

SYSDATE
-----------------
20200506 10:44:23

https://www.modb.pro/db/24927

---查看打开dblink的session:

sqlplus /nolog

connect / as sysdba

select username, osuser, status, sid, serial#,sql_id, machine,
process, terminal, program from v$session
where saddr in (select k2gtdses from sys.x$k2gte );

select username,
       osuser,
       status,
       type,
       sid,
       serial#,
       machine,
       sql_id,
       process,
       terminal,
       program,
       'alter system kill session ''' || sid || ',' || serial# || ',@' ||
       inst_id || ''' immediate' kill_session_script
  from gv$session
 where saddr in (select k2gtdses from sys.x$k2gte)
   and status = 'ACTIVE'
   and type = 'USER';

How to view open dblink connections (Doc ID 387848.1)

https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=1bfcfe0k8r_52&id=387848.1

APPLIES TO:
Oracle Database - Enterprise Edition - Version 8.1.7.4 to 12.1.0.2 [Release 8.1.7 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
Information in this document applies to any platform.

GOAL
To find out the number of database links opened.

SOLUTION
OPEN_LINKS
Default: 4 Range: 0 to 255
Specifies the maximum number of concurrent open connections to remote databases in one session.
These connections include database links plus external procedures and cartridges each of which uses a separate process.

The following view shows the database link connections that are currently open in your current session:

V$DBLINK - Lists all open database links in your session, that is, all database links with the IN_TRANSACTION column set to YES.

NOTE: It is important to state that the section above "Lists all open database links in your session" is important, as this is only YOUR open dblinks that can be seen.

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
/

SQL> @dblink

DB_LINK OWNID LOGON HETER PROTOCOL OPN_CUR TXN UPDATE C_P_S
------------------------- ------ ----- ----- -------- ------- --- ------ ------
<dblink> 0 YES YES UNKN 0 YES YES 255

Note that above displays ONLY details about database links open in the session within which you are working.

If looking for details about database links open by different sessions, might use below:

sqlplus /nolog

connect / as sysdba

select username, osuser, status, sid, serial#,sql_id, machine,
process, terminal, program from v$session
where saddr in (select k2gtdses from sys.x$k2gte );

USERNAME OSUSER STATUS
------------------------------ ------------------------------ --------
SID SERIAL#
---------- ----------
MACHINE
----------------------------------------------------------------
PROCESS TERMINAL
------------------------ ------------------------------
PROGRAM
------------------------------------------------
SCOTT <user> INACTIVE
68 11
<hostname>

29318 pts/15
sqlplus<hostname> (TNS V1-V3)

 

REPADMIN <user> INACTIVE
232 5
<hostname>

28081 pts/14
sqlplus@<hostname> (TNS V1-V3)

SQL>

oracle-dblink-常用查询

ORACLE实例与DBLINK相关的参数

测试当session中使用dblink后，什么时侯关闭?

public daabase link 重用是否会重用？减少DBLINK连接数占用

测试验证open_links限制的是每个session打开的dblink的数量

How to view open dblink connections (Doc ID 387848.1)

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

oracle-dblink-常用查询

ORACLE实例与DBLINK相关的参数

测试当session中使用dblink后，什么时侯关闭?

public daabase link 重用是否会重用？减少DBLINK连接数占用

测试验证open_links限制的是每个session打开的dblink的数量

How to view open dblink connections (Doc ID 387848.1)

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像