Everyone has a favorite script to find the active SQL statements. Here is one of mine, it’s RAC friendly too..
set serverout on size 999999 declare begin dbms_output.put_line(' '); dbms_output.put_line('************* Start report for WAITING sessions with current SQL ***************'); for x in (select vs.inst_id, vs.sid || ',' || vs.serial# sidser, vs.sql_address, vs.sql_hash_value, vs.last_call_et, vsw.seconds_in_wait, vsw.event, vsw.state from gv$session_wait vsw, gv$session vs where vsw.sid = vs.sid and vsw.inst_id = vs.inst_id and vs.type <> 'BACKGROUND' and vsw.event NOT IN ('rdbms ipc message' ,'smon timer' ,'pmon timer' ,'SQL*Net message from client' ,'lock manager wait for remote message' ,'ges remote message' ,'gcs remote message' ,'gcs for action' ,'client message' ,'pipe get' ,'Null event' ,'PX Idle Wait' ,'single-task message' ,'PX Deq: Execution Msg' ,'KXFQ: kxfqdeq - normal deqeue' ,'listen endpoint status' ,'slave wait' ,'wakeup time manager')) loop begin dbms_output.put_line('Event WaitState InstID SidSerial LastCallEt SecondsInWait'); dbms_output.put_line('************************* ******************** ****** *********** ********** *************'); dbms_output.put_line(rpad(x.event,25) ||' '|| rpad(x.state,20) ||' '|| lpad(x.inst_id,6) ||' '|| lpad(x.sidser,11) ||' '|| lpad(x.last_call_et,10) ||' '|| lpad(x.seconds_in_wait,13)); dbms_output.put_line(' SQLText '); dbms_output.put_line('****************************************************************'); for y in (select sql_text from gv$sqltext where address = x.sql_address and hash_value = x.sql_hash_value and inst_id = x.inst_id order by piece) loop dbms_output.put_line(y.sql_text); end loop; end; end loop; dbms_output.put_line('************** End re! port for sessions waiting with current SQL ****************'); dbms_output.put_line(' '); end; /
http://www.oracle.com/technology/oramag/code/tips2004/062104.html