v$datafile_header tips

Question: What is the v$datafile_header view and how is it different from the v$datafile view?

Answer:  The v$ views are actually structures stored in RAM.  The v$datafileview contains details about each database, but the v$datafile_header contains important space management information that applies to all data files within the header:

Space used  - basic space management information is retailed inv$datafile_header, such as "blocks" and "bytes" allocated.  The blocks column is especially important to the SQL optimizer when choosing index access over full-scan access.

Consistency - The "fuzzy": column will always read "NO" unless the data file is not consistent, then the fuzzy column will read 'YES'.  An inconsistent file in v$datafile_header indicates a file in need of recovery (roll-forward).

Tablespace - While a tablespace may contain many data files, a data file belong to one, and only one, tablespace.  Also note that the datafile header shows the associated tablespace n umber because a data file may belong to only one tablespace.

System change numbers - The last_dealloc_scn column helps keep track of the database consistency as the system change number (SCN) is kept within each data file.

Information about the datafile header can be gained through v$datafile_header.

SQL> desc v$datafile_header;

Name                Type
------------------- ----------------------
FILE#               NUMBER
STATUS              VARCHAR2(7)
ERROR               VARCHAR2(18)
FORMAT              NUMBER
RECOVER             VARCHAR2(3)
FUZZY               VARCHAR2(3)
CREATION_CHANGE#    NUMBER
CREATION_TIME       DATE
TABLESPACE_NAME     VARCHAR2(30)
TS#                 NUMBER
RFILE#              NUMBER
RESETLOGS_CHANGE#   NUMBER
RESETLOGS_TIME      DATE
CHECKPOINT_CHANGE#  NUMBER
CHECKPOINT_TIME     DATE
CHECKPOINT_COUNT    NUMBER
BYTES               NUMBER
BLOCKS              NUMBER
NAME                VARCHAR2(513)
SPACE_HEADER        VARCHAR2(40)
LAST_DEALLOC_SCN    VARCHAR2(16)

---

Here are some sample example queries using v$datafile_header:
-- display file numbers and names from v$datafile_header
select 
   file#, 
   name 
from 
   v$datafile_header 
order by 1;


-- Display all file numbers and names
select 
    file#, 
   nvl(name,'File Not Found') 
from 
   v$datafile_header 
order by 1;

====================================================================================