OraFAQ Forum: Performance Tuning » How to check index is re-built

Home » RDBMS Server » Performance Tuning » How to check index is re-built (11g, 11.2.0.3, Window server)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to check index is re-built [message #636655]

Thu, 30 April 2015 00:00

ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta

Senior Member

Dear All,

Last day, I had re-build the indexes. But unable to find, where it is re-build or not. Can any one suggest me, which view should I prefer to know about index rebuild status.

Regards,
Ashish Kumar Mahanta

Report message to a moderator

Re: How to check index is re-built [message #636656 is a reply to message #636655]

Thu, 30 April 2015 00:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ashishkumarmahanta80 wrote on Wed, 29 April 2015 22:00

Dear All,

Last day, I had re-build the indexes. But unable to find, where it is re-build or not. Can any one suggest me, which view should I prefer to know about index rebuild status.

Regards,
Ashish Kumar Mahanta

POST sql & RESULTS that show a rebuild is worthwhile.

Report message to a moderator

Re: How to check index is re-built [message #636657 is a reply to message #636655]

Thu, 30 April 2015 01:39

John Watson
Messages: 8970
Registered: January 2010
Location: Global Village

Senior Member

dba_objects will show you the last DDL time, which might be the rebuild.

Report message to a moderator

Re: How to check index is re-built [message #636667 is a reply to message #636657]

Thu, 30 April 2015 02:21

ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta

Senior Member

Dear John,

You are right. Last time i checked the last_ddl_time in dba_objects which was not getting update. Might be my execution problem.
Today, again I checked the same thing and it shows me my answer.

Code: [Select all] [Show/ hide]

SQL> select owner, index_name, table_name, ini_trans, blevel, last_analyzed    from dba_indexes
  2  where index_name='PK_REINS_COVER_ENDORSEMENT';

OWNER                          INDEX_NAME                                       
------------------------------ ------------------------------                   
TABLE_NAME                      INI_TRANS     BLEVEL LAST_ANAL                  
------------------------------ ---------- ---------- ---------                  
REINS                          PK_REINS_COVER_ENDORSEMENT                       
RI_TD_REINS_COVER_ENDORSEMENT           2          3 26-APR-15                  
                                                                                

SQL> set lines 125 pages 200
SQL> select owner, index_name, table_name, ini_trans, blevel, last_analyzed    from dba_indexes
  2  where index_name='PK_REINS_COVER_ENDORSEMENT';

OWNER                          INDEX_NAME                     TABLE_NAME                      INI_TRANS     BLEVEL LAST_ANAL 
------------------------------ ------------------------------ ------------------------------ ---------- ---------- --------- 
REINS                          PK_REINS_COVER_ENDORSEMENT     RI_TD_REINS_COVER_ENDORSEMENT           2          3 26-APR-15 



SQL> alter index REINS.PK_REINS_COVER_ENDORSEMENT initrans 100;

Index altered.


SQL> COL OWNER FOR A30
SQL> COL OBJECT_NAME FOR A30
SQL> SELECT owner, object_name, created, TO_CHAR(last_ddl_time, 'DD-MM-YYYY HH24:MI:SS')  FROM DBA_OBJECTS WHERE OBJECT_NAME='PK_REINS_COVER_ENDORSEMENT';

OWNER                          OBJECT_NAME                    CREATED   TO_CHAR(LAST_DDL_TI                                  
------------------------------ ------------------------------ --------- -------------------                                  
REINS                          PK_REINS_COVER_ENDORSEMENT     22-APR-15 30-04-2015 12:34:52                                  

SQL> ALTER INDEX REINS.PK_REINS_COVER_ENDORSEMENT REBUILD ONLINE  NOLOGGING COALESCE;

Index altered.

SQL> SELECT owner, object_name, created, TO_CHAR(last_ddl_time, 'DD-MM-YYYY HH24:MI:SS')  FROM DBA_OBJECTS WHERE OBJECT_NAME='PK_REINS_COVER_ENDORSEMENT';

OWNER                          OBJECT_NAME                    CREATED   TO_CHAR(LAST_DDL_TI                                  
------------------------------ ------------------------------ --------- -------------------                                  
REINS                          PK_REINS_COVER_ENDORSEMENT     22-APR-15 30-04-2015 12:44:37

Regards,
Ashish Kumar Mahanta

Report message to a moderator

Previous Topic:	Query running very slow
Next Topic:	ORACLE SQL PERFORMANCE TUNING AND OPTIMIZATION - Kevin Meade's book corner

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 13 20:20:07 CDT 2025