Oracle维护常用SQL语句

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;