(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
以下是修改的:
select a.tablespace_name "表空间名称",
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)",
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
where a.tablespace_name = b.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;
26。查询有哪些数据库实例在运行
select inst_name from v$active_instances;