set pages 60
set lin 160
set markup HTML on
set termout off
set feed off
col DATABASE_NAME hea 'Database|Name' for a10 word_wrapped
col TABLESPACE_NAME hea 'Tablespace Name' for a18 word_wrapped
col FILE_NAME hea 'File Name' for a50 word_wrapped
col USED_GB hea 'File Size|(GB)' jus r for 999999.99
col MAX_GB hea 'MAX SIZE|(GB)' jus r for 999999
col FREE_P hea 'Free|(%)' jus r for 990.0
col AUTOEXTENSIBLE hea 'Auto|Extend' for a6
col STATUS hea 'Status' for a10
col db_info new_v db_info
select 'for '||name||' on '||to_char(sysdate, 'YYYY-MM-DD') db_info from v$database;
tti ce '********** Tablespace Storage Information Report **********' s 1 -
ce '********** 'db_info' **********' s 2
set termout on
select b.name database_name, a.tablespace_name,
decode(count(a.file_name), 1, min(a.file_name), '2+ Files') file_name,
to_char(round(sum(a.bytes)/1024/1024/1024,2),9999990.99) used_gb,
round(sum(a.maxbytes)/1024/1024/1024) max_gb,
to_char(round((1-sum(bytes)/sum(a.maxbytes))*100,1),990.9) free_p,
a.autoextensible,
a.status
from cdb_data_files a
left join v$containers b on a.con_id=b.con_id
group by b.name, a.tablespace_name, a.autoextensible, a.status, a.con_id
order by a.con_id, (1-sum(bytes)/sum(a.maxbytes));
set markup HTML off
exit
set linesize 300
set pagesize 999
col tablespace_name format a20
select a.tablespace_name,
round(SUM(a.bytes)/(1024*1024*1024)) CURRENT_GB,
round(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024), b.maxextend*8192/(1024*1024*1024)))) MAX_GB,
(SUM(a.bytes)/(1024*1024*1024) - round(c.Free/1024/1024/1024)) USED_GB,
round((SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024), b.maxextend*8192/(1024*1024*1024)))
- (SUM(a.bytes)/(1024*1024*1024)
- round(c.Free/1024/1024/1024)))
,2) FREE_GB,
round(100*(SUM(a.bytes)/(1024*1024*1024)
- round(c.Free/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024), b.maxextend*8192/(1024*1024*1024))))) USED_PCT
from dba_data_files a, sys.filext$ b, (SELECT d.tablespace_name ,sum(nvl(c.bytes,0)) Free
FROM dba_tablespaces d,DBA_FREE_SPACE c
WHERE d.tablespace_name = c.tablespace_name(+)
group by d.tablespace_name) c
WHERE a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name, c.Free/1024
ORDER BY tablespace_name;