ora-02020故障診斷詳解(原創)

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;重新開機資料庫