Progress status of an index rebuild or creation, or other long operations.

David Lozano Lucas's picture
articles: 

When we launch a long operation, such as a RMAN backup or rebuild of a large index, we can come to despair of not having an estimate of the time it may take. We can even come to think that is doing nothing.

For the progress of a long operation we can query the view V$SESSION_LONGOPS, first obtaining the process ID from V$SESSION. In the case of DBA, we know exactly which user is rebuilding the index, so we can simplify it into a single query.

The following example shows the progress of the reconstruction of a partition of an index. It was launched using the PARTITION modifier, so that various processes were created in the instance:

SELECT MESSAGE FROM V$SESSION_LONGOPS WHERE SID IN (SELECT SID FROM V$SESSION WHERE USERNAME='SYS' AND STATUS='ACTIVE') ORDER BY START_TIME;

Rowid Range Scan: APPU.RT_SK: 111958 out of 111958 Blocks done
Rowid Range Scan: APPU.RT_SK: 99670 out of 99670 Blocks done
Rowid Range Scan: APPU.RT_SK: 50518 out of 50518 Blocks done
Rowid Range Scan: APPU.RT_SK: 238934 out of 238934 Blocks done
Rowid Range Scan: APPU.RT_SK: 38230 out of 38230 Blocks done
Rowid Range Scan: APPU.RT_SK: 42273 out of 42273 Blocks done
Rowid Range Scan: APPU.RT_SK: 1160535 out of 1160535 Blocks done

Oracle considers "long operation" any operation longer than 6 seconds.