currwaitobj.sql SQl_ID and SQL statement you can get from currwaitobj.sql

set lines 120 pages 200

column event format a30

column object_type format a14

column object_name format a30

column owner format a10

column sql_text format a120

spool currwaitobj

accept event_name prompt ‘Enter fraction of event to trace : ‘

accept start_time1 prompt ‘Start Time in (DD-MON-RRRR:HH24:MI format) : ‘

accept stop_time1 prompt ‘Stop Time in (DD-MON-RRRR:HH24:MI format) : ‘

select distinct a.event

from v$active_session_history a

where sample_time between to_date(‘&start_time1’, ‘DD-MON-RRRR:HH24:MI’) and to_date(‘&stop_time1’, ‘DD-MON-RRRR:HH24:MI’);

select distinct a.event, s.sql_id, s.sql_text

from v$active_session_history a, v$sqlarea s

where s.sql_id = a.sql_id

and lower(a.event) like lower(‘%&event_name%’)

and sample_time between to_date(‘&start_time1’, ‘DD-MON-RRRR:HH24:MI’) and to_date(‘&stop_time1’, ‘DD-MON-RRRR:HH24:MI’)

order by s.sql_id;

spool off