Database File Information by Filename (dfilef.sql)

What This Script Does

The dfilef.sql script retrieves comprehensive information about Oracle database files based on filename patterns. It searches both data files and temporary files, providing details about file location, size, status, and autoextend properties. This is particularly useful for file system management and troubleshooting file-related issues.

The Script

set lines 132

col tablespace_name format a12 heading 'TABLESPACE'
col file_id format 9999 heading 'ID'
col relative_fno format 9999 heading 'REL|FILE NO'
col bytes format 9,999,999,999,999 heading 'BYTES'
col blocks format 9,999,999 heading 'BLOCKS'
col increment_by format 9,999,999 heading 'INCREMENT|BY (K)'
col status format a9 heading 'STATUS'
col file_name format a45 heading 'FILE NAME'
col autoextensible form a4 heading 'AUTO'
rem
define fname=&file_name
rem
select
    tablespace_name,
    file_id,
    relative_fno,
    bytes,
    blocks,
    status,
    decode(autoextensible, 'NO', null, autoextensible) autoextensible,
    (increment_by * p.value)/1024 increment_by,
    file_name
  from v$parameter p, sys.dba_data_files
  where file_name like '&fname'
    and p.name='db_block_size'
union all
select
    tablespace_name,
    file_id,
    relative_fno,
    bytes,
    blocks,
    status,
    decode(autoextensible, 'NO', null, autoextensible) autoextensible,
    (increment_by * p.value)/1024 increment_by,
    file_name
  from v$parameter p, sys.dba_temp_files
  where file_name like '&fname'
    and p.name='db_block_size'
 order by tablespace_name, file_id
/
undefine fname

Usage

Run the script and provide a filename pattern when prompted:

SQL> @dfilef.sql
Enter value for file_name: /u01/oradata/PROD/system01.dbf

-- Or use wildcard patterns
Enter value for file_name: %system%
Enter value for file_name: /u01/oradata/PROD/%

Parameters:

file_name: Full or partial filename (supports SQL wildcard patterns % and _)

Required Privileges

SELECT privilege on DBA_DATA_FILES, DBA_TEMP_FILES, and V$PARAMETER
Usually requires DBA role or specific grants

Sample Output

TABLESPACE   ID REL FILE NO       BYTES   BLOCKS STATUS    AUTO INCREMENT BY (K) FILE NAME
------------ ---- ----------- ----------- -------- --------- ---- --------------- ---------------------------------------------
SYSTEM          1           1 800,000,000   97,656 AVAILABLE YES            10,240 /u01/oradata/PROD/system01.dbf
SYSAUX          2           2 550,000,000   67,139 AVAILABLE YES             5,120 /u01/oradata/PROD/sysaux01.dbf
UNDOTBS1        3           3 200,000,000   24,414 AVAILABLE YES            10,240 /u01/oradata/PROD/undotbs01.dbf
USERS           4           4 100,000,000   12,207 AVAILABLE YES             1,024 /u01/oradata/PROD/users01.dbf
TEMP            1           1 500,000,000   61,035 AVAILABLE YES            10,240 /u01/oradata/PROD/temp01.dbf

Key Output Columns

TABLESPACE: Name of the tablespace containing the file
ID: File ID number (unique within the database)
REL FILE NO: Relative file number (unique within the tablespace)
BYTES: Current file size in bytes
BLOCKS: Number of Oracle blocks in the file
STATUS: File status (AVAILABLE, OFFLINE, INVALID, etc.)
AUTO: Autoextend setting (YES if file can grow automatically)
INCREMENT BY (K): Autoextend increment size in kilobytes
FILE NAME: Full path and filename

Common Use Cases

File System Management
- Locate specific database files on the file system
- Verify file paths and naming conventions
Space Management
- Check current file sizes and autoextend settings
- Identify files that may need manual resizing
Troubleshooting
- Investigate file-related database errors
- Verify file accessibility and status
Migration Planning
- Document current file locations and sizes
- Plan file movement or reorganization

Understanding File Properties

File Status Values

AVAILABLE: File is online and accessible
OFFLINE: File is offline (not accessible for read/write)
INVALID: File header is corrupted or inconsistent
RECOVER: File needs media recovery

Autoextend Properties

AUTO = YES: File will grow automatically when full
AUTO = blank: File has fixed size (no autoextend)
INCREMENT BY: Size added when autoextend occurs

Relative File Numbers

Unique within tablespace: Used for backup and recovery operations
Important for RMAN: Required for file-specific recovery commands

Filename Pattern Examples

Specific File Search

-- Exact filename match
Enter value for file_name: /u01/oradata/PROD/system01.dbf

Pattern Matching

-- All files in a directory
Enter value for file_name: /u01/oradata/PROD/%

-- All system tablespace files
Enter value for file_name: %system%

-- Files with specific extension
Enter value for file_name: %.dbf

Troubleshooting Scenarios

-- Find files on a specific mount point
Enter value for file_name: /u02/%

-- Locate temp files
Enter value for file_name: %temp%

-- Find files by tablespace name pattern
Enter value for file_name: %USERS%

Performance Considerations

Large File Analysis

BYTES > 10GB: Consider if file sizes are appropriate for workload
Many Small Files: May indicate need for file consolidation
Autoextend Settings: Ensure increment sizes are reasonable

File Distribution

Multiple Mount Points: Good for I/O distribution
Single File System: May create I/O bottlenecks
Temp File Placement: Should be on fast storage

dfileid.sql - Database file information by file ID
dtspace.sql - Tablespace space usage
vdbfile.sql - Database file status overview

Script Features

Unified View: Combines data files and temp files in single output
Wildcard Support: Flexible filename pattern matching
Calculated Metrics: Shows increment sizes in readable format
Sorted Output: Organized by tablespace and file ID

Troubleshooting File Issues

File Not Found

-- Check if file exists in database catalog
SELECT file_name FROM dba_data_files
WHERE file_name LIKE '%filename_pattern%';

File Access Problems

STATUS != AVAILABLE: Investigate file system or permissions issues
Missing from Output: File may not be registered in data dictionary
Wrong Path: Verify file system paths and symbolic links

Autoextend Issues

AUTO = YES but not growing: Check file system space and maxsize settings
Large INCREMENT BY: May cause rapid space consumption
No Autoextend: Files may fill up and cause errors

Best Practices

Regular Monitoring: Check file status and growth patterns regularly
Naming Conventions: Use consistent naming patterns for easier management
Path Documentation: Maintain documentation of file locations
Backup Verification: Ensure all files are included in backup strategies

Limitations

Shows only files registered in the data dictionary
Does not validate actual file system existence
Historical information not available (current status only)
Redo log files not included (use V$LOGFILE for redo logs)