--1.表空间使用情况查询
select (select contents
from dba_tablespaces
where tablespace_name = e.tablespace_name) tablespace_type,
e.tablespace_name,
e.tablespace_size,
e.tablespace_free_size,
round((1 - e.tablespace_free_size / e.tablespace_size) * 100, 2) used_percent
from (select d.tablespace_name,
sum(d.file_size) / 1024 / 1024 tablespace_size,
trunc(sum(nvl(c.file_free_size, 0)) / 1024 / 1024, 2) as tablespace_free_size
from (select a.file_id, sum(a.bytes) file_free_size
from dba_free_space a
group by a.file_id) c,
(select b.file_id,
b.file_name,
b.tablespace_name,
b.bytes file_size
from dba_data_files b) d
where d.file_id = c.file_id(+)
group by d.tablespace_name) e
union all
select upper('temporary') tablespace_type,
e.tablespace_name,
e.tablespace_size,
e.tablespace_free_size,
round((1 - e.tablespace_free_size / e.tablespace_size) * 100, 2) used_percent
from (select d.tablespace_name,
d.tablespace_size / 1024 / 1024 tablespace_size,
trunc((d.tablespace_size - nvl(c.used_tablespace_size, 0)) / 1024 / 1024,
2) as tablespace_free_size
from (select a.tablespace,
sum(a.blocks) *
(select x.value
from v$parameter x
where x.name = lower('db_block_size')) used_tablespace_size
from v$sort_usage a
group by a.tablespace) c,
(select b.tablespace_name, sum(b.bytes) tablespace_size
from dba_temp_files b
group by b.tablespace_name) d
where d.tablespace_name = c.tablespace(+)) e
order by tablespace_type, tablespace_free_size;
--表空间使用查询(适用于ORACLE7.3.4)
select e.tablespace_name,
e.tablespace_size,
e.tablespace_free_size,
(e.tablespace_size - e.tablespace_free_size) tablespace_used_size,
round((1 - e.tablespace_free_size / e.tablespace_size) * 100, 2) used_percent
from (select d.tablespace_name,
sum(d.file_size) / 1024 / 1024 tablespace_size,
trunc(sum(nvl(c.file_free_size, 0)) / 1024 / 1024, 2) as tablespace_free_size
from (select a.file_id, sum(a.bytes) file_free_size
from dba_free_space a
group by a.file_id) c,
(select b.file_id,
b.file_name,
b.tablespace_name,
b.bytes file_size
from dba_data_files b) d
where d.file_id = c.file_id(+)
group by d.tablespace_name) e
order by used_percent;
