ora-02020故障诊断详解

ORA-2020错发生在一个分布式事务使用的dblink数超过参数open_links定义的阀值时：oracle@ibmvs_a@/oracle $ oerr ora 202002020,

ORA-2020错发生在一个分布式事务使用的dblink数超过参数open_links定义的阀值时：
Oracle@ibmvs_a@/oracle $ 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.
改报错主要是和open_links的设置有关为，将其设0时将禁用分布式事务。以下是部分官方说明：
OPEN_LINKS specifies the maximum number of concurrent open connections to remote databases in one session. These connections include database links, as well as external procedures and cartridges, each of which uses a separate process.
Oracle counts one open link for the following:
For each user that references a public or private database link
For each external procedure or cartridge connection when it is executed for the first time
Both types of connections close when the session ends. You can also close a database link connection explicitly by issuing an ALTER SESSION CLOSE DATABASE LINK statement.
You should set this parameter to allow for the external procedure and cartridge connections expected during the session plus the number of databases referred to in typical distributed transactions (that is, a single SQL statement that references multiple databases), so that all the databases can be open to execute the statement. For example, if queries alternately access databases A, B, and C, and OPEN_LINKS is set to 2, time will be lost waiting while one connection is broken and another made. Increase the value if many different databases are accessed over time.
This parameter refers only to connections used for distributed transactions. Direct connections to a remote database specified as an application connects are not counted.
If you set OPEN_LINKS to 0, then no distributed transactions are allowed.

 

在使用dblink的情况下，查询和DML操作都会造成分布式事务，长查询和没有及时提交或回滚事务都很容易造成ORA-02020报错。因此应特别注意使用dblink的事务需及时提交或回滚事务，避免长查询。在设置open_links时应了解应用开发商方面使用dblink的查询语句执行时间和查询密度，才能做出最有效的调整。

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> 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(*) fromdual@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  /
PL/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(*) fromdual@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
可通过如下方法修改open_links参数
SQL> alter system set open_links=12 scope=spfile;

重启数据库