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 1^st query allows the 5^th 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>