SQL statements to check status of datafile headers

Query the V$DATAFILE_HEADER view to determine the status of your datafiles. Run the following SQL statements to check the datafile headers:

COL FILE# FORMAT 999
COL STATUS FORMAT A7
COL ERROR FORMAT A10
COL TABLESPACE_NAME FORMAT A10
COL NAME FORMAT A30

SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
FROM V$DATAFILE_HEADER
WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);

You can also queryV$RECOVER_FILE to list datafiles requiring recovery by datafile number with their status and error information.

SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME
FROM V$RECOVER_FILE;


To find datafile and tablespace names, you can also perform useful joins using the datafile number and the V$DATAFILE and V$TABLESPACE views. For example:

COL DF# FORMAT 999
COL DF_NAME FORMAT A35
COL TBSP_NAME FORMAT A7
COL STATUS FORMAT A7
COL ERROR FORMAT A10
COL CHANGE# FORMAT 99999999
SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name,
d.STATUS, r.ERROR, r.CHANGE#, r.TIME
FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
WHERE t.TS# = d.TS#
AND d.FILE# = r.FILE#
;



reference: Oracle's Document

== TaTsHuYa ==