From time to time a request for a query to “show the current SQL” for each session appears on the internet. It’s 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’s a recent suggestion of what the query should look like:

select
        sess.sid, sess.serial#, sess.username, sess.status,
        sess.osuser, sess.machine, sess.module, sess.event,
        sess.logon_time, ss.sql_id, ss.sql_text
from
        v$session sess,
        v$sql     ss
where
        sess.sql_id   = ss.sql_id
and     sess.username = 'EFTUSER'
;

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 – so it’s not too nasty.)

You can find the answer out on my blog; Oracle Scratchpad.