SYSTEM表領域の使用状況(表領域およびデータファイル)を確認する

col tablespace_name format a30
col file_name format a30
break on tablespace_name

select
  tablespace_name
  ,' (表領域)' file_name
  ,sum(bytes) / 1024 / 1024 bytes_mb
  ,sum(bytes - nvl(c3,0)) / 1024 used_kb
  ,sum(nvl(c3,0)) / 1024 free_kb
  ,round*1 / 1024) / sum(bytes) / 1024) * 100, 2) "ts_used_%"
  ,to_number(null) "df_used_%"
from
  dba_data_files a
  ,(select tablespace_name c1, file_id c2, sum(bytes) c3
  from dba_free_space group by tablespace_name, file_id) b
where
  a.tablespace_name = b.c1(+)
  and a.file_id = b.c2(+)
  and a.tablespace_name = 'SYSTEM'
group by
  tablespace_name
union
select
  tablespace_name
  ,file_name
  ,bytes / 1024 / 1024 bytes_mb
  ,(bytes - nvl(c3, 0)) / 1024 used_kb
  ,nvl(c3, 0) / 1024 free_kb
  ,to_number(null) "ts_used_%"
  ,round(((bytes - nvl(c3, 0)) / 1024) / (bytes / 1024) * 100, 2) "df_used_%"
from
  dba_data_files a
  ,(select tablespace_name c1, file_id c2, sum(bytes) c3
  from dba_free_space group by tablespace_name, file_id) b
where
  a.tablespace_name = b.c1(+)
  and a.file_id = b.c2(+)
  and a.tablespace_name = 'SYSTEM'
order by 1
;

*1:sum(bytes - nvl(c3,0