Index creation progress in oracle

Index creation progress in oracle

----------Progress index creation--------------

set lines 200
 col "Index Operation" for a60
 select sess.sid as "Session ID", sql.sql_text as "Index Operation",
 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 upper(sql.sql_text) like '%INDEX%'

About the Author

debasis maity

12+ years of rich experience on Database Administrations and on Infrastructure Solution Architect. AWS Certified Solution Architect and Senior Oracle DBA