DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Time when Index was last rebuilt

  1. #1
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547

    Time when Index was last rebuilt

    Hi

    I was wondering if there is any way to know when was the last time an index was rebuilt in the database (except for using log-miner).

    Similarly, when was the last time a table was analysed?

    Thanks

    Raminder

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    look in user_indexes and user_tables for the last_analyzed time

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Code:
           SELECT last_analyzed 
           FROM dba_tables
           WHERE UPPER(OWNER) LIKE '%XYZ%'
             AND UPPER(TABLE_NAME) LIKE '%ABC%';
    You only would want to rebuild the indices when they are fragmented.



    -Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Thanks davey and Sambavan.

    Is there a way to know when were the indexes last rebuilt?

    Raminder

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  5. #5
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Rebuild gets timestamped in ???_objects.last_ddl_time.
    But it will be overwritten by other DDL on index.
    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. #6
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Originally posted by sambavan

    You only would want to rebuild the indices when they are fragmented.

    -Sam
    ...and if moving them to a different tablespace.
    OCP 8i, 9i DBA
    Brisbane Australia

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by grjohnson
    ...and if moving them to a different tablespace.
    ..and if you made them unusable to load data faster, then rebuilt them after the load was complete.
    Any more?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Originally posted by slimdave
    ..and if you made them unusable to load data faster, then rebuilt them after the load was complete.
    Any more?


    ... and after alter table exchange partition ... excluding indexes.
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    ... 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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Originally posted by TomazZ:
    Rebuild gets timestamped in ???_objects.last_ddl_time
    I am afraid that's not true:

    SQL> select object_name, object_type, last_ddl_time from user_objects where object_name= 'MCF_EXCEPTION_LOOKUP_1';

    OBJECT_NAME OBJECT_TYPE LAST_DDL
    ------------------------------ --------------- --------
    MCF_EXCEPTION_LOOKUP_1 INDEX 03-02-21

    SQL> alter index MCF_EXCEPTION_LOOKUP_1 rebuild;

    Index altered.

    SQL> select object_name, object_type, last_ddl_time from user_objects where obj
    ect_name= 'MCF_EXCEPTION_LOOKUP_1';

    OBJECT_NAME OBJECT_TYPE LAST_DDL
    ------------------------------ --------------- --------
    MCF_EXCEPTION_LOOKUP_1 INDEX 03-02-21

    My envronment is Oracle 8.0.5.2.1, though.

    Regards
    Last edited by Raminder; 07-30-2003 at 11:40 AM.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

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