How much disk space percentage is used in each individual datafile

How much disk space percentage is used in each individual datafile

If you are interested in investigating how much disk space percentage is used in each individual datafile:

SQL>set line 100
SQL>column TABLESPACE_NAME format A15
SQL>column Filename format A20 word_wrapped
SQL>set pagesize 80
SQL>column Used% format A5;

SQL>SELECT *

FROM (SELECT TABLESPACE_NAME,

FILE_NAME "Filename",

ROUND (BYTES / 1024 / 1024) "Size MB",

ROUND (MAXBYTES / 1024 / 1024) "MaxMB Set by DBA",

AUTOEXTENSIBLE "Autoextend Yes/No ?",

ROUND (

( ( (BYTES / 1024 / 1024) / (MAXBYTES / 1024 / 1024))),

2)

* 100

|| '%'

"Used%"

FROM SYS.DBA_DATA_FILES

WHERE AUTOEXTENSIBLE = 'YES'

UNION

SELECT TABLESPACE_NAME,

FILE_NAME "Filename",

ROUND (BYTES / 1024 / 1024) "Size MB",

(CASE WHEN MAXBYTES = 0 THEN ROUND (BYTES / 1024 / 1024) END),

(CASE

WHEN AUTOEXTENSIBLE = 'NO'

THEN

'No:MaxOut per DBA setting'

END)

"Autoextensible",

'100%'

FROM SYS.DBA_DATA_FILES

WHERE AUTOEXTENSIBLE = 'NO')

WHERE tablespace_name = 'EXAMPLE'  ---replace EXAMPLE with your real tablespace_name

ORDER BY 1 ASC, 5 DESC;

Output:

TABLESPACE_NAME Filename     Size MB MaxMB Set by DBA Autoextend Yes/No? Used%        
------------------------   -----------------            ---------- --------------------------  -------------------------   ----
EXAMPLE               +EX/bp1/blo.2       4096         31744                     YES                      13%