Oracle 碎片整理

Oracle 碎片问题

1>碎片对系统的影响

当要满足一个空间要求时，数据库将首先查找当前最大的自由范围，而"最大"自由范围逐渐变小，要找到一个足够大的自由范围已变得越来越困难，从而导致表空间中的速度障碍，使数据库的空间分配愈发远离理想状态 

部分的extent无法得以自动合并，浪费了大量的表空间

2>自由范围的碎片计算

FSFI（free space fragmentation index 自由空间碎片索引）

FSFI=100*sqrt(max(extent)/sum(extents))*1/sqrt(sqrt(count(extents))) 

FSFI最大100（理想的单文件表空间）

-- --查看所有表空间的碎片程度（值在30以下表示碎片很多）

select tablespace_name,sum(bytes),sum(free),sum(free)*100/sum(bytes) from (select b.file_id file_ID, b.tablespace_name tablespace_name, b.bytes Bytes, (b.bytes-sum(nvl(a.bytes,0))) used, sum(nvl(a.bytes,0)) free, sum(nvl(a.bytes,0))/(b.bytes)*100 Percent from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order by b.file_id) group by tablespace_name order by sum(free)*100/sum(bytes);

--运行后

TABLESPACE_NAME                SUM(BYTES)  SUM(FREE) SUM(FREE)*100/SUM(BYTES)

------------------------------ ---------- ---------- ------------------------

SYSTEM                          503316480    9568256               1.90104167

SYSAUX                          251658240    9240576                 3.671875

UNDOTBS1                        152043520  129368064               85.0862069

USERS                             5242880    4784128                    91.25

补充

--查找数据库某个表空间内最大连续的自由空间大小

SELECT tablespace_name,max(bytes)/1024/1024 "max_free_space(M)" 

FROM dba_free_space 

GROUP BY tablespace_name 

ORDER BY 1;

3>碎片整理

--盘区（extent）的碎片整理：alter tablespace temp coalesce

SELECT   'alter tablespace ' || TABLESPACE_NAME || ' coalesce ;'

  FROM   DBA_FREE_SPACE_COALESCED

 WHERE   PERCENT_EXTENTS_COALESCED < 100 OR PERCENT_BLOCKS_COALESCED < 100;

--段（segment）碎片整理，查看数据字典dba_segments,segment 的信息可以查看dba_extents

--表段，索引段等。每种段都可以碎片的整理

3.1>表段碎片整理

方式一:import/export

最简单的方法是用正确的存储参数将这个段重建，将旧表中的数据插入到新表，同时删除旧表。Import/export来完成。

Export命令有个（压缩标志）这个标志在读表时会引发export确定该表所分配的物理空间量，它会向输出转储文件写入一个新的初始化存储参数--等于全部所分配空间。若这个表关闭， 则使用import命令重新生成。

$exp user/password  file=exp.dmp  compress=y  grants=y  indexes=y tables(table1,table2);

$imp user/password  file=exp.emp  commit=y  buffer=64000  tables=(table1,table2);

除了exp/imp，还可以使用shrink table 或alter table tbname move

方式二:Shrink table

http://blog.csdn.net/leishifei/archive/2011/04/23/6347524.aspx

介绍：10g开始，提供shrink命令

表空间支持自动段空间管理，使用这个特性，降低HWM

分为两个阶段

1.数据重装（compact）：一系列insert，delete操作，将数据尽量排列在段的前面，这个过程需要在表上加RX锁，即，只在需要移动的行上加锁。涉及到rowid的改变，需要enable row movement同时disable基于rowid的trigger，对业务影响较小

语法：

$shrink space compact 

SQL>alter table <table_name> enable row movement --会造成引用表的XX对象（存储，包，视图）变为失效，执行utlrp.sql来编译无效的对象

2.HWM的调整：调整HWM位置，释放空间数据块，需要在表上加X锁，会造成表上所有DML语句阻塞，业务繁忙期影响较大。

语法：

alter table <table_name> shrink space [ <null> | compact | cascade ];

alter table <table_name> shrink space compcat;

收缩表，相当于把块中数据打结实了，但会保持 high water mark;

alter table <tablespace_name> shrink space;

收缩表，降低 high water mark;

alter table <tablespace_name> shrink space cascade;

收缩表，降低 high water mark，并且相关索引也要收缩一下下。

alter index idxname shrink space;

回缩索引

补充

alter table move

移动high water mark，但是并不会释放空间。shrink space移动高水位线，并且释放空间。

测试

普通表

Sql脚本，改脚本会生成相应的语句

select'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||table_name||' shrink space;'||chr(10)from user_tables;

select'alter index '||index_name||' shrink space;'||chr(10)from user_indexes;

分区表的处理

进行shrink space时，发生ORA-10631，shrink space有限制

在表上建有索引（包括全文索引）会失败

Sql

select 'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||table_name||' shrink space;'||chr(10) from user_tables where ;

select 'alter index '||index_name||' shrink space;'||chr(10) from user_indexes where uniqueness='NONUNIQUE' ;

select 'alter table '||segment_name||' modify subpartition '||partition_name||' shrink space;'||chr(10) from user_segments where segment_type='TABLE SUBPARTITION' ';

Shrink的几个问题

1 shrink后index是否需要rebuild

SQL> create table my_test3 tablespace users as select * from all_objects where rownum<=20000; SQL> create index i_my_test3 on my_test3(object_id); SQL> delete from my_test3 where object_name like '%C%'; SQL> delete from my_test3 where object_name like '%U%'; SQL> alter table my_test3 enable row movement; SQL> alter table my_test3 shrink space; SQL> select index_name ,status from user_indexes where index_name='I_MY_TEST3'; INDEX_NAME STATUS ------------------------------ -------- I_MY_TEST3 VALID --在shrink在移动数据时，一起维护了index上相应的数据rowid的信息，如果move的时候，--index太多，shrink过程中需要维护index的成本也会比较高

2.shrink时对table的lock 对table进行shrink，对table的锁定，v$locked_objects,被加上了row-x(SX)的lock

3.2>索引段碎片整理

--把索引重建（碎片整理后，很多索引会变成unusable状态，必须让塔变成void状态）

FOR REC IN (SELECT INDEX_NAME FROM USER_INDEXES ) --WHERE STATUS='UNUSABLE'

LOOP

tmp_val:='ALTER INDEX '||REC.INDEX_NAME ||' REBUILD';

BEGIN

EXECUTE IMMEDIATE tmp_val;

DBMS_OUTPUT.put_line (tmp_val);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Error: ' || tmp_val || '!');

END;

END LOOP;

END;