Is it possible to determine when an object within an Oracle database became invalid?
I have tried looking at the DBA_OBJECTS view, but none of its date stamp columns seem to be affected when the STATUS changes to 'INVALID'.
views:
264answers:
3
+1
A:
No. You might be able to estimate this by looking at the timestamps of the related dependent objects (in DBA_DEPENDENCIES) - if the object was invalidated because of dropping another object then you could check the drop time of the parent in the recycle bin. Again, this is detective work and not as straightforward as having the information presented in a view.
dpbradley
2009-09-23 18:22:59
A:
OEM Reports
If you have OEM configured, you can look at your daily objects reports. This is a manual solution, not sure if the data in these reports is queriable or LOB data.
Chad
2009-09-23 18:26:32
+1
A:
Something has to happen to make an object invalid. Usually it's a modification to a referenced object. Turn on database auditing on DDL and you should be able to catch changes to objects that cause other objects to become invalid.
Rob
2009-09-28 14:45:22
objects can become invalid for other reasons than DDL. like rollups expiring and needed to be recalculated, changes to external dependent objects(linked servers), space constraints, etc.
Chad
2009-09-28 19:27:22