In the view V$DATAFILE, there are two columns that are updated when an unrecoverable operation is run in the database and modified the specific datafile.
The columns are:
* UNRECOVERABLE_CHANGE# – Change number at point unrecoverable change completes.
* UNRECOVERABLE_TIME – Timestamp at point unrecoverable change completes.
Monitoring these columns is useful if you are running physical standby database in your environment and can give you a heads up on any files that may need to be copied from the primary database.
This is also useful, since a number of bugs in Oracle partitioning cause indexes to be rebuilt in nologging mode, even when the system is supposed to be logging.
Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0