Skip to content
Oracle DBA Scripts & Database Utilities

Database File Information (vdbfile.sql)

What This Script Does

Section titled “What This Script Does”

This script provides essential information about all database datafiles including their status, checkpoint information, and file paths. It’s useful for database administration tasks, troubleshooting file issues, and understanding the current state of database files.

Script

Section titled “Script”
rem  vdbfile.sql
rem
ttitle 'Database File Definitions'
rem
col file# format 999 heading 'ID'
col status format a7 heading 'STATUS'
col enabled format a10 heading 'ENABLED'
col checkpoint_change# format 9999999999999 heading 'CHECKPOINT|CHANGE'
col kbytes format 999999999 heading 'SIZE (KB)'
col name format a30 heading 'NAME'
rem
break on report
compute sum of kbytes on report
rem
select file#,
       status,
       enabled,
       checkpoint_change#,
       bytes/1024 kbytes,
       name
  from v$datafile;

Usage

Section titled “Usage”
-- Run the script in SQL*Plus or SQLcl
@vdbfile.sql


-- No parameters required
-- Shows current state of all datafiles

Required Privileges

Section titled “Required Privileges”
  • SELECT on V$DATAFILE
  • Generally available to most database users

Sample Output

Section titled “Sample Output”
Database File Definitions


 ID STATUS  ENABLED          CHECKPOINT   SIZE (KB) NAME
                              CHANGE
--- ------- ---------- --------------- ---------- ------------------------------
  1 ONLINE  READ WRITE      1234567890    1048576 /u01/oradata/PROD/system01.dbf
  2 ONLINE  READ WRITE      1234567890     524288 /u01/oradata/PROD/sysaux01.dbf
  3 ONLINE  READ WRITE      1234567890    2097152 /u01/oradata/PROD/users01.dbf
  4 ONLINE  READ WRITE      1234567890     262144 /u01/oradata/PROD/undotbs01.dbf
  5 ONLINE  READ WRITE      1234567890    1048576 /u01/oradata/PROD/temp01.dbf
  6 ONLINE  READ WRITE      1234567890    5242880 /u01/oradata/PROD/data01.dbf
                                        ----------
sum                                      10223616

Key Output Columns

Section titled “Key Output Columns”
  • ID: Datafile number (file#)
  • STATUS: File status (ONLINE, OFFLINE, RECOVER)
  • ENABLED: File access mode (READ WRITE, READ ONLY)
  • CHECKPOINT CHANGE: Last checkpoint SCN for this file
  • SIZE (KB): File size in kilobytes
  • NAME: Full path to the datafile

Understanding File Status

Section titled “Understanding File Status”

File Status Values

Section titled “File Status Values”
  • ONLINE: File is available and accessible
  • OFFLINE: File is offline and needs recovery
  • RECOVER: File needs media recovery
  • UNKNOWN: File status cannot be determined

Enabled Values

Section titled “Enabled Values”
  • READ WRITE: Normal operational mode
  • READ ONLY: File is read-only (typically for read-only tablespaces)
  • DISABLED: File is disabled

Checkpoint Change Numbers

Section titled “Checkpoint Change Numbers”
  • Same SCN across files: Normal synchronized state
  • Different SCNs: May indicate recovery needed or file offline
  • Very old SCN: File may need recovery

Performance Analysis

Section titled “Performance Analysis”

File Size Distribution

Section titled “File Size Distribution”
  • Large files: May impact backup/recovery times
  • Small files: May cause excessive file count
  • Uneven sizes: Consider file management strategy

Status Monitoring

Section titled “Status Monitoring”
  • All ONLINE: Normal healthy state
  • OFFLINE files: Require immediate attention
  • RECOVER status: Media recovery needed

Common Use Cases

Section titled “Common Use Cases”

Health Check

Section titled “Health Check”
-- Quick database file health check
@vdbfile.sql
-- Verify all files are ONLINE and READ WRITE

Backup Planning

Section titled “Backup Planning”
-- Analyze file sizes for backup strategy
@vdbfile.sql
-- Calculate total database size

Recovery Assessment

Section titled “Recovery Assessment”
-- During recovery operations
@vdbfile.sql
-- Check file status and checkpoint information

Capacity Planning

Section titled “Capacity Planning”
-- Monitor file growth
@vdbfile.sql
-- Plan storage allocation

Troubleshooting File Issues

Section titled “Troubleshooting File Issues”

Offline Files

Section titled “Offline Files”
-- If files show OFFLINE status:
-- 1. Check if tablespace is offline
SELECT tablespace_name, status
FROM dba_tablespaces;


-- 2. Check for missing files
SELECT file_name, status
FROM dba_data_files
WHERE status != 'AVAILABLE';


-- 3. Bring file online if possible
ALTER DATABASE DATAFILE '/path/to/file' ONLINE;

Recovery Needed

Section titled “Recovery Needed”
-- If files need recovery:
-- 1. Check recovery requirements
SELECT file#, status, error
FROM v$datafile_header;


-- 2. Perform recovery
RECOVER DATAFILE '/path/to/file';
ALTER DATABASE DATAFILE '/path/to/file' ONLINE;

File Path Issues

Section titled “File Path Issues”
-- If files are missing or paths changed:
-- 1. Check actual file locations
-- 2. Use RMAN to restore/rename if needed
RMAN> RESTORE DATAFILE '/old/path' TO '/new/path';


-- 3. Update controlfile
ALTER DATABASE RENAME FILE '/old/path' TO '/new/path';

Advanced Analysis

Section titled “Advanced Analysis”

File Header Information

Section titled “File Header Information”
-- More detailed file header analysis
SELECT file#, status, error, recover, fuzzy,
       creation_change#, creation_time,
       checkpoint_change#, checkpoint_time
FROM v$datafile_header;

Tablespace Mapping

Section titled “Tablespace Mapping”
-- Map files to tablespaces
SELECT df.file_id, df.file_name,
       df.tablespace_name, df.status,
       vf.status as v_status
FROM dba_data_files df, v$datafile vf
WHERE df.file_id = vf.file#
ORDER BY df.file_id;

File I/O Statistics

Section titled “File I/O Statistics”
-- Combine with I/O statistics
SELECT vf.file#, vf.name,
       fs.phyblkrd, fs.phyblkwrt,
       fs.readtim, fs.writetim
FROM v$datafile vf, v$filestat fs
WHERE vf.file# = fs.file#
ORDER BY fs.phyblkrd DESC;
Section titled “Related Scripts”