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@(protected) > Big_Table@(protected) 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
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
