Time when Index was last rebuilt - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: Time when Index was last rebuilt

  1. #11
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Yeah but was it already rebuild today? Stick a TO_CHAR around the last_ddl_time

    select object_name, object_type, to_char(last_ddl_time, 'DD/MM/YYYY HH24:MI:SS')
    from user_objects where object_name= 'TEST_INDEX';

    TEST_INDEX,INDEX,06/06/2003 17:17:00

    alter index TEST_INDEX rebuild;

    select object_name, object_type, to_char(last_ddl_time, 'DD/MM/YYYY HH24:MI:SS')
    from user_objects where object_name= 'TEST_INDEX';

    TEST_INDEX,INDEX,30/07/2003 17:18:18
    OCP 8i, 9i DBA
    Brisbane Australia

  2. #12
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Originally posted by grjohnson:
    Yeah but was it already rebuild today? Stick a TO_CHAR around the last_ddl_time
    No Johnson. I guess its a different version of Oracle we are using. You are on 8i, I guess while I am ancient(8).

    SQL> select object_name, object_type, to_char(last_ddl_time, 'DD/MM/YYYY HH24:MI
    :SS')
    fr 2 om user_objects where object_name= 'MCF_EXCEPTION_LOOKUP_1';

    OBJECT_NAME OBJECT_TYPE TO_CHAR(LAST_DDL_TI
    ------------------------------ --------------- -------------------
    MCF_EXCEPTION_LOOKUP_1 INDEX 21/02/2003 16:18:30

    SQL> alter index MCF_EXCEPTION_LOOKUP_1 rebuild;

    Index altered.

    SQL> select object_name, object_type, to_char(last_ddl_time, 'DD/MM/YYYY HH24:M
    I:SS') from user_objects where object_name= 'MCF_EXCEPTION_LOOKUP_1';

    OBJECT_NAME OBJECT_TYPE TO_CHAR(LAST_DDL_TI
    ------------------------------ --------------- -------------------
    MCF_EXCEPTION_LOOKUP_1 INDEX 21/02/2003 16:18:30


    Raminder

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #13
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Yes, it's a version thing. I had the same problem on 8.1.5 (which was a bug) and was eventually fixed in 8.1.7.x.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #14
    Join Date
    Jul 2003
    Posts
    323

    Question index rebuild!

    What tomaz said seems to be right - if you query dba_objects.last_ddl_time u can get it(last idx rebuild after running an "alter idx_name rebuild" - but if any other ddl has occurred since it may overwrite)!

    last_analyzed is updated only when you ANALYZE a table - the table, index, cluster gets analyzed and the time is logged as last_analyzed
    in user|dba_tables !

    Any other ideas ?

  5. #15
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Originally posted by slimdave
    ... and when the table is moved.

    It used to be the case that making any column nullable or non-nullable would make all the bitmap indexes unusable, so there's another one.
    Moving has been mentioned before.

    Bottom line, it looks like we are rebuilding our indexes all the time.
    This sounds familiar... index rebuilding... Windows rebooting...

    Raminder: when you will get your database upgraded, you will be able to put a trigger on DDL - enabling you to get 100% rebuild history
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  6. #16
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    ... and when the table is moved.

    It used to be the case that making any column nullable or non-nullable would make all the bitmap indexes unusable, so there's another one.
    Yet another, "when you dont need Index on the COL list"
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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