Displays space usage for each datafile

SQL> SET SERVEROUTPUT ON
SQL> SET PAGESIZE 1000
SQL> SET LINESIZE 255
SQL> SET FEEDBACK OFF
SQL> SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
2 Substr(df.file_name,1,40) "File Name",
3 Round(df.bytes/1024/1024,2) "Size (M)",
4 Round(e.used_bytes/1024/1024,2) "Used (M)",
5 Round(f.free_bytes/1024/1024,2) "Free (M)",
6 Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used"
7 FROM DBA_DATA_FILES DF,
8 (SELECT file_id,
9 Sum(Decode(bytes,NULL,0,bytes)) used_bytes
10 FROM dba_extents
11 GROUP by file_id) E,
12 (SELECT Max(bytes) free_bytes,
13 file_id
14 FROM dba_free_space
15 GROUP BY file_id) f
16 WHERE e.file_id (+) = df.file_id
17 AND df.file_id = f.file_id (+)
18 ORDER BY df.tablespace_name,
19 df.file_name;

Tablespace Name File Name Size (M) Used (M) Free (M) % Used
-------------------- ---------------------------------------- ---------- ---------- ---------- -----------
APPS_TS_ARCHIVE /dev02/TESTONE/db/apps_st/data/a_archive 2000 1020.63 874.5 XXXXX-----
APPS_TS_INTERFACE /dev02/TESTONE/db/apps_st/data/a_int01.d 2000 908.88 1071.13 XXXXX-----
APPS_TS_MEDIA /dev02/TESTONE/db/apps_st/data/a_media01 2000 1166.13 833.75 XXXXXX----
APPS_TS_NOLOGGING /dev02/TESTONE/db/apps_st/data/a_nolog01 60 39.75 11.88 XXXXXXX---
APPS_TS_QUEUES /dev02/TESTONE/db/apps_st/data/a_queue01 500 137 362.88 XXX-------
APPS_TS_QUEUES /dev02/TESTONE/db/apps_st/data/a_queue02 500 162.63 337 XXX-------
APPS_TS_SEED /dev02/TESTONE/db/apps_st/data/a_ref01.d 1500 1396.5 95.63 XXXXXXXXX-
APPS_TS_SEED /dev02/TESTONE/db/apps_st/data/a_ref02.d 1500 1029.5 464.75 XXXXXXX---
APPS_TS_SUMMARY /dev02/TESTONE/db/apps_st/data/a_summ01. 1000 966.75 6.75 XXXXXXXXXX
APPS_TS_TOOLS /dev02/TESTONE/db/apps_st/data/apps_ts_t 500 499.88 ----------
APPS_TS_TX_DATA /dev02/TESTONE/db/apps_st/data/a_txn_dat 4000 2686 1276.63 XXXXXXX---
APPS_TS_TX_DATA /dev02/TESTONE/db/apps_st/data/a_txn_dat 2000 1997.13 0.13 XXXXXXXXXX
APPS_TS_TX_DATA /dev02/TESTONE/db/apps_st/data/a_txn_dat 2000 1991 0.13 XXXXXXXXXX
APPS_TS_TX_IDX /dev02/TESTONE/db/apps_st/data/a_txn_ind 2000 1341.5 652.38 XXXXXXX---
APPS_TS_TX_IDX /dev02/TESTONE/db/apps_st/data/a_txn_ind 2000 1496.13 503 XXXXXXX---
APPS_TS_TX_IDX /dev02/TESTONE/db/apps_st/data/a_txn_ind 2000 1340.5 656.38 XXXXXXX---
APPS_TS_TX_IDX /dev02/TESTONE/db/apps_st/data/a_txn_ind 2000 1234.63 764.38 XXXXXX----
APPS_TS_TX_IDX /dev02/TESTONE/db/apps_st/data/a_txn_ind 2000 1609.38 390.25 XXXXXXXX--
APPS_UNDOTS1 /dev02/TESTONE/db/apps_st/data/undo01.db 4000 3967.94 8 XXXXXXXXXX
CTXD /dev02/TESTONE/db/apps_st/data/ctxd01.db 20 17.13 2.75 XXXXXXXXX-
INTERIM /dev02/TESTONE/db/apps_st/data/interim.d 200 199.88 ----------
ODM /dev02/TESTONE/db/apps_st/data/odm.dbf 100 14.38 85.5 X---------
OLAP /dev02/TESTONE/db/apps_st/data/olap.dbf 100 31 68.88 XXX-------
OWAPUB /dev02/TESTONE/db/apps_st/data/owad01.db 10 9.88 ----------
PORTAL /dev02/TESTONE/db/apps_st/data/portal01. 100 1.25 98.63 ----------
SYSAUX /dev02/TESTONE/db/apps_st/data/sysaux01. 2000 1996.38 0.81 XXXXXXXXXX
SYSAUX /dev02/TESTONE/db/apps_st/data/sysaux02. 2493.5 2493.44 XXXXXXXXXX
SYSTEM /dev02/TESTONE/db/apps_st/data/system01. 1000 919.98 78.32 XXXXXXXXX-
SYSTEM /dev02/TESTONE/db/apps_st/data/system02. 1000 927.05 70.34 XXXXXXXXX-
SYSTEM /dev02/TESTONE/db/apps_st/data/system03. 1000 980.36 19.2 XXXXXXXXXX
SYSTEM /dev02/TESTONE/db/apps_st/data/system04. 1000 937.44 59.72 XXXXXXXXX-
SYSTEM /dev02/TESTONE/db/apps_st/data/system05. 1000 924.8 75.01 XXXXXXXXX-
SYSTEM /dev02/TESTONE/db/apps_st/data/system06. 1250 768.82 481.05 XXXXXX----
SYSTEM /dev02/TESTONE/db/apps_st/data/system07. 1250 768.06 481.82 XXXXXX----
SYSTEM /dev02/TESTONE/db/apps_st/data/system08. 1100 768.17 331.73 XXXXXXX---
SYSTEM /dev02/TESTONE/db/apps_st/data/system09. 1000 494.59 504.95 XXXXX-----
SYSTEM /dev02/TESTONE/db/apps_st/data/system10. 1000 869.55 129.02 XXXXXXXXX-
SYSTEM /dev02/TESTONE/db/apps_st/data/system11. 1000 906.73 91.38 XXXXXXXXX-

SQL>