12 Replies Latest reply: Jan 19, 2013 4:24 PM by Osama_Mustafa RSS

    how to find last index rebuild date

    827207
      Hi,

      How to find last index rebuild date in oracle 9i database.
        • 1. Re: how to find last index rebuild date
          Aman....
          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
            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
              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
                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....
                  @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
                    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
                      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
                        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
                          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....
                            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....
                              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
                                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