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;