How to Close and Expire Database Link

How to Close and Expire Database Link [ID 473635.1]

Modified 09-DEC-2009 Type HOWTO Status PUBLISHED

In this Document

Goal

Solution

Applies to:

Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 11.1.0.6

Information in this document applies to any platform.

Goal

How to close and expire database link

Solution

Following are the ways links could be closed

A) Only session which opened the database link can close it..

 Database link are closed when the session is closed...

      select * from dba_DB_LINKS   --->will show database links created

      V$DBLINK  will lists all open/active database links in your session,..

       For an indication on how long the dblink has been open, run:

        select t.addr, s.sid, s.username, s.machine, s.status,

        (sysdate - to_date(t.start_time, 'MM/DD/YY HH24:MI:SS')) * 24 as hours_active

        from v$transaction t, v$session s

        where t.addr = s.taddr;

        How to know if a transaction is local or distributed?

        Check v$global_transaction

B) Using ALTER SESSION or explicitly using command:

        alter session close database link <name>;

        or Use the following package:

        dbms_session.close_database_link(<name>);

C) It is also posible to set idle_time limit to user under which connects dblink.

    On the server side of dblink (target of dblink) issue:

    create profile pidle limit idle_time 5; -- 5 minutes

    alter user test profile pidle; -- user under which connects dblink

    alter system set resource_limit=true; -- must be set to work idle_time limit

    (or add resource_limit=true to init.ora or both)

Reference:

=======

Oracle® Database Administrator's Guide 11g Release 1 (11.1)

Part Number B28310-03 Check Section Determining Which Link Connections Are Open

Oracle® Database SQL Language Reference 11g Release 1 (11.1)

Part Number B28286-02 Check Section CREATE DATABASE LINK

Oracle® Database PL/SQL Packages and Types Reference11g Release 1 (11.1.)

Part Number B28419-02 Chapter 117 Check CLOSE_DATABASE_LINK Procedure

Products

•Oracle Database Products > Oracle Database > Oracle Database > Oracle Server – Enterprise Edition

Keywords

DBMS_SESSION.CLOSE_DATABASE_LINK; DATABASE LINK; DBLINK