DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Saturday, September 5, 2015

Monitoring the Index creation or Index rebuild


Monitoring the Index creation or Index rebuild


set lines 200
col "Index Operation" for a60
select sess.sid as "Session ID", sql.sql_text as "Index Creation",
longops.totalwork, longops.sofar,
longops.elapsed_seconds/60 as "Runtime Mins",
longops.time_remaining/60 as "ETA Mins"
from v$session sess, v$sql sql, v$session_longops longops
where
sess.sid=longops.sid
and sess.sql_address = sql.address
and sess.sql_address = longops.sql_address
and sess.status  = 'ACTIVE'
and longops.totalwork > longops.sofar
and sess.sid not in ( SELECT sys_context('USERENV', 'SID') SID  FROM DUAL)
and sql.sql_text like '%INDEX%'
;

No comments: