tbsuseg.sql
code:SQL
set timing off
col TABLESPACE_NAME for a20 heading "Tablespace Name"
col TOTAL_GB for 999,990 heading "Total(GB)"
col USED_GB for 999,990 heading "Used(GB)"
col FREE_GB for 999,990 heading "Free(GB)"
col USED_RATE for 990.9 heading "Used(%)"
select
a.CON_ID
,a.TABLESPACE_NAME
,total_bytes/1024/1024/1024 TOTAL_GB
,(total_bytes - free_total_bytes)/1024/1024/1024 USED_GB
,free_total_bytes/1024/1024/1024 FREE_GB
,(total_bytes - free_total_bytes)/total_bytes*100 USED_RATE
from
(
select CON_ID,TABLESPACE_NAME,sum(BYTES) total_bytes from cdb_data_files group by CON_ID,TABLESPACE_NAME
) a,
(
select CON_ID,TABLESPACE_NAME,sum(BYTES) free_total_bytes from cdb_free_space group by CON_ID,TABLESPACE_NAME
) b
where a.CON_ID = b.CON_ID(+)
and a.TABLESPACE_NAME = b.TABLESPACE_NAME(+)
--order by TABLESPACE_NAME
UNION ALL
-- Added. For Temporary Tablespace 2020/11/06
select
s.CON_ID
,s.TABLESPACE_NAME||' (HWM)' TABLESPACE_NAME
,f.TOTAL_GB
,s.USED_GB
,f.TOTAL_GB - s.USED_GB FREE_GB
,(s.USED_GB/f.TOTAL_GB)*100 USED_RATE
from
(
select
CON_ID
,TABLESPACE_NAME
,sum(MAX_USED_BLOCKS)*8/1024/1024 USED_GB
from
V$SORT_SEGMENT
where TABLESPACE_NAME like 'TEMP%'
group by CON_ID,TABLESPACE_NAME
) s
,(
select
sum(BYTES)/1024/1024/1024 TOTAL_GB
from
DBA_TEMP_FILES
where TABLESPACE_NAME like 'TEMP%'
) f
order by TABLESPACE_NAME
;
@default_settings