/*
#################################################################
#								#
#     Shows the info about all datafiles in the database        #
#								#
#################################################################
*/
set lines 200
COL TABLESPACE_NAME FOR A10
COL FILE_NAME FOR A38
COL FILE_SIZE FOR A9
COL FREE_SPACE FOR A8
col USED_SPACE for a10
col autoextensible for a6
col status for a10
col pct_free for a8
col max_bytes for a10
col c0 noprint

select NVL(SUM(B.BYTES/1048576),0) / A.BYTES/1048576 c0,
         A.tablespace_name,
         A.file_name,
         to_char(TRUNC(NVL(SUM(B.BYTES/1048576),0) /
(A.BYTES/1048576),4)*100)||'%' pct_free,
       NVL(TRUNC(SUM(B.BYTES/1048576),2),0)||'MB' FREE_SPACE,
       TRUNC((A.BYTES/1048576)-NVL(SUM(B.BYTES/1048576),2),0)||'MB'
USED_SPACE, TRUNC(A.BYTES/1048576,2)||'MB' FILE_SIZE,
a.maxbytes/1024/1024||'MB' MAX_BYTES,
         autoextensible,
         status
FROM   DBA_DATA_FILES A, DBA_FREE_SPACE B
WHERE  A.FILE_ID=B.FILE_ID (+)
GROUP BY A.tablespace_name, A.file_name,a.maxbytes,
A.bytes/1048576,autoextensible,status,A.BYTES
UNION
select NVL(SUM(BB.BYTES/1048576),0) / AA.BYTES/1048576 c0,
         AA.tablespace_name,
         AA.file_name,
         to_char(TRUNC(NVL(SUM(BB.BYTES/1048576),0) / (AA.BYTES/1048576),4)*100)||'%' pct_free,
       NVL(TRUNC(SUM(BB.BYTES/1048576),2),0)||'MB' FREE_SPACE,
         TRUNC((AA.BYTES/1048576)-NVL(SUM(BB.BYTES/1048576),2),0)||'MB' USED_SPACE,
         TRUNC(AA.BYTES/1048576,2)||'MB' FILE_SIZE,
aa.maxbytes/1024/1024||'MB' MAX_BYTES,
       autoextensible,
         status
FROM     DBA_TEMP_FILES AA, DBA_FREE_SPACE BB
WHERE    AA.FILE_ID=BB.FILE_ID (+)
GROUP BY
AA.tablespace_name,AA.FILE_NAME,AA.BYTES/1048576,autoextensible,status,AA.BYTES,aa.maxbytes
ORDER BY 1
/