To determine Tablespace, File, Size, Used, Free, Maxbytes, Auto Extend of all the tablespces in oracle

To determine Tablespace, File, Size, Used, Free, Maxbytes, Auto Extend of all the tablespces in oracle:



SQL>set line 100
SQL> set pagesize 80

SQL>column tablespace format A10

SQL>column file format A20 word_wrapped


SQL>SELECT Substr(df.tablespace_name,1,20) "Tablespace",
Substr(df.file_name,1,40) "File",
Round(df.bytes/1024/1024,2) "Size (M)",
Round(e.used_bytes/1024/1024,2) "Used (M)",
Round(f.free_bytes/1024/1024,2) "Free (M)",
Round(df .MAXBYTES/1024/1024,2) "Maxbytes (M)",
Substr(df.AUTOEXTENSIBLE,1,20) "Autoextensible"
FROM DBA_DATA_FILES DF,
(SELECT file_id,
Sum(Decode(bytes,NULL,0,bytes)) used_bytes
FROM dba_extents
GROUP by file_id) E,
(SELECT Max(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;