dbms_session.close_database_link in oracle procedures

I have many procedures for which i am using the same db link and this procedure can be executed at different time or some of the procedure runs at the same time depending on the scheduler job. This procedure used to fetch the data from remote database and insert the data in my local database. As i dont want to left open the db connection after execution of every procedure because it produces the load on the database connection which might impact on query cost and query execution time. Thats why i am using dbms_session to close the database link at the end of each and every procedure. DBMS_SESSION.CLOSE_DATABASE_LINK But i dont know whether this results any issue while fetching the data for some of the procedure which executes at the same time. As the dbms session close the db link connection for some of the procedures and at the same time if any of the procedure try to acess the db link and it might not fetch the data or results in any error. Will this situation arises while using DBMS_SESSION.CLOSE_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)