Explicitly Closing Oracle Database Link

From time to time (actually, once in a several years ;-)) I need to explicitly close opened database link, so here I’m putting some notes for myself on this topic.

Additionally to implicitly closing database link by closing you connection we have next methods for explicitly closing specific database link:

  1. ALTER SESSION CLOSE DATABASE LINK my_db_link;
  2. EXECUTE DBMS_SESSION.CLOSE_DATABASE_LINK(‘my_db_link‘);

To successfully close database link:

  • database link have to be opened first, or you will get ORA-02081: database link is not open
  • all distributed transactions originated from your session, that use this particular database link, must be COMMITed or ROLLed BACK, or you will get ORA-02080: database link is in use;
  • all cursors, that uses remote objects through this particular database link must be closed first, or you will get ORA-02080: database link is in use

To check if DB link is currently used(has open transactions and/or open cursors) next SQL statement may be used:

select db_link,logged_on,open_cursors,in_transaction from v$dblink where db_link like ‘my_db_link‘;

References:

1034343.6 How To Close Remote Connections
394730.1  ORA-2080 : PL/SQL Fails To Close DBLink After ROLLBACK / COMMIT

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s