Re: Checking the status of remote session

I would also like to mention that SID on remote session remains same throughout the job execution period. If that helps.

Thanks!

Ramniwas

On Mon, Aug 29, 2016 at 11:12 PM, Ramnivas Chaurasia < ramnivaschaurasia_at_gmail.com> wrote:

> 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
>
>
>
>

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