Home » RDBMS Server » Performance Tuning » How to check index is re-built (11g, 11.2.0.3, Window server)
How to check index is re-built [message #636655] Thu, 30 April 2015 00:00 Go to next message
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
Re: How to check index is re-built [message #636656 is a reply to message #636655] Thu, 30 April 2015 00:33 Go to previous messageGo to next message
BlackSwan
Messages: 26289
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.
Re: How to check index is re-built [message #636657 is a reply to message #636655] Thu, 30 April 2015 01:39 Go to previous messageGo to next message
John Watson
Messages: 7673
Registered: January 2010
Location: Global Village
Senior Member
dba_objects will show you the last DDL time, which might be the rebuild.
Re: How to check index is re-built [message #636667 is a reply to message #636657] Thu, 30 April 2015 02:21 Go to previous message
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.

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
Previous Topic: Query running very slow
Next Topic: ORACLE SQL PERFORMANCE TUNING AND OPTIMIZATION - Kevin Meade's book corner
Goto Forum:
  


Current Time: Wed Nov 21 09:44:07 CST 2018