To identify long running queries

col name form a35
col value form a20
set head off
set feedback off
select '               '||upper(name)name,value
from v$parameter
where name = 'timed_statistics'
/
select 'Current Time -> '||to_char(sysdate,'dd-mon-yy hh24:mi:ss')
from dual
/
set feedback on
set head on
cle bre
col event form a30 trunc head "Event| Waiting For"
col p1 form 9999999999 trunc
col p2 form 9999999999 trunc
col p3 form 999999999 trunc
col sql_hash_value form 99999999999999 trunc
col wait_time form 999 trunc head "Last|Wait|Time"
col command form a7 trunc head "Command"
col state form a10 trunc
col sid form 9999 trunc
select a.sid,
         decode(command,0,'None',2,'Insert',3,'Select',
                 6,'Update',7,'Delete',10,'Drop Index',12,'Drop Table',
                 45,'Rollback',47,'PL/SQL',command) command,
         a.event,a.p1,a.p2,a.p3,b.sql_hash_value,a.state,a.wait_time
from v$session_wait a,V$session b
where b.sid=a.sid
and (a.sid>20 and a.event not in('SQL*Net message from client',
                        'SQL*Net message to client','pipe get','rdbms ipc message','queue messages','jobq slave wait')
or (a.sid<=20 and a.event not in ('rdbms ipc message','smon timer',
        'pmon timer','SQL*Net message from client','gcs remote message')))
order by decode(event,'pipe get','A',event),p1,p2
/

**********************************************************************************

********************************************************************************** 

********************************************************************************** 

set lines 120

cle bre

set lines 129

col sid form 9999

col start_time head "Start|Time" form a12 trunc

col opname head "Operation" form a12 trunc

col target head "Object" form a30 trunc

col totalwork head "Total|Work" form 9999999999 trunc

col Sofar head "Sofar" form 9999999999 trunc

col elamin head "Elapsed|Time|(Mins)" form 99999999 trunc

col tre head "Time|Remain|(Mins)" form 999999999 trunc

col Module Format a10

 

--select sid,to_char(start_time,'dd-mon:hh24:mi') start_time,

--       opname,target,totalwork,sofar,(elapsed_Seconds/60) elamin,

--       time_remaining tre

--from v$session_longops

--where totalwork <> SOFAR

--order by start_time

 

 

select a.sid,to_char(start_time,'dd-mon:hh24:mi') start_time,

         opname,substr(b.module,1,10) Module ,target,totalwork,sofar,(elapsed_Seconds/60) elamin,

         time_remaining/60 tre

from v$session_longops a, v$session b

where

a.sid = b.sid and

totalwork <> SOFAR

order by start_time

/