Thursday, September 20, 2012

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