一、概述
最近碰到很多dblink的问题，网上关于这方面的介绍很少，索性自己研究下。本文将介绍open_links与open_links_per_instance这两个数据库参数。
open_links – specifies the maximum number of concurrent open connections to remote databases in one session. 在一个会话中，最大并发打开的dblink数量
open_links_per_instance – specifies the maximum number of migratable open connections globally for each database instance. XA transactions use migratable open connections so that the connections are cached after a transaction is committed. Another transaction can use the connection, provided the user who created the connection is the same as the user who owns the transaction.每个数据库实例中，最大打开dblink数量，这个应用在分布式事务中
还是不懂，没关系，看例子
二、open_links测试
这两个参数，数据库默认都是4
SQL> show parameter link NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ open_links integer 4 open_links_per_instance integer 4复制
为了验证这两个参数的差别，将其调整成不一样的值，注意这两个参数都是静态参数，修改需要重启实例
SQL> alter system set open_links = 2 scope=spfile; SQL> alter system set open_links_per_instance = 3 scope=spfile; SQL> startup force;复制
创建测试dblink
SQL> create public database link dl_1 connect to tom1 identified by tom1 using 'orcl19c'; SQL> create public database link dl_2 connect to tom2 identified by tom2 using 'orcl19c'; SQL> create public database link dl_3 connect to tom3 identified by tom3 using 'orcl19c'; SQL> create public database link dl_4 connect to tom4 identified by tom4 using 'orcl19c'; SQL> create public database link dl_5 connect to tom5 identified by tom5 using 'orcl19c';复制
执行plsql，里面有4个相同dblink
set serveroutput on declare v_no number; begin select 1 into v_no from dual@dl_1; dbms_output.put_line('1' || to_char(v_no)); select 2 into v_no from dual@dl_1; dbms_output.put_line('2' || to_char(v_no)); select 3 into v_no from dual@dl_1; dbms_output.put_line('3' || to_char(v_no)); select 4 into v_no from dual@dl_1; dbms_output.put_line('4' || to_char(v_no)); commit; end; /复制
程序正常执行
11 22 33 44 PL/SQL procedure successfully completed.复制
执行plsql，里面有4个不同的dblink
declare v_no number; begin select 1 into v_no from dual@dl_1; dbms_output.put_line('1' || to_char(v_no)); select 2 into v_no from dual@dl_2; dbms_output.put_line('2' || to_char(v_no)); select 3 into v_no from dual@dl_3; dbms_output.put_line('3' || to_char(v_no)); select 4 into v_no from dual@dl_4; dbms_output.put_line('4' || to_char(v_no)); commit; end; /复制
可以看到报错了，而且是在执行dual@dl_3的时候，我将plsql进行修改，逐渐减少dblink数量，直到只剩两个dblink的时候，程序才不报错，也就是说在一个会话中最多只能有两个不同的dblink。
begin * ERROR at line 3: ORA-04052: error occurred when looking up remote object TOM3.DUAL@DL_3 ORA-00604: error occurred at recursive SQL level 1 ORA-02020: too many database links in use复制
那表明是open_links参数限制了一个会话的最大dblink数量，是这样吗，我们将open_links设置成4，让它的值比open_links_per_instance大，再看看效果。
SQL> alter system set open_links = 4 scope=spfile; SQL> startup force;复制
执行plsql，里面有4个不同dblink
set serveroutput on declare v_no number; begin select 1 into v_no from dual@dl_1; dbms_output.put_line('1' || to_char(v_no)); select 2 into v_no from dual@dl_2; dbms_output.put_line('2' || to_char(v_no)); select 3 into v_no from dual@dl_3; dbms_output.put_line('3' || to_char(v_no)); select 4 into v_no from dual@dl_4; dbms_output.put_line('4' || to_char(v_no)); commit; end; /复制
程序正常执行，此时打开了4个不同的dblink，而参数open_links_per_instance为3，即open_links_per_instance并没有影响当前打开的dblink数量，那么open_links_per_instance是个啥呢？
11 22 33 44 PL/SQL procedure successfully completed.复制
三、open_links进一步测试
这里先提下，在oracle中普通的select并不会开启事务，但select语句中如有dblink，会自动开启一个事务。
避免之前的测试对结果产生干扰，关闭之前的窗口，新开一个窗口
SQL> select 1 from dual@dl_1; SQL> select * from v$dblink; // 查询当前会话打开的dblink数量，注意v$dblink需要在当前窗口执行才能看到结果 DB_LINK OWNER_ID LOGGED_ON HETEROGENEOUS PROTOCOL OPEN_CURSORS IN_TRANSACTION UPDATE_SENT COMMIT_POINT_STRENGTH ---------- --------- ---------- -------------------- ---------- ------------- --------------- --------------- --------------------- DL_1 0 YES YES UNKN 0 YES NO 1复制
依次执行以下sql，可以看到在执行select 1 from dual@dl_5报错
SQL> select 1 from dual@dl_2; SQL> select 1 from dual@dl_3; SQL> select 1 from dual@dl_4; SQL> select 1 from dual@dl_5; ERROR at line 1: ORA-02020: too many database links in use复制
查询当前会话打开的dblink数量
SQL> select * from v$dblink; DB_LINK OWNER_ID LOGGED_ON HETEROGENEOUS PROTOCOL OPEN_CURSORS IN_TRANSACTION UPDATE_SENT COMMIT_POINT_STRENGTH ---------- --------- ---------- -------------------- ---------- ------------- --------------- --------------- --------------------- DL_1 0 YES YES UNKN 0 YES NO 1 DL_2 0 YES YES UNKN 0 YES NO 1 DL_3 0 YES YES UNKN 0 YES NO 1 DL_4 0 YES YES UNKN 0 YES NO 1复制
此时可以看到当前会话中，一共有4个dblink，且IN_TRANSACTION为yes。那么我们执行下commit看看效果。
SQL> commit; SQL> select * from v$dblink; DB_LINK OWNER_ID LOGGED_ON HETEROGENEOUS PROTOCOL OPEN_CURSORS IN_TRANSACTION UPDATE_SENT COMMIT_POINT_STRENGTH ---------- --------- ---------- -------------------- ---------- ------------- --------------- --------------- --------------------- DL_1 0 YES YES UNKN 0 NO NO 1 DL_2 0 YES YES UNKN 0 NO NO 1 DL_3 0 YES YES UNKN 0 NO NO 1 DL_4 0 YES YES UNKN 0 NO NO 1复制
可以看到其它没啥变化，但是IN_TRANSACTION全部变为NO。这个时候我们再执行下select 1 from dual@dl_5试试
SQL> select 1 from dual@dl_5; SQL> select * from v$dblink; DB_LINK OWNER_ID LOGGED_ON HETEROGENEOUS PROTOCOL OPEN_CURSORS IN_TRANSACTION UPDATE_SENT COMMIT_POINT_STRENGTH ---------- --------- ---------- -------------------- ---------- ------------- --------------- --------------- --------------------- DL_1 0 YES YES UNKN 0 NO NO 1 DL_2 0 YES YES UNKN 0 NO NO 1 DL_3 0 YES YES UNKN 0 NO NO 1 DL_5 0 YES YES UNKN 0 YES NO 1复制
可以看到DL_4不见了，取而代之的是DL_5。
总结：open_links限制了当前会话中的最大处于事务中的dblink数量。
四、open_links_per_instance
待续。。。
评论
0
点赞