题记:之前在写一个统计性质的存储过程,运行时报了ORA-02020。研究下~ |
$ oerr ora 2020
02020, 00000, "too many database links in use" // *Cause: The current session has exceeded the INIT.ORA open_links maximum. // *Action: Increase the open_links limit, or free up some open links by // committing or rolling back the transaction and canceling open // cursors that reference remote databases. |
确实用了好几个database links 。
下面建立实验来验证 committing or rolling back the transaction 是ok的。
1、 查看dblink的参数。创建多个dblink,这里我建立了6个:
从DBLINK_TEST1至DBLINK_TEST6
z@test10g> show parameter open_links
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ open_links integer 4 open_links_per_instance integer 4 |
2、连接6个dblink,每次连接后提交。则可以成功。
z@test10g> show parameter open_links
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_links integer 4
open_links_per_instance integer 4
z@test10g> col DB_LINK for a20
z@test10g> select * from v$dblink;no rows selected
z@test10g> declare
2 v_i number;
3 v_sql varchar(500);
4 begin
5 for i in 1..6
6 loop
7 v_sql:='select count(*) from dual@DBLINK_TEST'||to_char(i);
8 execute immediate v_sql into v_i;
9 commit;
10 dbms_output.put_line(i);
11 end loop;
12 end;
13 /
1
2
3
4
5
6PL/SQL procedure successfully completed.
z@test10g> select * from v$dblink;
DB_LINK OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD COMMIT_POINT_STRENGTH
-------------------- ---------- --- --- ------ ------------ --- --- ---------------------
DBLINK_TEST1 58 YES YES UNKN 0 NO NO 1
DBLINK_TEST2 58 YES YES UNKN 0 NO NO 1
DBLINK_TEST3 58 YES YES UNKN 0 NO NO 1
DBLINK_TEST6 58 YES YES UNKN 0 NO NO 1
3、连接6个dblink,每次连接后不提交。则提示失败。
z@test10g> declare
2 v_i number;
3 v_sql varchar(500);
4 begin
5 for i in 1..6
6 loop
7 v_sql:='select count(*) from dual@DBLINK_TEST'||to_char(i);
8 execute immediate v_sql into v_i;
9 --commit;
10 dbms_output.put_line(i);
11 end loop;
12 end;
13 /
1
2
3
4
declare
*
ERROR at line 1:
ORA-02020: too many database links in use
ORA-06512: at line 8
z@test10g> select * from v$dblink;DB_LINK OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD COMMIT_POINT_STRENGTH
-------------------- ---------- --- --- ------ ------------ --- --- ---------------------
DBLINK_TEST1 58 YES YES UNKN 0 YES NO 1
DBLINK_TEST2 58 YES YES UNKN 0 YES NO 1
DBLINK_TEST3 58 YES YES UNKN 0 YES NO 1
DBLINK_TEST4 58 YES YES UNKN 0 YES NO 1
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23650854/viewspace-687904/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23650854/viewspace-687904/