This discussion is archived
12 Replies Latest reply: Jan 19, 2013 2:24 PM by Osama_Mustafa RSS

how to find last index rebuild date

827207 Newbie
Currently Being Moderated
Hi,

How to find last index rebuild date in oracle 9i database.
  • 1. Re: how to find last index rebuild date
    Aman.... Oracle ACE
    Currently Being Moderated
    Pravin wrote:
    Hi,

    How to find last index rebuild date in oracle 9i database.
    Just a quick thought, can't say it's a definite answer.
    SQL*Plus: Release 11.2.0.3.0 Production on Sat Jan 19 16:12:24 2013
    
    Copyright (c) 1982, 2011, Oracle.  All rights reserved.
    
    startup
    Connected to an idle instance.
    
    SQL> 
    ORACLE instance started.
    
    Total System Global Area  263639040 bytes
    Fixed Size                  1344312 bytes
    Variable Size             205524168 bytes
    Database Buffers           54525952 bytes
    Redo Buffers                2244608 bytes
    Database mounted.
    Database opened.
    SQL> SQL> 
    SQL> select object_name, object_type, last_ddl_time, created from dba_objects 
      2  where object_name='PK_EMP';
    
    OBJECT_NAME
    --------------------------------------------------------------------------------
    OBJECT_TYPE         LAST_DDL_ CREATED
    ------------------- --------- ---------
    PK_EMP
    INDEX               18-SEP-11 18-SEP-11
    
    
    
    SQL> alter index scott.pk_emp rebuild;
    
    Index altered.
    
    SQL>  select object_name, object_type, last_ddl_time, created from dba_objects 
      2  where object_name='PK_EMP';
    
    OBJECT_NAME
    --------------------------------------------------------------------------------
    OBJECT_TYPE         LAST_DDL_ CREATED
    ------------------- --------- ---------
    PK_EMP
    INDEX               19-JAN-13 18-SEP-11
    
    
    SQL> 
    Now, the question for you is what why you want to know this? What's the actual issue that you are trying to resolve by knowing this date of last rebuild of index?

    Aman....
  • 2. Re: how to find last index rebuild date
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    http://www.orafaq.com/maillist/oracle-l/2001/01/03/0850.htm

    The Solution provide by aman , please mark this thread as answered.
  • 3. Re: how to find last index rebuild date
    JohnWatson Guru
    Currently Being Moderated
    Osama_mustafa wrote:
    The Solution provide by aman , please mark this thread as answered.
    I wouldn't say it was answered. The LAST_DDL_TIME column is exactly that:
    orcl> select object_name, object_type, last_ddl_time, created from user_objects
      2  where object_name='PK_EMP';
    
    OBJECT_NAME          OBJECT_TYPE         LAST_DDL_TIME       CREATED
    -------------------- ------------------- ------------------- -------------------
    PK_EMP               INDEX               2013-01-15 16:56:01 2013-01-11 16:41:54
    
    orcl> alter index pk_emp allocate extent;
    
    Index altered.
    
    orcl> select object_name, object_type, last_ddl_time, created from user_objects
      2  where object_name='PK_EMP';
    
    OBJECT_NAME          OBJECT_TYPE         LAST_DDL_TIME       CREATED
    -------------------- ------------------- ------------------- -------------------
    PK_EMP               INDEX               2013-01-19 11:51:26 2013-01-11 16:41:54
    
    orcl>
  • 4. Re: how to find last index rebuild date
    JohnWatson Guru
    Currently Being Moderated
    Sused it. ind$.spare6 is updated by a rebuild, but perhaps not by other ddl. I have tested only the one, you must test all other DDLs yourself:
    orcl> select object_id from user_objects where object_name='PK_EMP';
    
     OBJECT_ID
    ----------
         77163
    
    orcl> select spare6 from sys.ind$ where obj#=77163;
    
    SPARE6
    -------------------
    2013-01-19 11:58:15
    
    orcl> alter index pk_emp rebuild;
    
    Index altered.
    
    orcl> select spare6 from sys.ind$ where obj#=77163;
    
    SPARE6
    -------------------
    2013-01-19 12:01:02
    
    orcl> alter index pk_emp allocate extent;
    
    Index altered.
    
    orcl> select spare6 from sys.ind$ where obj#=77163;
    
    SPARE6
    -------------------
    2013-01-19 12:01:02
    
    orcl>
  • 5. Re: how to find last index rebuild date
    Aman.... Oracle ACE
    Currently Being Moderated
    @Osama,
    Thanks for posting the link. I guess, I wasn't think something really silly :) .

    @John,
    I know it wasn't an accurate answer . It was just that I thought instantly , tried and posted.

    Regards
    Aman....
  • 6. Re: how to find last index rebuild date
    sb92075 Guru
    Currently Being Moderated
    Pravin wrote:
    Hi,

    How to find last index rebuild date in oracle 9i database.
    DBMS_LOGMNR
  • 7. Re: how to find last index rebuild date
    JohnWatson Guru
    Currently Being Moderated
    Aman.... wrote:
    @Osama,
    Thanks for posting the link. I guess, I wasn't think something really silly :) .

    @John,
    I know it wasn't an accurate answer . It was just that I thought instantly , tried and posted.

    Regards
    Aman....
    Actually, Aman, I don't know why either of us bothered. I've looked at Pravin's previous questions, and he doesn't mark them as answered or even say thank you. So there isn't any point in trying, I won;t in future.
  • 8. Re: how to find last index rebuild date
    JohnWatson Guru
    Currently Being Moderated
    sb92075 wrote:
    Pravin wrote:
    Hi,

    How to find last index rebuild date in oracle 9i database.
    DBMS_LOGMNR
    I frequently see advice to use the Log Miner on this forum. In this case, I suppose one would have to mine redo going back over months, to find every occurance of an index rebuild. I can't imagine that you are seriously suggesting this, SB.
    It is the same with all the other people who so quickly advise it. How many people have ever tried to use the Log Miner, when you have no idea when the event in which you are interested occured? I haven't. Just restoring all the gigabytes of archivelogs would be a mission, never mind actually doing hte mining.
  • 9. Re: how to find last index rebuild date
    Girish Sharma Guru
    Currently Being Moderated
    How to find last index rebuild date in oracle 9i database.
    What about auditing? If auditing is enabled then I think dba_audit_trail will give you when last alter index <index name> rebuild issued with additional info like who etc.

    Regards
    Girish Sharma
  • 10. Re: how to find last index rebuild date
    Aman.... Oracle ACE
    Currently Being Moderated
    Whatever it may be, auditing, logminer etc etc, the important question is why ? Why to bother about such date in the first place? All I can think of is that when such date is asked for , that means one is looking forward to schedule a job on a periodic basis to rebuild the index. I may be wrong but I can't think of any use for the date of the rebuilding of the index myself. If I would be really looking for, probably it would be the size of the index that I would keep an eye over that it's not going to become exceptionally bigger. About the date, I am not so sure!

    Aman....
  • 11. Re: how to find last index rebuild date
    Aman.... Oracle ACE
    Currently Being Moderated
    I know it's not very encouraging not to hear anything from the OP but let's just do our part which is to try to help and share whatever we know to clear the doubts and to provide the answer in the best possible ways and leave the rest on the OP.

    Regards
    Aman....
  • 12. Re: how to find last index rebuild date
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Jonathan Lewis Blog
    https://jonathanlewis.wordpress.com/2012/09/04/online-rebuild-2/#more-8877

    When to rebuild index ? useful notes, recommend NOT rebuilding indexes except when you can prove that there are tangible benefits.

    http://www.dbforums.com/oracle/1609607-last-time-indexes-were-rebuilt.html

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points