Mir Sayeed Hassan – Oracle Blog

Script to check the datafile size used in Oracle database

[oracle@testdb scripts]$ pwd
/home/oracle/scripts

[oracle@testdb scripts]$ cat checkdf.sql
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN "Tablespace Name" FORMAT A20
COLUMN "File Name" FORMAT A80

SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
 Substr(df.file_name,1,80) "File Name",
 Round(df.bytes/1024/1024,0) "Size (M)",
 decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used (M)",
 decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free (M)",
 decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
FROM DBA_DATA_FILES DF,
 (SELECT file_id,
 sum(bytes) used_bytes
 FROM dba_extents
 GROUP by file_id) E,
 (SELECT sum(bytes) free_bytes,
 file_id
 FROM dba_free_space
 GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
AND df.file_id = f.file_id (+)
ORDER BY df.tablespace_name,
 df.file_name
/

[oracle@testdb scripts]$ !sq
sqlplus

sys@TESTDB> @/home/oracle/scripts/checkdf.sql;
Press Return to Continue

Tablespace Name File Name Size (M) Used (M) Free (M) % Used
-------------------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------
EXAMPLE /u01/app/oracle/oradata/prim/example01.dbf 50 0 49 0
GOLDENGATE /u01/app/oracle/oradata/prim/goldengate01.dbf 100 0 99 0
SYSAUX /u01/app/oracle/oradata/prim/sysaux01.dbf 6816 6489 326 95
SYSTEM /u01/app/oracle/oradata/prim/system01.dbf 31180 31179 0 100
SYSTEM /u01/app/oracle/oradata/prim/system02.dbf 5120 3314 1805 65
UNDONEW /u01/app/oracle/oradata/prim/undonew.dbf 200 156 43 78
UNDOTBS1 /u01/app/oracle/oradata/prim/UNDOTBS1.dbf 200 6 193 3
USERS /backup/datafiles/users03.dbf 100 0 99 0
USERS /u01/app/oracle/oradata/prim/users01.dbf 500 308 191 62
USERS /u01/app/oracle/oradata/prim/users02.dbf 0 0 0
USERS_NEW /u01/app/oracle/oradata/prim/usersnew.dbf 200 0 199 0

11 rows selected.

========Hence tested & verified========