SQL QUERY TO FIND LONG RUNNING SESSION IN ORACLE

SQL query to find long running stats job

set lines 200
set pages 1000
col OPNAME format a20
col MESSAGE format a20
col username format a10
col opname format a10
col sid format 999999
col serial# format 999999
col TIME_REMAINING format 999999
col pct format 9999999
select sid,
       serial#,
       USERNAME,
       opname,
       100*(sofar/totalwork) as pct,
       to_char(START_TIME,'dd-mm-yy hh24:mi:ss') as starttime,
       to_char(LAST_UPDATE_TIME,'dd-mm-yy hh24:mi:ss') as change,
       TIME_REMAINING,
       ELAPSED_SECONDS,
       MESSAGE 
from v$session_longops 
where  sofar < totalwork
and message like '%Stat%';

SQL query to find long running RMAN backup or restore or recovery job

set lines 200
set pages 1000
col OPNAME format a20
col MESSAGE format a20
col username format a10
col opname format a10
col sid format 999999
col serial# format 999999
col TIME_REMAINING format 999999
col pct format 9999999
select sid,
           serial#,
           USERNAME,
           opname,
           100*(sofar/totalwork) as pct,
           to_char(START_TIME,'dd-mm-yy hh24:mi:ss') as starttime,
           to_char(LAST_UPDATE_TIME,'dd-mm-yy hh24:mi:ss') as change,
           TIME_REMAINING,
           ELAPSED_SECONDS,
           MESSAGE 
from v$session_longops 
where  sofar < totalwork
and message like '%RMAN%';

SQL query to find long running normal job

set lines 200
set pages 1000
col OPNAME format a20
col MESSAGE format a20
col username format a10
col opname format a10
col sid format 999999
col serial# format 999999
col TIME_REMAINING format 999999
col pct format 9999999
select sid,serial#,USERNAME,opname,100*(sofar/totalwork) as pct,to_char(START_TIME,'dd-mm-yy hh24:mi:ss') as starttime,
to_char(LAST_UPDATE_TIME,'dd-mm-yy hh24:mi:ss') as change,TIME_REMAINING,ELAPSED_SECONDS,MESSAGE 
from v$session_longops 
where  sofar < totalwork;

Leave a Reply