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';