查看表空间使用情况

[list]
select dts.tablespace_name,
nvl(ddf.bytes / 1024 / 1024, 0) "total(MB)",
nvl(ddf.bytes - nvl(dfs.bytes, 0), 0) / 1024 / 1024 "used(MB)",
nvl(dfs.bytes / 1024 / 1024, 0) "free(MB)",
to_char(nvl((ddf.bytes - nvl(dfs.bytes, 0)) / ddf.bytes * 100, 0),
'990.00') "Used %"
from dba_tablespaces dts,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) ddf,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) dfs
where dts.tablespace_name = ddf.tablespace_name(+)
and dts.tablespace_name = dfs.tablespace_name(+)
and not (dts.extent_management like 'LOCAL' and
dts.contents like 'TEMPORARY')
union all
select dts.tablespace_name,
nvl(dtf.bytes / 1024 / 1024, 0) avail,
nvl(t.bytes, 0) / 1024 / 1024 used,
nvl(dtf.bytes - nvl(t.bytes, 0), 0) / 1024 / 1024 free,
to_char(nvl(t.bytes / dtf.bytes * 100, 0), '990.00') "Used %"
from dba_tablespaces dts,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) dtf,
(select tablespace_name, sum(bytes_used) bytes
from v$temp_space_header
group by tablespace_name) t
where dts.tablespace_name = dtf.tablespace_name(+)
and dts.tablespace_name = t.tablespace_name(+)
and dts.extent_management like 'LOCAL'
and dts.contents like 'TEMPORARY'

[/list]