Thanks for the question, Luis Gustavo.
Asked: April 12, 2017 - 7:02 pm UTC
Last updated: April 14, 2017 - 10:05 am UTC
Version: 11g R2
Viewed 1000+ times
You Asked
How can i find the unusable date of indexes, index partitions and index subpartitions , only with sqlplus , not looking in the alert log.
- How can i find the date that the status of an index has became unusable ?
- How can i know the date that the status of an index partition has became unusable ?
- How can i know the date that the status of an index sub-partition has became unusable ?
and Connor said...
Unless you are auditing DDL changes, then you will not be able to find the *date* at which this occurred.
Obviously the *current* status can be found simply by querying
xxx_INDEXES
xxx_IND_PARTITIONS
xxx_IND_SUBPARTITIONS
but if you want to capture the *when*, you'll need some auditing.
If the status is *currently* UNUSABLE, you could use LAST_DDL_TIME on xxx_OBJECTS as a *possible* time of status change.
Is this answer out of date? If it is, please let us know via a Comment