DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Getting last rebuild date of index

  1. #1
    Join Date
    Dec 2003
    Posts
    90

    Getting last rebuild date of index

    Is the date created or updated of an index stored in a system table anywhere?

    I need to get the date and time of the last rebuild on an index.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    To my knowledge the only date that you can get is the last analyzed date.

    Code:
      1  select owner, table_name, column_name
      2    from dba_tab_columns where data_type='DATE'
      3     and table_name like '%IND%'
      4*    and owner='SYS'
    SQL> /
    
    OWNER TABLE_NAME                     COLUMN_NAME
    ----- ------------------------------ -------------
    SYS   ALL_INDEXES                    LAST_ANALYZED
    SYS   ALL_IND_PARTITIONS             LAST_ANALYZED
    SYS   ALL_IND_SUBPARTITIONS          LAST_ANALYZED
    SYS   DBA_INDEXES                    LAST_ANALYZED
    SYS   DBA_IND_PARTITIONS             LAST_ANALYZED
    SYS   DBA_IND_SUBPARTITIONS          LAST_ANALYZED
    SYS   IND$                           ANALYZETIME
    SYS   IND$                           SPARE6
    SYS   INDCOMPART$                    ANALYZETIME
    SYS   INDPART$                       ANALYZETIME
    SYS   INDSUBPART$                    ANALYZETIME
    SYS   KU$_INDEX_COL_VIEW             SPARE6
    SYS   KU$_INDEX_VIEW                 ANALYZETIME
    SYS   KU$_INDEX_VIEW                 SPARE6
    SYS   KU$_IND_COMPART_VIEW           ANALYZETIME
    SYS   KU$_IND_PART_VIEW              ANALYZETIME
    SYS   KU$_IND_SUBPART_VIEW           ANALYZETIME
    SYS   USER_INDEXES                   LAST_ANALYZED
    SYS   USER_IND_PARTITIONS            LAST_ANALYZED
    SYS   USER_IND_SUBPARTITIONS         LAST_ANALYZED
    
    20 rows selected.

  3. #3
    Join Date
    Mar 2002
    Posts
    534
    I think USER_OBJECTS.LAST_DDL_TIME may also be updated when you execute an ALTER INDEX REBUILD.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by mike9
    I think USER_OBJECTS.LAST_DDL_TIME may also be updated when you execute an ALTER INDEX REBUILD.
    I believe that you are correct.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width