Hi,
There is a table which gets refreshed using DB link from another database
on daily basis. It's scheduled in crontab and runs for around 10 hours
every day.
Lately, there have been situations where remote database session gets
terminated due to some network issue, but the session on local database is
still active but in hung situation.
Because of that, the job does not return any error message and shows that
process is still running.
To resolve this situation, I want to schedule another job to monitor the
corresponding session in remote database. But I have not got a way yet to
do that.
I tried using below SQL to get the status from remote database:
*Select /*+ ORDERED */*
* substr(s.ksusepid,1,10) as origin ,*
*s2.username,s2.machine*
*from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w, v$session s2*
*where g.K2GTDXCB =t.ktcxbxba*
*and g.K2GTDSES=t.ktcxbses*
*and s.addr=g.K2GTDSES*
*and w.sid=s.indx*
*and s2.sid = w.sid;*
But the problem is that, the job issues "commit" after every single insert
and after the commit, the connection on remote DB gets closed until the
next insert started. And if the monitoring job runs during this time, it
sends false alarm.
So, I want to get that SID details from remote which was originated because
of this job irrespective of the remote connection closed due to "commit".
DB Version: EE 11.2.0.3
Thank you!
Ramniwas
