Oracleをインストールしてみた2 (表領域毎の容量)

Oracleいいですね。

ちょっと遊んでいます。
SQLPLUSを起動して、まずは以下のコマンドでフォーマットを整えまして。

column tsname heading "表領域" format A14
column used heading "割当済(MB)" format 99,999.999
column free heading "未使用(MB)" format 99,999.999
column total heading "合　計(MB)" format 99,999.999
column retio heading "割当率%" format 999.99
column maxsize heading "空最大(MB)" format 99,999.999

次に以下のSQLを実行。

select
t1.ts as tsname,
sum(decode(t1.kbn,'u',t1.mb,0))/1024/1024 as used,
sum(decode(t1.kbn,'f',t1.mb,0))/1024/1024 as free,
sum(decode(t1.kbn,'a',t1.mb,0))/1024/1024 as total,
sum(decode(t1.kbn,'u',t1.mb,0))/sum(decode(t1.kbn,'a',t1.mb,0))*100 as retio,
sum(decode(t1.kbn,'m',t1.mb,0))/1024/1024 as maxsize
from (
select rtrim(TABLESPACE_NAME,' ') ts,sum(BYTES) mb,'u' kbn from DBA_SEGMENTS group by TABLESPACE_NAME
union
select rtrim(TABLESPACE_NAME,' ') ts,sum(BYTES) mb,'f' kbn from DBA_FREE_SPACE group by TABLESPACE_NAME
union
select rtrim(TABLESPACE_NAME,' ') ts,sum(BYTES) mb,'a' kbn from DBA_DATA_FILES group by TABLESPACE_NAME
union
select rtrim(TABLESPACE_NAME,' ') ts,max(BYTES) mb,'m' kbn from DBA_FREE_SPACE group by TABLESPACE_NAME
) t1
group by t1.ts

実行結果が以下です。