tbsuse.sql
表領域の総サイズ(MB)使用サイズ(MB)未使用サイズ(MB)使用率(%)を一覧表示する
■使用法
SQL> @tbsuse
code:SQL
set timing off
col TABLESPACE_NAME for a20 heading "Tablespace Name"
col TOTAL_MB for 999,999,990 heading "Total(MB)"
col USED_MB for 999,999,990 heading "Used(MB)"
col FREE_MB for 999,999,990 heading "Free(MB)"
col USED_RATE for 990.9 heading "Used(%)"
select
a.CON_ID
,a.TABLESPACE_NAME
,a.total_bytes/1024/1024 TOTAL_MB
,(a.total_bytes - b.free_total_bytes)/1024/1024 USED_MB
,b.free_total_bytes/1024/1024 FREE_MB
,(a.total_bytes - b.free_total_bytes)/a.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_MB
,s.USED_MB
,f.TOTAL_MB - s.USED_MB FREE_MB
,(s.USED_MB/f.TOTAL_MB)*100 USED_RATE
from
(
select
CON_ID
,TABLESPACE_NAME
,sum(MAX_USED_BLOCKS)*8/1024 USED_MB
from
V$SORT_SEGMENT
where TABLESPACE_NAME like 'TEMP%'
group by CON_ID,TABLESPACE_NAME
) s
,(
select
sum(BYTES)/1024/1024 TOTAL_MB
from
DBA_TEMP_FILES
where TABLESPACE_NAME like 'TEMP%'
) f
order by TABLESPACE_NAME
;
@default_settings
■使用例
SQL> @tbsuse
Tablespace Name Total(MB) Used(MB) Free(MB) Used(%)
-------------------- ------------ ------------ ------------ -------
RDSADMIN 7 7 1 92.9
APPLTBS 1,378,304 477,339 900,965 34.6
STATSPACK 6,144 4,392 1,752 71.5
SYSAUX 3,072 727 2,345 23.7
SYSTEM 2,048 852 1,196 41.6
UNDO_T1 113,664 97,860 15,804 86.1
USERS 108,544 269 108,275 0.2
TEMP (HWM) 92,160 291 91,869 0.3
8行が選択されました。