{"id":23,"date":"2012-04-02T07:34:00","date_gmt":"2012-04-02T07:34:00","guid":{"rendered":"http:\/\/anjo.pt\/wp\/keyword-oracle\/2012\/04\/02\/who-is-using-the-db-link\/"},"modified":"2015-11-17T20:27:24","modified_gmt":"2015-11-17T19:27:24","slug":"who-is-using-the-db-link","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2012\/04\/02\/who-is-using-the-db-link\/","title":{"rendered":"Who is using the DB Link?"},"content":{"rendered":"<p>On the magic web I found this script at <a href=\"http:\/\/jkstill.blogspot.com\/2010\/03\/whos-using-database-link.html\" rel=\"nofollow\">Jared Still&#8217;s blog<\/a>. It allows to see which database links are being used and by whom. Information sometimes useful (needs to be run as SYSDBA):<\/p>\n<pre style=\"background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;\"><code>-- who is querying via dblink?\n-- Courtesy of Tom Kyte, via Mark Bobak\n-- this script can be used at both ends of the database link\n-- to match up which session on the remote database started\n-- the local transaction\n-- the GTXID will match for those sessions\n-- just run the script on both databases\n\nSelect \/*+ ORDERED *\/\nsubstr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10)      \"ORIGIN\",\nsubstr(g.K2GTITID_ORA,1,35) \"GTXID\",\nsubstr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) \"LSESSION\" ,\ns2.username,\nsubstr(\n   decode(bitand(ksuseidl,11),\n      1,'ACTIVE',\n      0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),\n      2,'SNIPED',\n      3,'SNIPED',\n      'KILLED'\n   ),1,1\n) \"S\",\nsubstr(w.event,1,10) \"WAITING\"\nfrom  x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w, v$session s2\nwhere  g.K2GTDXCB =t.ktcxbxba\nand   g.K2GTDSES=t.ktcxbses\nand  s.addr=g.K2GTDSES\nand  w.sid=s.indx\nand s2.sid = w.sid<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>On the magic web I found this script at Jared Still&#8217;s blog. It allows to see which database links are being used and by whom. Information sometimes useful (needs to be run as SYSDBA): &#8212; who is querying via dblink? &#8212; Courtesy of Tom Kyte, via Mark Bobak &#8212; this script can be used at [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,6],"tags":[],"class_list":["post-23","post","type-post","status-publish","format-standard","category-db-links","category-oracle","czr-hentry"],"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/23","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=23"}],"version-history":[{"count":1,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/23\/revisions"}],"predecessor-version":[{"id":167,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/23\/revisions\/167"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=23"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=23"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=23"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}