set echo on
REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
set linesize 165
set pagesize 40
set heading on
col fname heading "Filename" format a60
col fnum heading "#" format 999
col ts heading "Tablespace|Name" format a15
col tb heading "Total|Potential|File Size" format 999,999,999,999
col cb heading "Total|Current|File Size" like tb
col used heading "Bytes Used" like tb
col free heading "Potential|Bytes Free" like tb
col autoext heading "Auto|Ext." format a4
col percentfree heading "% Free|of|Pot.|Total|Bytes" format 999
break on report
compute sum of tb cb used free on report
spool TablespaceUsage.txt
select substr(tablespace_name,1,15) ts
,d.file_id fnum
,decode(e.file#,null,d.bytes,(e.maxextend * blksize)) tb
,d.bytes cb
,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) used
,decode(e.file#,null,d.bytes,(e.maxextend * blksize))
-decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) free
,trunc(((decode(e.file#,null,d.bytes,(e.maxextend * blksize))
-decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/
decode(e.file#,null,d.bytes,(e.maxextend * blksize))
),2) * 100 percentfree
,decode(e.file#,null,'No','Yes') autoext
,''''||substr(file_name,1,55)||'''' fname
from sys.dba_data_files d
,(select file_id,sum(bytes) freebytes
from sys.dba_free_space
group by file_id) f
,sys.filext$ e
,v$datafile v
,(select value blksize from v$parameter
where name = 'db_block_size') b
where d.file_id=f.file_id(+)
and d.file_id=e.file#(+)
and v.file#=d.file_id
order by tablespace_name,creation_time
/
spool off
prompt
prompt Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential
prompt
prompt Wrote spool file "TablespaceUsage.txt".
prompt
% Free
of
Total Total Pot.
Tablespace Potential Current Potential Total Auto
Name # File Size File Size Bytes Used Bytes Free Bytes Ext. Filename
--------------- ---- ---------------- ---------------- ---------------- ---------------- ------ ---- ---------------------------------------------------
DATA1 4 2,097,152,000 293,601,280 78,381,056 2,018,770,944 96 Yes 'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA101.DBF'
DATA2 5 2,097,152,000 241,172,480 6,684,672 2,090,467,328 99 Yes 'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA201.DBF'
RBS 2 2,097,152,000 20,971,520 65,536 2,097,086,464 99 Yes 'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTRBS01.DBF'
SYSTEM 1 2,097,152,000 205,520,896 196,878,336 1,900,273,664 90 Yes 'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTSYS01.DBF'
TEMP 3 2,097,152,000 20,971,520 49,152 2,097,102,848 99 Yes 'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTTEMP01.DBF'
---------------- ---------------- ---------------- ----------------
sum 10,485,760,000 782,237,696 282,058,752 10,203,701,248
Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential
Wrote spool file "TablespaceUsage.txt".
********************************************************************************************************************************************************