How to find if there are datafiles that have objects that are not recoverable since last backup?

Using the following query one can find the datafiles that had nologging turned on, direct load or unrecoverable option was used on a datafile since the last successful backup.

SELECT df.name, to_char(df.unrecoverable_time, ‘DD-MON-YYYY HH24:MI:SS’) unrecover_time
FROM v$datafile df,
(SELECT bd.file#, max(completion_time) completion_time
FROM v$backup_datafile bd GROUP BY bd.file#) bd
WHERE bd.file# (+) = df.file#
AND df.unrecoverable_time > bd.completion_time;

If it returns no rows it means no unrecoverable/nologging options were used since the last backup.

NAME UNRECOVER_TIME
—————————————————————- ———————-
/u01/oradata/TESTDB/INDX_01.dbf 27-APR-2009 13:31:52
/u03/oradata/TESTDB/DATA_01.dbf 27-APR-2009 03:52:59