How To Close A Database Link

Today someone ask me about how to close a dabase link in Oracle, I post a very simple example to close open cursors;

 

 
DB12*ANTO> select version from V$instance;

VERSION
_________________
12.1.0.2.0

DB12*ANTO> REMARK
DB12*ANTO> REMARK TESTCASE I (Using alter session)
DB12*ANTO> REMARK
DB12*ANTO> r
  1* select * from v$dblink

ninguna fila seleccionada

DB12*ANTO> select count (*) from dba_objects@transfer_link;

  COUNT(*)
__________
     20017

DB12*ANTO> select * from v$dblink;

DB_LINK                        OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD COMMIT_POINT_STRENGTH     CON_ID
____________________________ __________ ___ ___ ______ ____________ ___ ___ _____________________ __________
TRANSFER_LINK.XXXX.COM               62 YES YES UNKN              0 YES NO                      1          0

DB12*ANTO> 
DB12*ANTO> 
DB12*ANTO> ALTER SESSION CLOSE DATABASE LINK transfer_link;
ERROR:
ORA-02080: el enlace de base de datos está en uso

DB12*ANTO> REMARK
DB12*ANTO> REMARK Of course, the transaction is open you need to close (commit/rollback)
DB12*ANTO> REMARK
DB12*ANTO> ROLLBACK;

Rollback terminado.

DB12*ANTO> ALTER SESSION CLOSE DATABASE LINK transfer_link;

Sesión modificada.

DB12*ANTO> REMARK
DB12*ANTO> REMARK TESTCASE II (Using DBMS_SESSION PACKAGE)
DB12*ANTO> REMARK
DB12*ANTO> exec DBMS_SESSION.CLOSE_DATABASE_LINK ('transfer_link');
BEGIN DBMS_SESSION.CLOSE_DATABASE_LINK ('transfer_link'); END;

*
ERROR en línea 1:
ORA-02081: el enlace de base de datos no está abierto
ORA-06512: en "SYS.DBMS_SESSION", línea 191
ORA-06512: en línea 1

DB12*ANTO> REMARK The database link is close, you need to user the db link to open it
DB12*ANTO>  select count (*) from dba_objects@transfer_link;

  COUNT(*)
__________
     20017

DB12*ANTO> SELECT * FROM V$DBLINK;

DB_LINK                        OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD COMMIT_POINT_STRENGTH     CON_ID
____________________________ __________ ___ ___ ______ ____________ ___ ___ _____________________ __________
TRANSFER_LINK.XXXX.COM               62 YES YES UNKN              0 YES NO                      1          0

DB12*ANTO> COMMIT;

Confirmación terminada.

DB12*ANTO>  exec DBMS_SESSION.CLOSE_DATABASE_LINK ('transfer_link');

Procedimiento PL/SQL terminado correctamente.

DB12*ANTO> 

HTH – Antonio NAVARRO

 

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