检查、处理行链接！

一、使用dbms_advisor段顾问建议来检查行链接

1、首先模拟行链接：

u1@ORCL> drop table big_table;表已删除。u1@ORCL> create table big_table(col varchar2(4000)) pctfree 0;表已创建。u1@ORCL> select table_name,pct_free from user_tables where table_name='BIG_TABLE';TABLE_NAME                       PCT_FREE------------------------------ ----------BIG_TABLE                               0u1@ORCL> insert /*+ append */ into big_table select object_name from dba_objects;已创建72600行。u1@ORCL> commit;提交完成。u1@ORCL> select bytes/1024/1024 from user_segments where segment_name='BIG_TABLE';BYTES/1024/1024---------------              3u1@ORCL> update big_table set col=rpad(col,4000,'a');已更新72600行。u1@ORCL> commit;提交完成。u1@ORCL> select bytes/1024/1024 from user_segments where segment_name='BIG_TABLE';BYTES/1024/1024---------------            576

2、使用dbms_advisor段顾问建议手动检查，参考：http://blog.csdn.net/zq9017197/article/details/13622139。然后在查询：

u1@ORCL> SELECT  2   'Task Name        : ' || f.task_name  || chr(10) ||  3   'Segment Name     : ' || o.attr2      || chr(10) ||  4   'Segment Type     : ' || o.type       || chr(10) ||  5   'Partition Name   : ' || o.attr3      || chr(10) ||  6   'Message          : ' || f.message    || chr(10) ||  7   'More Info        : ' || f.more_info TASK_ADVICE  8  FROM dba_advisor_findings f  9      ,dba_advisor_objects  o 10  WHERE o.task_id = f.task_id 11  AND o.object_id = f.object_id 12  AND f.owner=user 13  AND f.task_name like 'BIG_TABLE Advice' 14  ORDER BY f.task_name;TASK_ADVICE-----------------------------------------------------------------------------------------------------------------------------------Task Name        : BIG_TABLE AdviceSegment Name     : BIG_TABLESegment Type     : TABLEPartition Name   :Message          : 对象具有可通过重组删除的链接行。More Info        : 通过重组可以删除百分之 64 的链接行。Task Name        : BIG_TABLE AdviceSegment Name     : BIG_TABLESegment Type     : TABLEPartition Name   :Message          : 启用表 U1.BIG_TABLE 的行移动并执行收缩, 估计可以节省 11808913 字节。More Info        : 分配空间:603979776: 已用空间:592170863: 可回收空间:11808913:

3、move之后，再检查，再查询：

u1@ORCL> alter table big_table move;表已更改。u1@ORCL> SELECT  2   'Task Name        : ' || f.task_name  || chr(10) ||  3   'Segment Name     : ' || o.attr2      || chr(10) ||  4   'Segment Type     : ' || o.type       || chr(10) ||  5   'Partition Name   : ' || o.attr3      || chr(10) ||  6   'Message          : ' || f.message    || chr(10) ||  7   'More Info        : ' || f.more_info TASK_ADVICE  8  FROM dba_advisor_findings f  9      ,dba_advisor_objects  o 10  WHERE o.task_id = f.task_id 11  AND o.object_id = f.object_id 12  AND f.owner=user 13  AND f.task_name like 'BIG_TABLE Advice' 14  ORDER BY f.task_name;TASK_ADVICE-----------------------------------------------------------------------------------------------------------------------------------Task Name        : BIG_TABLE AdviceSegment Name     : BIG_TABLESegment Type     : TABLEPartition Name   :Message          : 此对象中的空闲空间小于 10MB。More Info        : 分配空间:301989888: 已用空间:297333300: 可回收空间:4656588:

二、使用analyze来处理行链接

1、模拟行链接同样是前面的脚本，假设第一步已经做了。分析行链接：

u1@ORCL> @?/rdbms/admin/utlchain.sql    --这里是创建了一个chained_rows表表已创建。u1@ORCL> analyze table big_table list chained rows;表已分析。u1@ORCL> select count(*) from chained_rows where table_name='BIG_TABLE';     --有72343行行链接了  COUNT(*)----------     72343

2、处理行链接，使用analyze的好处是可以只处理行链接那一部分数据，不用整个表move。

u1@ORCL> create table big_table_tmp as select * from big_table where rowid in(  2   select head_rowid from chained_rows where table_name='BIG_TABLE');表已创建。u1@ORCL> delete from big_table where rowid in(select head_rowid from chained_rows where table_name='BIG_TABLE');已删除72343行。u1@ORCL> insert into big_table select * from big_table_tmp;已创建72343行。u1@ORCL> commit;提交完成。u1@ORCL> analyze table big_table list chained rows;表已分析。u1@ORCL> select count(*) from chained_rows where table_name='BIG_TABLE';  COUNT(*)----------     72343u1@ORCL> truncate table chained_rows;表被截断。u1@ORCL> analyze table big_table list chained rows;表已分析。u1@ORCL> select count(*) from chained_rows where table_name='BIG_TABLE';  COUNT(*)----------         0