Problems in DB Link Connections.

Dear DBAs,  

We are facing lot of problems using db links across remote systems. I tried to simulate the same for better understanding. The facts are as follows:  

Observations on a simulated environment  

Problem : The target remote systems complaining that we (source) are holding lots of INACTIVE connections which are not needed (at target server!)  

Source - A
Target - B  

Db Link - TestLink04  

1. A database connection will be created at the target whenever source uses a db link.
2. The status of the above connection will be shown as ACTIVE only when it is REALLY doing some massive work. ASAP, it changes the status to INACTIVE

The above is one of the reasons why MOST of the connections are being shown as INACTIVE, though ACTUALLY they are in use.  

For example : Firing a SELECT * FROM <mailto:Big_Table_at_target> Big_Table_at_target IS changing the status of the session at target from INACTIVE to ACTIVE MANY a times in quick succession though at source, STILL the records are getting displayed.  

3. Though the source is finished with its work with the db link (by properly issuing a COMMIT / ROLLBACK), the connection at the target will be still present (as INACTIVE, ofcourse).  

4. The target database connection will ONLY terminate when the source connection terminates (which NEVER happens in OSD I believe). So, the connection once established at the target will LIVE for ever if it is not killed forcibly!  

5. If the target INACTIVE connection is killed, then at source, any further queries to the DBLINK connection may throw  

ORA-02068 : following severe error from testlink04 ORA-01012: not logged on  

at the first invocation of any SQL at target through the same session at source. Subsequent calls will automatically trigger a new connection at target.  

Any suggestions for reducing the number of INACTIVE connections at the target will be appreciated.  

Many Thanks,
Arul.

--
http://www.freelists.org/webpage/oracle-l