You may face with that error while you are working with dblink. Sometimes, after a remote operation a user wants to close the connection to the remote site to conserve resources.

Formal details lets look at the oerr:

ORA-02080, 00000, "database link is in use"
Cause: a transaction is active or a cursor is open on the database link given in the alter session close database link  command.
Action: commit or rollback, and close all cursors








So how We can handle this error message? Here is the solution steps:

The three ways of closing the remote connections are:

  1. Exit out of the local connection. i.e logout of the local database. This will automatically disconnect the connection to the remote database for that session.
  2. Users with ALTER SESSION privilege can explicitly close the connection after the remote operation is complete, by issuing this command:
    SQL> show parameter SESSION_CACHED_CURSORS->In my case value is 50
    SQL> execute immediate ‘ALTER SESSION SET SESSION_CACHED_CURSORS = 0’;
    SQL> ALTER SESSION CLOSE DATABASE LINK ; If you receive an ORA-2080: database link is in use, issue a COMMIT; and then the alter session command again.
  3. Run below command: SQL> dbms_session.CLOSE_DATABASE_LINK(‘####’);
    SQL> execute immediate ‘ALTER SESSION SET SESSION_CACHED_CURSORS = 50’; This procedure basically does the ALTER SESSION CLOSE DATABASE LINK call as in Step 2 above.