Does anybody know of a query where I can find out the last time an index was rebuilt? I tried last_ddl_time from dba_objects, but it doesn't reflect when the index was rebuilt.
Printable View
Does anybody know of a query where I can find out the last time an index was rebuilt? I tried last_ddl_time from dba_objects, but it doesn't reflect when the index was rebuilt.
okay I spent way too much scanning the dictionary to find anything but to no avail and then I turned to Metalink and I found the exact same problem listed there (of-course if you haven't checked that already)
- Rajeev
****************************
Subject: How can I tell the last time an index was rebuilt?
How can I tell the last time an index was rebuilt?
I've been using the "alter index.....rebuild....." command to improve performance on my four HP/UX
10.20 7.3.4.3.1 instances.
It would be usefull to know the last time this command was successfully executed for a particular
index. I thought the LAST_DDL_TIME in DBA_OBJECTS would give me that information, but the field
seems unaffected by this or any other "alter index....." commands.
So my question is "How can I know the last time an "alter index.....rebuild....." statement was
successfully executed for an index?"
-------------------------------------------------------------------------------
From: Oracle, Ken Robinson 09-Oct-99 23:05
Subject: Re : How can I tell the last time an index was rebuilt?
The LAST_DDL_TIME should be updated with an index rebuild. Per bug 761673, development acknowledged this needs to be fixed, but there is no mention of a time/release level this is expected to be available.
At this time there is not a way to get this information (development also noted that the underlying dictionary table obj$ doesn't get updated with a timestamp for an ALTER INDEX command).
Ken
--------------------------------------------------------------------------------
From: Casey Dyke 10-Oct-99 01:20
Subject: Re : Re : How can I tell the last time an index was rebuilt?
I believe last_ddl_time can be effected by such things as grants and so on - making relying on it somewhat questionable (depending on when you check it).
If your rebuilds are frequent and the need to check them important - perhaps a generic stored procedure/package for the rebuild that included some form of auditing (ie: insert row into last_ddl_tbl including object, date/time, perhaps even time to rebuild ...). Let the procedure implicitly handle your logging needs and not only do you have your ref check - you've got some history as well. Obviously thats a bit of work that would be better handled by the Oracle kernel - but if the need for checking is important - that sort of structure can be whipped together pretty quickly.
Cheers,
Casey ...
--------------------------------------------------------------------------------
From: Scott Dawley 11-Oct-99 16:57
Subject: Re : How can I tell the last time an index was rebuilt?
I do all my index rebuilds through a home-grown
script. As part of that script, I log the timestamp
and the index rebuild script to a logfile.
Scott Dawley
Wooded Hills Consulting, Inc.
(414) 899-6127