Tuesday, June 19, 2012

ORA-02020


Tested on Oracle 9. I reconfigured several database links then ran a test script to check them all:

SQL> conn andrew/reid
Connected.
SQL> select * from dual@link1
  2  /

D
-
X

SQL> select * from dual@link2
  2  /

D
-
X

SQL> select * from dual@link3
  2  /

D
-
X

SQL> select * from dual@link4
  2  /

D
-
X

SQL> select * from dual@link5
  2  /
select * from dual@link5
                   *
ERROR at line 1:
ORA-02020: too many database links in use

SQL>

The 5th query failed. This was because the value of the open_links parameter was too low:

SQL> conn / as sysdba
Connected.
SQL> select value from v$parameter
  2  where name = 'open_links'
  3  /

VALUE
----------
4

SQL>

Once you have run a query down a database link, it is retained until you do a commit or rollback. In the example below, a commit after the 1st query allows the 5th to work. Changing the value of the open_links parameter is also an option. I will try to look at this in a future post:

SQL> conn andrew/reid
Connected.
SQL> select * from dual@link1
  2  /

D
-
X

SQL> commit
  2  /

Commit complete.

SQL> select * from dual@link2
  2  /

D
-
X

SQL> select * from dual@link3
  2  /

D
-
X

SQL> select * from dual@link4
  2  /

D
-
X

SQL> select * from dual@link5
  2  /

D
-
X

SQL>

No comments: