Oracle – How to Track Progress of an Index Rebuild

indexmonitoringoracle

How can I track the progress of a command such as this?

alter index myindex rebuild reverse online;

Best Answer

You should Query the V$SESSION_LONGOPS

select ops.OPNAME, ops.TIME_REMAINING,ops.start_time
from v$session_longops ops
where ops.sid=&your_sid
and ops.serial#=&your_serial
and time_remaining>0;

If the index rebuild is parallel, than you you must find the parallel slaves that do the work. This can be done by querying the V$PX_SESSION. Your session that issued the 'alter index' statement is the query coordinator

select ops.OPNAME, ops.TIME_REMAINING,ops.start_time
from v$px_session par, v$session_longops ops
where par.qcsid=&your_sid
and par.qcserial#=&your_serial
and ops.sid=par.sid
and ops.serial#=par.serial#
and ops.time_remaining>0;

But an index creation consists of more than one longops (scan the table, sort the data,...)

Related Question