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后，什么时侯关闭?
测试发现应用session 关闭时，同时关闭session open的dblink
或者可以使用以下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
