Database File Information by File ID (dfileid.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

The dfileid.sql script retrieves information about Oracle data files using either file ID numbers or filename patterns. It provides essential file properties including tablespace assignment, size, status, and autoextend settings. This script is simpler than dfilef.sql as it focuses only on data files (not temp files) and doesn’t include increment calculations.

The Script

set lines 132

col tablespace_name format a15 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 status format a9 heading 'STATUS'
col file_name format a45 heading 'FILE NAME'
col autoextensible form a4 heading 'AUTO'

select
    tablespace_name,
    file_id,
    relative_fno,
    bytes,
    blocks,
    status,
    decode(autoextensible, 'NO', null, autoextensible) autoextensible,
    file_name
  from sys.dba_data_files
  where file_id like nvl('&id','%')
    and file_name like nvl('&file_name','%')
 order by tablespace_name, file_id
/

Usage

Run the script and provide input when prompted:

SQL> @dfileid.sql
Enter value for id: 4
Enter value for file_name:

-- Or search by filename pattern
Enter value for id:
Enter value for file_name: %users%

-- Or leave both empty to see all data files
Enter value for id:
Enter value for file_name:

Parameters:

• id: File ID number (exact match or % for all)
• file_name: Filename pattern (supports SQL wildcards % and _)

Required Privileges

• SELECT privilege on DBA_DATA_FILES (usually requires DBA role)

Sample Output

TABLESPACE      ID REL FILE NO       BYTES   BLOCKS STATUS    AUTO FILE NAME
--------------- ---- ----------- ----------- -------- --------- ---- ---------------------------------------------
SYSTEM             1           1 800,000,000   97,656 AVAILABLE YES  /u01/oradata/PROD/system01.dbf
SYSAUX             2           2 550,000,000   67,139 AVAILABLE YES  /u01/oradata/PROD/sysaux01.dbf
UNDOTBS1           3           3 200,000,000   24,414 AVAILABLE YES  /u01/oradata/PROD/undotbs01.dbf
USERS              4           4 100,000,000   12,207 AVAILABLE YES  /u01/oradata/PROD/users01.dbf
USERS              5           5  50,000,000    6,103 AVAILABLE      /u01/oradata/PROD/users02.dbf

Key Output Columns

• TABLESPACE: Name of the tablespace containing the file
• ID: File ID number (unique across 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 or blank for fixed size)
• FILE NAME: Full path and filename

Common Use Cases

1. File ID Lookup

   • Find specific file details by file ID from error messages
   • Verify file properties for backup and recovery operations

2. Tablespace File Analysis

   • List all files belonging to a specific tablespace
   • Analyze file distribution across tablespaces

3. File Status Verification

   • Check if files are online and available
   • Identify offline or problematic files

4. Size and Growth Analysis

   • Review current file sizes
   • Identify files with or without autoextend enabled

Understanding File Properties

File ID vs Relative File Number

• File ID: Unique across entire database
• Relative File Number: Unique within each tablespace
• Both Required: For certain RMAN operations and recovery scenarios

File Status Interpretation

• AVAILABLE: File is online and accessible for read/write
• OFFLINE: File is offline (manual intervention required)
• INVALID: File header corruption or inconsistency
• RECOVER: File requires media recovery

Autoextend Behavior

• AUTO = YES: File grows automatically when space needed
• AUTO = blank: Fixed size file (manual resize required)

Query Patterns

Find Specific File

-- By file ID (from error messages)
Enter value for id: 4
Enter value for file_name:

-- By exact filename
Enter value for id:
Enter value for file_name: /u01/oradata/PROD/users01.dbf

Find Files by Pattern

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

-- All files for a tablespace (by naming convention)
Enter value for id:
Enter value for file_name: %users%

Administrative Queries

-- All files (comprehensive view)
Enter value for id: %
Enter value for file_name: %

-- Range of file IDs
Enter value for id: [1-9]
Enter value for file_name:

Performance Analysis

File Size Considerations

• Large Files (>10GB): May impact backup/recovery times
• Many Small Files: Could indicate fragmentation issues
• Uneven Sizes: May suggest manual intervention needed

File Distribution

• Single Tablespace/Multiple Files: Good for I/O parallelism
• One File/Tablespace: May limit performance scalability
• File System Spread: Important for I/O distribution

Troubleshooting Guide

File-Related Errors

Oracle error messages often include file IDs:

ORA-01110: data file 4: '/u01/oradata/PROD/users01.dbf'

Use this script to get complete file details for the specified file ID.

File Status Issues

• OFFLINE: Use ALTER DATABASE DATAFILE n ONLINE;
• INVALID: May require media recovery
• Missing Files: Check file system and restore if necessary

Autoextend Problems

• No AUTO setting: File may fill up causing errors
• AUTO without maxsize: File could consume all disk space
• Filesystem full: Autoextend fails silently

Related Scripts

• dfilef.sql - File information with filename search and temp files
• dtspace.sql - Tablespace usage with file details
• vdbfile.sql - Database file overview

Script Features

• Dual Parameter Support: Search by file ID or filename pattern
• Clean Output: Focused on essential file properties
• Data Files Only: Simplified view excluding temp files
• Wildcard Support: Flexible pattern matching for both parameters

Best Practices

1. File ID Documentation: Maintain mapping of file IDs to business functions
2. Regular Monitoring: Check file status and growth regularly
3. Standardized Naming: Use consistent file naming conventions
4. Autoextend Settings: Configure appropriate autoextend parameters

Comparison with Related Scripts

vs. dfilef.sql

• dfileid.sql: Simpler, data files only, file ID search capability
• dfilef.sql: More comprehensive, includes temp files, increment calculations

vs. dtspace.sql

• dfileid.sql: File-focused view with detailed file properties
• dtspace.sql: Tablespace-focused view with space utilization

Common Error Scenarios

File Not Found in Output

1. Check file ID range: Ensure file ID exists in database
2. Verify filename pattern: Check spelling and wildcards
3. Temp files: This script only shows data files, not temp files

Permission Issues

• Ensure user has SELECT privilege on DBA_DATA_FILES
• Consider using ALL_DATA_FILES for limited access environments
• May need specific grants for data file views

Limitations

• Data files only (use dfilef.sql for temp files)
• No historical information (current status only)
• No file system validation (shows data dictionary information only)
• No redo log files (use V$LOGFILE for redo log information)