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