Problems in DB Link Connections.
- From: <arul.kumar@xxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 Nov 2004 12:03:36 -0000
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
