--set linesize 121
----------------------------------------------------------------------------------------
-- free.sql
--
-- This SQL Plus script lists freespace by tablespace
------------------------------------------------------------------------------------------
column dummy noprint
column pct_used format 999.9 heading "%|Used"
column name format a35 heading "Tablespace Name"
column Mbytes format 9,999,999,999 heading "Mbytes"
column used format 9,999,999,999 heading "Used"
column free format 9,999,999,999 heading "Free"
column largest format 9,999,999,999 heading "Largest"
col MBYTES_PROVIDEED format 999,999,999 heading "Mbytes Provideed"
column max_size format 9,999,999,999,999 heading "MaxPoss|Mbytes"
column pct_max_used format 999.9 heading "%|Max|Used"
break on report
compute sum of Mbytes on report
compute sum of free on report
compute sum of used on report
compute sum of Max_Size on report
compute sum of mbytes_provideed on report
select (select decode(extent_management, 'LOCAL', '*', ' ') ||
decode(segment_space_management, 'AUTO', 'a ', 'm ')
from dba_tablespaces
where tablespace_name = b.tablespace_name) ||
nvl(b.tablespace_name, nvl(a.tablespace_name, 'UNKOWN')) name,
mbytes_alloc mbytes,
mbytes_alloc - nvl(mbytes_free, 0) used,
nvl(mbytes_free, 0) free,
((mbytes_alloc - nvl(mbytes_free, 0)) / mbytes_alloc) * 100 pct_used,
nvl(largest, 0) largest,
round(mbytes_provideed,2) mbytes_provideed ,
nvl(mbytes_max, mbytes_alloc) Max_Size,
decode(mbytes_max, 0, 0, (mbytes_alloc / mbytes_max) * 100) pct_max_used
from (select sum(bytes) / 1024 /1024 Mbytes_free,
max(bytes) / 1024 /1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name) a,
(select sum(bytes) / 1024 / 1024 Mbytes_alloc,
sum(decode(autoextensible,'YES',maxbytes,bytes)) / 1024 / 1024 Mbytes_max,
(sum(decode(autoextensible,'YES',maxbytes,bytes)) / 1024 / 1024) - (sum(bytes) / 1024 / 1024) mbytes_provideed,
tablespace_name
from sys.dba_data_files
group by tablespace_name
union all
select sum(bytes) / 1024 / 1024 Mbytes_alloc,
sum(decode(autoextensible,'YES',maxbytes,bytes)) / 1024 / 1024 Mbytes_max,
(sum(decode(autoextensible,'YES',maxbytes,bytes)) / 1024 / 1024) - (sum(bytes) / 1024 / 1024) mbytes_provideed,
tablespace_name
from sys.dba_temp_files
group by tablespace_name) b
where a.tablespace_name(+) = b.tablespace_name
order by 1
/
Tablespace Name Mbytes Used Free Used Largest Mbytes Provideed Mbytes Used
----------------------------------- -------------- -------------- -------------- ------ -------------- ---------------- ------------------ ------
*a SYSAUX 2,460 2,297 163 93.4 101 30,308 32,768 7.5
*a USERS 5 1 4 20.0 4 32,763 32,768 .0
*m SYSTEM 1,860 1,854 6 99.7 5 30,908 32,768 5.7
*m TEMP 173 173 0 100.0 0 32,595 32,768 .5
*m UNDOTBS1 840 37 803 4.5 736 31,928 32,768 2.6
-------------- -------------- -------------- ----------------
sum 5,338 4,363 975 158,502
