{"id":73337,"date":"2012-02-17T15:18:29","date_gmt":"2012-02-17T15:18:29","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/session-sql\/"},"modified":"2021-07-14T13:08:02","modified_gmt":"2021-07-14T13:08:02","slug":"session-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/session-sql\/","title":{"rendered":"Session SQL"},"content":{"rendered":"

From time to time a request for a query to \u201cshow the current SQL\u201d for each session appears on the internet. It\u2019s not really difficult to write something suitable, but this particular request is an interesting demonstration of how much inertia there is in the Oracle world. Here\u2019s a recent suggestion of what the query should look like:<\/p>\n

\r\nselect\r\n        sess.sid, sess.serial#, sess.username, sess.status,\r\n        sess.osuser, sess.machine, sess.module, sess.event,\r\n        sess.logon_time, ss.sql_id, ss.sql_text\r\nfrom\r\n        v$session sess,\r\n        v$sql     ss\r\nwhere\r\n        sess.sql_id   = ss.sql_id\r\nand     sess.username = 'EFTUSER'\r\n;\r\n<\/pre>\n
The query has an error in it that appears extremely frequently in response to this request. Can you spot what it is ? (On the plus side, the query references v$sql rather than v$sqlarea \u2013 so it\u2019s not too nasty.)<\/p>\n
You can find the answer out on my blog; Oracle Scratchpad<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
From time to time a request for a query to \u201cshow the current SQL\u201d for each session appears on the internet. It\u2019s not really difficult to write something suitable, but this particular request is an interesting demonstration of how much inertia there is in the Oracle world. Here\u2019s a recent suggestion of what the query should look like: select sess.sid,…<\/p>\n","protected":false},"author":101205,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[],"coauthors":[],"class_list":["post-73337","post","type-post","status-publish","format-standard","hentry","category-oracle-databases"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73337","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/101205"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73337"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73337\/revisions"}],"predecessor-version":[{"id":91781,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73337\/revisions\/91781"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73337"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73337"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73337"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73337"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}