//查詢各tablespace目前使用容量
col tablespace_name for a20
col file_name for a50
col auto_extend for a12
set linesize 200
set pagesize 500
select
a.tablespace_name,
a.file_name,
a.total "Total(MB)",
round(a.total-b.Free_Space) "Used(MB)",
round(((a.total-b.Free_Space)/a.total)*100,2) "Used(%)",
a.auto_extend
from
(select
FILE_ID,
tablespace_name,
file_name,
bytes/(1024*1024) Total,
AUTOEXTENSIBLE auto_extend
from
dba_data_files ddf) a,
(select
file_id,
sum(bytes)/(1024*1024) Free_Space
from
dba_free_space
group by file_id) b
where
a.file_id=b.file_id
and a.tablespace_name not in ('SYSTEM','SYSAUX')
