LAST_REBUILD_TIME?

[marist89]

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.

[rsuri]

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