Index Rebuild - Progress & Index creation Progress

index Rebuild - Progress & Index creation Progress

To identify the index rebuild progress.

i have used the oracle schema to identify the session which are active in nature.

Note: For some reason V$SESSION_LONGOPS doesn't show the progress of index Rebuild process. use the below query for index rebuild. index creation can be seen in the below query and other query with the
V$SESSION_LONGOPS

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

MESSAGE

-----------------------------------------------------------------------------------Table Scan:  (stale or locked) obj# 31940: 1426408 out of 1426408 Blocks done

Table Scan:  BHUVAN.EMP: 2388523 out of 2388523 Blocks done

Sort Output:  : 235280 out of 235280 Blocks done

Table Scan:  BHUVAN.EMP: 154318 out of 2388523 Blocks done

-- i have merge active session with the V$SESSION_LONGOPS view to retrieve progress of index creation with the index creation syntax.

SQL> col a.sid format 9999

SQL> select a.sid, sql_text ,target, sofar, totalwork, time_remaining still, elapsed_seconds tillnow

  2  from v$session a ,  v$sql b, v$session_longops c

  3  where a.sid=c.sid

  4  and a.sql_address = b.address

  5  and a.sql_address = c.sql_address

and status  = 'ACTIVE';

 SID

SQL_TEXT

TARGET                                                                SOFAR  TOTALWORK      STILL    TILLNOW

---------------------------------------------------------------- ---------- ------ 366

CREATE INDEX "EMP~EXT" ON "EMP" ("CLIENT", "BPEXT") PCTFREE 10 INITRANS 002 TABLESPACE PBHUVAN COMPRESS 2 STORAGE (INITIAL 0000000064 K NEXT 0000001024 K MINEXTENTS 0000000001 MAXEXTENTS UNLIMITED PCTINCREASE 0000 FREELISTS 001)

BHUVAN.EMP                                                        894046    2388523        563        337

Hope this help you..... Happy Learning