List the SQL statement running by users

Written by Kokei's Blog
Posted Apr 26, 2009 at 8:33 PM

This sql script list the SQL statements from the shared cursor in the SGA and the sessions who have executed them

set linesize 120
col c1 format a9 heading "OS User"
col c2 format a15 heading "Oracle User"
col c3 format 9999 justify left heading "SID"
col c4 format 99999 justify left heading "SERIAL#"
col c5 format a80 heading "SQL"
select s.osuser c1, s.username c2, s.sid c3, s.serial# c4,st.sql_text c5
  from v$sqltext st, v$session s
   where st.address = s.sql_address
-- and b.status = 'ACTIVE' /* uncommet this line if you only want to see currently active session */
   and st.hash_value = s.sql_hash_value
order by st.hash_value,st.piece
/

OS User Oracle User SID SERIAL# SQL
--------- --------------- ----- ------- --------------------------------------------------------------------------------
oracle SYS 684 351 select s.osuser c1, s.username c2, s.sid c3, s.serial# c4,st.sq
oracle SYS 684 351 l_text c5 from v$sqltext st, v$session s where st.address
oracle SYS 684 351 = s.sql_address -- and b.status = 'ACTIVE' /* YOU CAN CH
oracle SYS 684 351 OOSE THIS OPTION ONLY TO SEE ACTVE TRANSACTION ON THAT MOMENT */
oracle SYS 684 351 and st.hash_value = s.sql_hash_value order by st.hash_value
oracle SYS 684 351 ,st.piece