--查询使用表空间百分比
/*connect system/--------@--------db;*/
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') as "查询时间",
a.tablespace_name as "表空间名称",
c.owner as "表空间所属用户",
a.bytes / 1024 / 1024 as "总空间(单位:MB)",
(a.bytes - b.bytes) / 1024 / 1024 as "使用表空间(单位:MB)",
b.bytes / 1024 / 1024 as "未使用空间(单位:MB)",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) || '%' as "使用表空间占百分比"
from (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes, max(bytes) largest
from dba_free_space
group by tablespace_name) b,
(select tablespace_name, owner
from dba_segments
group by tablespace_name, owner) c
where a.tablespace_name = b.tablespace_name
and a.tablespace_name = c.tablespace_name
and c.owner = '-----------'
order by c.owner, a.tablespace_name, ((a.bytes - b.bytes) / a.bytes) desc;
增加表空间大小的四种方法