2016-03-09 回答
查看“oracle”临时表空间当前使用了多少空间，可按照以下程序。
select d.tablespace_name "name", d.status "status",
to_char (nvl (a.bytes / 1024 / 1024, 0), '99,999,990.90') "size (m)",
to_char (nvl (a.bytes - nvl (f.bytes, 0), 0) / 1024 / 1024,
'99999999.99'
) use,
to_char (nvl ((a.bytes - nvl (f.bytes, 0)) / a.bytes * 100, 0),
'990.00'
) "used %"
from sys.dba_tablespaces d,
(select tablespace_name, sum (bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum (bytes) bytes
from dba_free_space
group by tablespace_name) f
where d.tablespace_name = a.tablespace_name(+)
and d.tablespace_name = f.tablespace_name(+)
and not (d.extent_management like 'local' and d.contents like 'temporary')
union all
select d.tablespace_name "name", d.status "status",
to_char (nvl (a.bytes / 1024 / 1024, 0), '99,999,990.90') "size (m)",
to_char (nvl (t.bytes, 0) / 1024 / 1024, '99999999.99') use,
to_char (nvl (t.bytes / a.bytes * 100, 0), '990.00') "used %"
from sys.dba_tablespaces d,
(select tablespace_name, sum (bytes) bytes
from dba_temp_files
group by tablespace_name) a,
(select tablespace_name, sum (bytes_cached) bytes
from v$temp_extent_pool
group by tablespace_name) t
where d.tablespace_name = a.tablespace_name(+)
and d.tablespace_name = t.tablespace_name(+)
and d.extent_management like 'local'
and d.contents like 'temporary';