Find objects that are performing unrecoverable/nologgging option?

Using the steps below one can find the object that is unrecoverable from UNRECOVERABLE_CHANGE# in the v$datafile, one would use this to find the object that is unrecoverable since the last backup.

In this scenario datafile# 5 had unrecoverable/nologging as when we query v$datafile it shows and UNRECOVERABLE_TIME was greater than when the last backup was done.

SQL> column UNRECOVERABLE_CHANGE# format 99999999999999

SQL> select file#, UNRECOVERABLE_CHANGE# from v$datafile where file# = 5;

FILE# UNRECOVERABLE_CHANGE#
———- ———————
5 37640948153

– find the archive log that contains the sequence#
SQL> select sequence#, name from v$archived_log where 37640948153 >= first_change# and 37640948153 < next_change#;

SEQUENCE# NAME
———- ——————————————————————————–
2108 /u01/TESTDB/arch/arch_1_2108.arc

– This will create file in user_dump
SQL> alter system dump logfile '/u01/TESTDB/arch/arch_1_2108.arc' layer 19;

In 8i the object id is not included but in the 10g trace file the object id is included in the trace file
/u01/admin/TESTDB/udump $ grep DBA ora_168168_testdb.trc

…
CHANGE #1 INVLD AFN:5 DBA:0x01401e0a BLKS:0×0020 SCN:0×0008.c39349b9 SEQ: 1 OP:19.2
…

# to find the file# and block
SET SERVEROUTPUT ON
DECLARE
l_dba NUMBER := TO_NUMBER (’01401e0a’,'XXXXXXXX’);
l_file NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (l_dba);
l_block NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (l_dba);
BEGIN
DBMS_OUTPUT.PUT_LINE (‘File : ‘||l_file);
DBMS_OUTPUT.PUT_LINE (‘Block : ‘||l_block);
END;
/

Output:
File : 5
Block : 7690

SQL> SELECT owner, segment_name FROM dba_extents where file_id = 5 and block_id = 7690;

OWNER SEGMENT_NAME
—————————— —————-
SCOTT TEST_TABLE