set pagesize 10000
set COLSEP '|'
set VERIFY off
set serveroutput on size 1000000
BREAK ON report
column tablespace_name format a30 heading 'TABLESPACE'
column sizegb format 9999999999D9 heading 'SIZE-Gb'
column usedproc format 999D99 heading 'USED-%'
column status format a10 heading 'STATUS'
COMPUTE sum LABEL 'Total size:' of sizegb ON report
SELECT b.tablespace_name ,
b.bytes/1024/1024/1024 as sizegb ,
NVL(100-((a.bytes/b.bytes)*100), 100) usedproc,
replace(c.status,' ','_') status
FROM
( select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name
) a ,
( select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name
) b ,
dba_tablespaces c
WHERE b.tablespace_name = a.tablespace_name (+)
AND b.tablespace_name = c.tablespace_name
union
select f.TABLESPACE_NAME,
f.TOTAL_MB/1024 sizegb,
nvl( (u.USED_MB/f.TOTAL_MB)*100, 0 ) usedproc,
'TEMPORARY' status
from
(
select f1.TABLESPACE_NAME,sum( f1.BYTES/1024/1024 ) TOTAL_MB
from (
select TABLESPACE_NAME,BYTES
from dba_temp_files
union all
select TABLESPACE_NAME,BYTES
from dba_data_files
where TABLESPACE_NAME in (
select TABLESPACE_NAME
from dba_tablespaces
where CONTENTS='TEMPORARY'
)
) f1
group by f1.TABLESPACE_NAME
) f,
(
select u1.TABLESPACE,
sum(u1.blocks) * max((select value from v$parameter where name='db_block_size')/1024/1024) USED_MB
from v$sort_usage u1
group by u1.TABLESPACE
) u
where f.TABLESPACE_NAME = u.tablespace (+)
ORDER BY 1;