表領域の使用率などを算出

軍鶏/ 7月 7, 2020/ 未分類

以下のSQLを実行

select a.tablespace_name,
kbytes_alloc "Allocated MB",
trunc(kbytes_alloc-nvl(kbytes_free,0),2) "Used MB",
trunc(nvl(kbytes_free,0),2) "Free MB",
( trunc((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc, 2) ) "Used",
data_files "Data Files"
from ( select sum(bytes)/1024/1024 Kbytes_free,
max(bytes)/1024/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a join
( select sum(bytes)/1024/1024 Kbytes_alloc,
tablespace_name,
count(*) data_files
from sys.dba_data_files
group by tablespace_name )b
on a.tablespace_name = b.tablespace_name
order by 1;