Database File Information (dfile.sql)

What This Script Does

This script provides comprehensive information about all data files and temporary files in the database, including sizes, autoextend settings, and file locations.

The Script

set lines 200

col tablespace_name format a22 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,999 heading 'MAX|SIZE (K)'
col status format a9 heading 'STATUS'
col file_name format a60 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

-- Basic usage
@dfile.sql

-- When prompted, enter:
-- tablespace: ** - Tablespace name pattern to filter (use % for all)

Parameters

The script prompts for:

&tablespace - ** - Tablespace name pattern to filter (use % for all)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT ON DBA_TABLESPACES
SELECT ON DBA_DATA_FILES
SELECT ON DBA_TEMP_FILES

Sample Output

                                  REL                           A INCREMENT          MAX
TABLESPACE             ID FILE NO              BYTES     BLOCKS U T   BY (K)     SIZE (K) STATUS    FILE NAME
---------------------- ---- ------- ------------------ ---------- O ---------- ---------------- --------- ------------------------------------------------------------
SYSTEM                    1       1    1,073,741,824    131,072 Y     65,536   34,359,721,984 AVAILABLE /u01/app/oracle/oradata/ORCL/system01.dbf

SYSAUX                    3       3      524,288,000     64,000 Y     65,536   34,359,721,984 AVAILABLE /u01/app/oracle/oradata/ORCL/sysaux01.dbf

UNDOTBS1                  4       4      104,857,600     12,800 Y     65,536   34,359,721,984 AVAILABLE /u01/app/oracle/oradata/ORCL/undotbs01.dbf

USERS                     7       7    5,368,709,120    655,360 Y    131,072   34,359,721,984 AVAILABLE /u01/app/oracle/oradata/ORCL/users01.dbf
                          8       8    2,147,483,648    262,144 Y    131,072   34,359,721,984 AVAILABLE /u01/app/oracle/oradata/ORCL/users02.dbf

TEMP                      1       1    2,147,483,648    262,144 Y    131,072   34,359,721,984 ONLINE    /u01/app/oracle/oradata/ORCL/temp01.dbf

Key Output Columns

TABLESPACE - Tablespace name
ID - File ID number
REL FILE NO - Relative file number
BYTES - Current file size in bytes
BLOCKS - Size in Oracle blocks
AUTO - Autoextend enabled (Y if enabled)
INCREMENT BY (K) - Autoextend increment in KB
MAX SIZE (K) - Maximum file size in KB
STATUS - File status (AVAILABLE, ONLINE)
FILE NAME - Full path to data/temp file

Common Use Cases

Check Tablespace Files

-- View files for USERS tablespace
@dfile.sql
-- Enter: USERS

Monitor All Files

-- Check all data and temp files
@dfile.sql
-- Enter: %

Directory Objects - View directory paths
FRA Space Usage - Check recovery area space