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
Hope this help you..... Happy Learning
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