V$DBLINK

Tuesday, October 27, 2009

V$DBLINK

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

===================================================================

V$DBLINK describes all database links (links with IN_TRANSACTION = YES) opened by the session issuing the query on V$DBLINK. These database links must be committed or rolled back before being closed.

Column	Datatype	Description
DB_LINK	VARCHAR2(128)	Name of the database link
OWNER_ID	NUMBER	Owner of the database link UID
LOGGED_ON	VARCHAR2(3)	Whether the database link is currently logged on
HETEROGENEOUS	VARCHAR2(3)	Whether the database link is heterogeneous
PROTOCOL	VARCHAR2(6)	Communication protocol for the database link
OPEN_CURSORS	NUMBER	Whether there are open cursors for the database link
IN_TRANSACTION	VARCHAR2(3)	Whether the database link is currently in a transaction
UPDATE_SENT	VARCHAR2(3)	Whether there has been an update on the database link
COMMIT_POINT_STRENGTH	NUMBER	Commit point strength of the transactions on the database link

Note:

1. DBA_DB_LINKS describes all database links in the database. ALL_DB_LINKS describes the database links accessible to the current user. USER_DB_LINKS describes the database links owned by the current user.

2. How to close and expire database link (Metalink 473635.1)

Database link is opened one for every session. 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
select * from V$DBLINK ---> will lists all open/active database links in your session

B) Using ALTER SESSION or explicitly using command:

            alter session close database link ;
      or Use the following package:
            dbms_session.close_database_link();

C) It is also possible 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)