Code:
set feedback off
set pagesize 0
set trimspool on
ttitle off
btitle off
set verify off
set linesize 260
column name heading "TABLESPACE" format A25
column size_mb heading "SIZE (M)" format 99999990.9
column "USED (%)" format 990
select ts name,
round(size_mb,2) size_mb,
round(decode(total_size_mb,0,'',(total_size_mb-total_free_mb)*100/total_size_mb),0) "USED (%)",
autoextensible "AUT"
from
(select a.tablespace_name ts,
nvl(c.mb,0)+nvl(e.mb,0) size_mb,
decode(lower(a.contents),'temporary',
decode(sign(nvl(e.tot_mb,0)-nvl(e.mb,0)),1,'YES','NO'),
decode(sign(nvl(c.tot_mb,0)-nvl(c.mb,0)),1,'YES','NO'))
autoextensible,
decode(lower(a.contents),'temporary',
decode(sign(nvl(e.tot_mb,0)-nvl(e.mb,0)),1,
nvl(e.tot_mb,0),nvl(e.mb,0)),
decode(sign(nvl(c.tot_mb,0)-nvl(c.mb,0)),1,
nvl(c.tot_mb,0),nvl(c.mb,0)))
total_size_mb,
decode(lower(a.contents),'temporary',
decode(sign(nvl(e.tot_mb,0)-nvl(e.mb,0)),1,
nvl(e.tot_mb,0)-nvl(f.used_mb,0),
nvl(e.mb,0)-nvl(f.used_mb,0)),
decode(sign(nvl(c.tot_mb,0)-nvl(c.mb,0)),1,
nvl(c.tot_mb,0)-nvl(c.mb,0)+nvl(d.free_mb,0),
nvl(d.free_mb,0)))
total_free_mb
from
dba_tablespaces a,
(select tablespace_name ts,
sum(extents) ext,
count(*) seg
from dba_segments group by tablespace_name) b,
(select tablespace_name ts,
sum(nvl(bytes,0))/1024/1024 mb,
count(*) files,
sum(decode(lower(autoextensible),'no',nvl(bytes,0),nvl(maxbytes,0)))/1024/1024 tot_mb
from dba_data_files group by tablespace_name) c,
(select tablespace_name ts,
sum(nvl(bytes,0))/1024/1024 free_mb
from dba_free_space group by tablespace_name) d,
(select tablespace_name ts,
sum(nvl(bytes,0)/1024/1024) mb,
count(*) files,
sum(decode(lower(autoextensible),'no',nvl(bytes,0),
nvl(maxbytes,0)))/1024/1024 tot_mb
from dba_temp_files group by tablespace_name) e,
(select tablespace_name ts,
sum(nvl(bytes_used,0))/1024/1024 used_mb
from gv$temp_extent_pool group by tablespace_name) f
where
a.tablespace_name=b.ts(+) and
a.tablespace_name=c.ts(+) and
a.tablespace_name=d.ts(+) and
a.tablespace_name=e.ts(+) and
a.tablespace_name=f.ts(+))
order by 1 desc
/