100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) \占用率(%)\ round(a.bytes_alloc/1024/1024,2) \容量(M)\ round(nvl(b.bytes_free,0)/1024/1024,2) \空闲(M)\
round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) \使用(M)\ Largest \最大扩展段(M)\
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') \采样时间\ from (select f.tablespace_name, sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes from dba_data_files f group by tablespace_name) a, (select f.tablespace_name, sum(f.bytes) bytes_free from dba_free_space f group by tablespace_name) b,
(select round(max(ff.length)*16/1024,2) Largest, ts.name tablespace_name
from sys.fet$ ff, sys.file$ tf,sys.ts$ ts
where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts# group by ts.name, tf.blocks) c
where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name
25、 查询表空间的碎片程度
select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name having count(tablespace_name)>10;
alter tablespace name coalesce; alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
select * from ts_blocks_v;
select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space group by tablespace_name;