Detailed explanation of ora-02020 Fault Diagnosis

ORA-2020 errors occur when the number of dblinks used by a distributed transaction exceeds the threshold defined by the open_links parameter: oracle @ ibmvs_a @ oracle $ oerrora202002020,

ORA-2020 error occurs when the number of dblinks used by a distributed transaction exceeds the threshold defined by the open_links parameter: oracle @ ibmvs_a @/oracle $ oerr ora 202002020,

ORA-2020 errors occur when the number of dblinks used by a distributed transaction exceeds the threshold defined by the open_links parameter:
Oracle @ ibmvs_a @/oracle $ oerr ora 2020.
02020,000 00, "too connected 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
// Committing or rolling back the transaction and canceling open
// Cursors that reference remote databases.
The error is mainly related to the open_links setting. If you set it to 0, distributed transactions are disabled. Some official instructions are as follows:
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 shoshould 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 your 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.

In the case of dblink, queries and DML operations will cause distributed transactions, long queries and not timely commit or rollback transactions are easy to cause ORA-02020 errors. Therefore, pay special attention to the need to promptly commit or roll back the transactions using dblink to avoid long queries. When setting open_links, you should understand the execution time and query density of the query statement of dblink for the application developer to make the most effective adjustment.

1. View dblink parameters. Create multiple dblinks. Here I have created six:
From DBLINK_TEST1 to DBLINK_TEST6
Z @ test10g> show parameter open_links
NAME TYPE VALUE
-----------------------------------------------------------------------------
Open_links integer 4
Open_links_per_instance integer 4
2. Connect six dblinks and submit them after each connection. Then, the operation is successful.
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 unkn 0 NO 1
DBLINK_TEST2 58 yes unkn 0 NO 1
DBLINK_TEST3 58 yes unkn 0 NO 1
DBLINK_TEST6 58 yes unkn 0 NO 1
3. Connect six dblinks and do not submit them after each connection. An error is prompted.
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 relational 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 unkn 0 yes no 1
DBLINK_TEST2 58 yes unkn 0 yes no 1
DBLINK_TEST3 58 yes unkn 0 yes no 1
DBLINK_TEST4 58 yes unkn 0 yes no 1
You can modify the open_links parameters as follows:
SQL> alter system set open_links = 12 scope = spfile;

Restart Database