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@target> 
Big_Table@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

Other related posts: