check_free.sql

July 30, 2012 admin Old but still valuable, Oracle, Scripts

rem This script give tablespace level statisitcs
rem Note that if a tablespace has multiple datafiles this
rem script DOES not display the free space for the
rem tablespace. Instead, it displays at datafile level
rem There is also another caveat, if a datafile has
rem autoextend turned on then, this script does not account
rem for the maxbytes, it only account for the current size
rem of the datafile.
rem
set feedback off
set verify off
ttitle ‘TABLE SPACE STATISTICS’
column File# format 999
column Tablespace format a30
column TotBytes format 999,999,999
column TotUsed format 999,999,999
column TotFree format 999,999,999
column PctFree format 999.99
spool table_space_stat
select b.file_id File#, substr(b.tablespace_name, 1, 20) Tablespace,
b.bytes/1024 KBTotBytes, (b.bytes/1024 -sum(nvl(a.bytes/1024, 0))) KBTotUsed,
sum (nvl(a.bytes/1024, 0)) KBTotFree,
(sum(nvl(a.bytes/1024,0))/(b.bytes/1024))*100 “PctFree”
from dba_free_space a, dba_data_files b
where a.file_id(+) = b.file_id
group by b.tablespace_name, b.file_id, b.bytes
order by b.tablespace_name
/
exit;

check_tab_stats.sql check_last_analyzed.sql