Script to show Database Filename, Free Space and Status

SET SERVEROUTPUT ON
SET ECHO OFF
SET FEEDBACK OFF
SET PAGES 80
SET LINES 132

REM
REM || Title : show_tabfilesp.sql
REM ||
REM || Purpose : This script produces a brief report showing the filenames, free space, size
REM || and status of all tablespaces in the database.
REM ||
REM || Release No : 1
REM ||
REM || Variables : None
REM ||
REM || Schema : system
REM ||
REM ||


BREAK ON table_space ON free

COLUMN avail_mb FORMAT 999,999
COLUMN total_mb FORMAT 999,999
COLUMN name_of_file FORMAT A50

SELECT SUBSTR (df.tablespace_name, 1, 15) table_space,
SUBSTR (df.file_name, 1, 50) name_of_file,
SUM (fs.bytes)/1024/1024 avail_mb,
df.bytes/1024/1024 total_mb,
SUBSTR (df.status, 1, 5) stat
FROM dba_data_files df,
dba_free_space fs
WHERE df.tablespace_name = fs.tablespace_name
GROUP BY SUBSTR (df.tablespace_name, 1, 15),
SUBSTR (df.file_name, 1, 50),
df.bytes,
SUBSTR (df.status, 1, 5)

SPOOL show_tabfilesp.lis
/
SPOOL OFF

SET SERVEROUTPUT ON
SET FEEDBACK ON
SET PAGES 60

CLEAR BREAKS
This script is provided for educational purposes only. The script has been tested and appears to work as intended. However, you should always test any script before relying on it. No responsibility will be accepted for Lost or damage that may occur from it's use.