oracle碎片整理讨论

oracle碎片整理讨论 碎片程度 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; 查看碎片程度高的表 SELECT segment_name table_name , COUNT(*) extents FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name); If you have to rebuild a table,always consider the 'move' command first.Availability can be reduced,but the benefit to administrative safty and convenience is large.There are no windows of opportunity for data to go missing.----Jonathan Lewis. 如果必须要重建表，alter table ...move tablespace...绝对是第一选择， 理由如下: 1 对于大表move时，对此表的查询不受影响，只有在move操作完 成的瞬间受影响。DML操作受影响。 2 index结构不受影响，只需move完成后rebuild。 3 与其它对象依赖关系不受影响，操作前不必为对象间的依赖关系操 心。 4 move操作可以parallel。 5 NOLOGGING选项对move操作有作用，可大大加快重建速度。如果要move的表是nologging的，则不需指定。 基于以上理由，move是rebuild table的最佳选择，应该优先考虑，大家可以对照以上优点考虑一下用exp/imp的优缺点。采取的策略根据实际需求的不同而不同，关键是得知道我们有哪几种，每种方案的优缺点。