RE: SYS_CONTEXT DBLINK_INFO returns null over db link

From: Patterson, Joel <jpatterson_at_entint.com>
Date: Wed, 5 Mar 2014 00:36:22 -0500
Message-ID: <C1117B1AA0340645894671E09A7891F715F3B55D92_at_EIHQEXVM2.ei.local>

Rich, Here is something I picked up along the way, maybe it will help :) Don't worry about startime_sessionproc, that is one of my scripts, and note to self.

• who is querying via dblink?
• Courtesy of Tom Kyte, via Mark Bobak
• this script can be used at both ends of the database link
• to match up which session on the remote database started
• the local transaction
• the GTXID will match for those sessions
• just run the script on both databases
  --
  
• The PID is shown in the ORIGIN column, at the end.
• Run s:\see\sessions\startime_sessionproc
• for more information about the user.
  --
  
• See also http://jkstill.blogspot.com/2010/03/whos-using-database-link.html
  --
  

prompt
prompt This query for 9i databases because v$session does not have event prompt column, v$session_wait is needed. prompt
Select /*+ ORDERED */

substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10)      "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,

s2.username,
substr(

   decode(bitand(ksuseidl,11),

      1,'ACTIVE',
      0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),
      2,'SNIPED',
      3,'SNIPED',
      'KILLED'

   ),1,1
) "S",
substr(w.event,1,10) "WAITING"
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;

prompt
prompt Run following query as SYSDBA
prompt
select /*+ ORDERED */

substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,

s2.username,
substr(
decode(bitand(ksuseidl,11),

1,'ACTIVE',
0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),
2,'SNIPED',
3,'SNIPED',

'KILLED'
),1,1
) "S",
substr(s2.event,1,10) "WAITING"
from x$k2gte g, x$ktcxb t, x$ksuse s, v$session s2 where g.K2GTDXCB =t.ktcxbxba
and g.K2GTDSES=t.ktcxbses
and s.addr=g.K2GTDSES
and s2.sid=s.indx;

--

Joel Patterson
Sr. Database Administrator | Enterprise Integration Phone: 904-928-2790 | Fax: 904-733-4916
http://www.entint.com/

http://www.entint.com/

http://www.facebook.com/pages/Enterprise-Integration/212351215444231 http://twitter.com/#!/entint http://www.linkedin.com/company/18276?trk=tyah http://www.youtube.com/user/ValueofIT

This message (and any associated files) is intended only for the use of the addressee and may contain information that is confidential, subject to copyright or constitutes a trade secret. If you are not the intended recipient, you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message, is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and deleting it from your computer. Messages sent to and from us may be monitored. Any views or opinions presented are solely those of the author and do not necessarily represent those of the company. [v.1.1]

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich Jesse Sent: Monday, March 03, 2014 2:34 PM
To: oracle-l_at_freelists.org
Subject: Re: SYS_CONTEXT DBLINK_INFO returns null over db link

Karth replies:

> Select property_value from Databae_properties_at_remotedb Where > property_name='GLOBAL_DB_NAME';

That's going to get me the "target" information. I was looking for the "source" information, or what instance the DB link was coming from.

I've decided to switch tactics and perform an extra SELECT over the DB link instead of trying to figure out how to grab the remote link info.

Thanks!

Rich

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Mar 05 2014 - 06:36:22 CET