Database File Analysis by Tablespace (dfile.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

This script provides comprehensive database file analysis by:

• Displaying both data files and temporary files in a unified view
• Organizing output by tablespace for easy analysis
• Showing file sizing, autoextend configuration, and status
• Converting autoextend increments to KB for readability
• Supporting wildcard filtering for specific tablespace analysis

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 maxkbytes format 999,999,999 heading 'MAX|SIZE (K)'
col status format a9 heading 'STATUS'
col file_name format a38 heading 'FILE NAME'
col autoextensible form a1 heading 'A|U|T|O'
rem
define tbspace='&tablespace'
rem
break on tablespace_name skip 1
rem
select
    f.tablespace_name,
    f.file_id,
    f.relative_fno,
    f.bytes,
    f.blocks,
    f.status,
    decode(f.autoextensible, 'NO', null, 'Y') autoextensible,
    (f.increment_by * t.block_size)/1024 increment_by,
    f.maxbytes/1024 maxkbytes,
    f.file_name
  from dba_tablespaces t, sys.dba_data_files f
  where f.tablespace_name like nvl(upper('&tbspace'),'%')
    and t.tablespace_name = f.tablespace_name
union all
select
    f.tablespace_name,
    f.file_id,
    f.relative_fno,
    f.bytes,
    f.blocks,
    f.status,
    decode(f.autoextensible, 'NO', null, 'Y') autoextensible,
    (f.increment_by * t.block_size)/1024 increment_by,
    f.maxbytes/1024 maxkbytes,
    f.file_name
  from dba_tablespaces t, sys.dba_temp_files f
  where f.tablespace_name like nvl(upper('&tbspace'),'%')
    and t.tablespace_name = f.tablespace_name
 order by tablespace_name, file_id
/
undefine tbspace

Usage

SQL> @dfile.sql
Enter value for tablespace: %

Parameters

• tablespace: Tablespace name pattern (% for all tablespaces, specific name for single tablespace)

Required Privileges

• SELECT on DBA_TABLESPACES
• SELECT on SYS.DBA_DATA_FILES
• SELECT on SYS.DBA_TEMP_FILES

Sample Output

TABLESPACE       ID REL    BYTES          BLOCKS STATUS    A INCREMENT  MAX SIZE FILE NAME
                    FILE NO                                U BY (K)    (K)
------------ ----- --- --------------- --------- --------- - --------- --------- --------------------------------------
SYSTEM           1   1   2,147,483,648   262,144 AVAILABLE Y   131,072 2,097,151 /u01/app/oracle/oradata/orcl/system01.dbf

SYSAUX           2   2   1,073,741,824   131,072 AVAILABLE Y    65,536 2,097,151 /u01/app/oracle/oradata/orcl/sysaux01.dbf

UNDOTBS1         3   3     536,870,912    65,536 AVAILABLE Y    32,768 2,097,151 /u01/app/oracle/oradata/orcl/undotbs01.dbf

USERS            4   4     104,857,600    12,800 AVAILABLE Y     6,400   102,400 /u01/app/oracle/oradata/orcl/users01.dbf
                 5   5     104,857,600    12,800 AVAILABLE         0         0 /u01/app/oracle/oradata/orcl/users02.dbf

TEMP             1   1   1,073,741,824   131,072 AVAILABLE Y    65,536 2,097,151 /u01/app/oracle/oradata/orcl/temp01.dbf

SALES_DATA       6   6   5,368,709,120   655,360 AVAILABLE Y   262,144 10,485,760 /u02/app/oracle/oradata/orcl/sales01.dbf
                 7   7   5,368,709,120   655,360 AVAILABLE Y   262,144 10,485,760 /u02/app/oracle/oradata/orcl/sales02.dbf

Key Output Columns

File Identification

• TABLESPACE: Tablespace name containing the file
• ID: File ID (unique across database)
• REL FILE NO: Relative file number within tablespace
• FILE NAME: Full path to the database file

Size Information

• BYTES: Current file size in bytes
• BLOCKS: Current file size in Oracle blocks
• MAX SIZE (K): Maximum autoextend size in KB
• INCREMENT BY (K): Autoextend increment size in KB

Configuration

• STATUS: File status (AVAILABLE, OFFLINE, INVALID)
• AUTO: Autoextend enabled indicator (Y = Yes, blank = No)

Understanding File Types

Data Files (from DBA_DATA_FILES)

• Purpose: Store user data, indexes, and system data
• Tablespaces: SYSTEM, SYSAUX, USERS, application tablespaces
• Management: Can be resized, moved, added to tablespaces

Temporary Files (from DBA_TEMP_FILES)

• Purpose: Store temporary data for sorting, hashing, temporary tables
• Tablespaces: TEMP and additional temporary tablespaces
• Management: Different management commands than data files

File Status Analysis

Status Values

• AVAILABLE: File is online and accessible
• OFFLINE: File is offline (data files only)
• INVALID: File has problems and needs attention
• RECOVER: File needs media recovery

Autoextend Configuration

• Y (Autoextend): File will grow automatically when space needed
• Blank (Fixed): File size is fixed, manual resize required
• MAX SIZE: Prevents runaway autoextend growth

Storage Analysis

Space Planning

-- Calculate total and used space by tablespace
SELECT ts.tablespace_name,
       ROUND(SUM(df.bytes)/1024/1024/1024, 2) total_gb,
       ROUND(SUM(df.maxbytes)/1024/1024/1024, 2) max_gb,
       COUNT(*) file_count
FROM dba_tablespaces ts, dba_data_files df
WHERE ts.tablespace_name = df.tablespace_name
GROUP BY ts.tablespace_name
ORDER BY total_gb DESC;

Growth Potential

-- Identify files that can grow
SELECT tablespace_name, file_name,
       ROUND(bytes/1024/1024, 2) current_mb,
       ROUND(maxbytes/1024/1024, 2) max_mb,
       ROUND((maxbytes - bytes)/1024/1024, 2) growth_potential_mb
FROM dba_data_files
WHERE autoextensible = 'YES'
AND maxbytes > bytes
ORDER BY growth_potential_mb DESC;

Common Use Cases

1. Capacity Planning

   • Monitor current file sizes
   • Plan for storage growth
   • Assess autoextend configuration

2. Performance Analysis

   • Identify file distribution across storage
   • Check for balanced file sizes
   • Plan I/O optimization

3. Maintenance Operations

   • Locate files for backup/restore
   • Plan tablespace changes
   • Coordinate with storage team

File Management Operations

Adding New Files

-- Add data file to tablespace
ALTER TABLESPACE sales_data ADD DATAFILE
'/u02/app/oracle/oradata/orcl/sales03.dbf'
SIZE 1G AUTOEXTEND ON NEXT 256M MAXSIZE 10G;

-- Add temporary file
ALTER TABLESPACE temp ADD TEMPFILE
'/u01/app/oracle/oradata/orcl/temp02.dbf'
SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 2G;

Modifying Existing Files

-- Resize file
ALTER DATABASE DATAFILE '/path/to/file.dbf' RESIZE 2G;

-- Modify autoextend
ALTER DATABASE DATAFILE '/path/to/file.dbf'
AUTOEXTEND ON NEXT 256M MAXSIZE 5G;

-- Take file offline (data files only)
ALTER DATABASE DATAFILE '/path/to/file.dbf' OFFLINE;

Monitoring File Growth

Growth Rate Analysis

-- Track file size changes over time
SELECT file_name, tablespace_name,
       ROUND(bytes/1024/1024, 2) current_mb,
       CASE WHEN autoextensible = 'YES'
            THEN 'Auto-extend enabled'
            ELSE 'Fixed size'
       END growth_policy
FROM dba_data_files
WHERE tablespace_name IN ('USERS', 'SALES_DATA')
ORDER BY tablespace_name, file_id;

Space Utilization

-- Check space usage within files
SELECT f.tablespace_name, f.file_name,
       ROUND(f.bytes/1024/1024, 2) total_mb,
       ROUND(NVL(fs.bytes, 0)/1024/1024, 2) free_mb,
       ROUND((f.bytes - NVL(fs.bytes, 0))/f.bytes * 100, 2) pct_used
FROM dba_data_files f
LEFT JOIN (SELECT tablespace_name, SUM(bytes) bytes
           FROM dba_free_space GROUP BY tablespace_name) fs
ON f.tablespace_name = fs.tablespace_name
ORDER BY pct_used DESC;

Storage Best Practices

File Sizing Guidelines

1. Uniform File Sizes: Use similar sizes for files in same tablespace
2. Reasonable Autoextend: Set conservative increments (10-20% of current size)
3. Maximum Limits: Always set MAXSIZE to prevent runaway growth

Distribution Strategy

1. Multiple Files: Distribute I/O across multiple files and disks
2. Separate Storage: Place different tablespaces on different storage tiers
3. Temporary Files: Size appropriately for workload requirements

Troubleshooting

Common Issues

Out of Space Errors

• ORA-01652: Unable to extend temp segment
• Solution: Add files or increase autoextend limits

File Corruption

• ORA-01578: Block corruption detected
• Solution: Use RMAN to restore/recover affected files

Performance Issues

• Uneven File Sizes: Can cause I/O hotspots
• Solution: Balance file sizes across tablespace

Emergency Procedures

-- Quickly add space to critical tablespace
ALTER TABLESPACE users ADD DATAFILE SIZE 1G;

-- Increase existing file size immediately
ALTER DATABASE DATAFILE 4 RESIZE 2G;

-- Enable autoextend on existing file
ALTER DATABASE DATAFILE 4 AUTOEXTEND ON NEXT 100M MAXSIZE 5G;

Integration with Storage Management

ASM Integration

• Files on ASM show as +DISKGROUP/database/datafile/…
• Autoextend behavior managed by ASM
• File balancing handled automatically

File System Considerations

• Monitor underlying file system space
• Coordinate with storage administrators
• Plan for backup storage requirements

Related Scripts

• Tablespace Analysis
• File Information by ID
• Tablespace Space Usage