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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s