/*
|| 表領域サイズ情報
*/
set lines 120
set pages 100
set term off
tti off
clear col
col tablespace_name format a20 head “表領域名”
col “T_SIZE” format a20 head “全ファイルサイズ(MB)”
col “U_SIZE” format a20 head “使用サイズ(MB)”
col “F_SIZE” format a20 head “空きサイズ(MB)”
col “U_RATE” format 990.99 head “使用率(%)”
ttitle left ‘■■■■ 表領域サイズ情報 ■■■■’
spool d:\work\20190404\tbs_use_info.lis
SELECT
tablespace_name
,to_char(nvl(total_bytes/1024/1024,0),’999,999,999′) T_SIZE
,to_char(nvl((total_bytes – free_total_bytes)/1024/1024,0),’999,999,999′) U_SIZE
,to_char(nvl(free_total_bytes/1024/1024,0),’999,999,999′) F_SIZE
,round(nvl((total_bytes – free_total_bytes)/total_bytes * 100,100),2) U_RATE
FROM
( SELECT tablespace_name, sum(bytes) total_bytes
FROM dba_data_files
GROUP BY tablespace_name
)
,( SELECT tablespace_name free_tablespace_name, sum(bytes) free_total_bytes
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY 1
)
WHERE tablespace_name = free_tablespace_name(+)
;
spool off
clear col
ttitle off
