--解决行链接问题SQL> create tablespace hzqtbs datafile '/u01/app/oracle/oradata/prod/hzqtbs.dbf' size 600m;Tablespace created.SQL> create user hzq identified by hzq default tablespace hzqtbs quota unlimited on hzqtbs account unlock;User created.SQL> grant create session to hzq;Grant succeeded.SQL> grant resource to hzq;Grant succeeded.SQL> conn hzq/hzqConnected.create table test (id number,name varchar2(100));beginfor i in  1..1000000 loopinsert into test values(i,'user'||i);commit;end loop;end;/--通过更新使字段边长生成行链接update test set name='userldsajflsadjfldsjfldsafdsahflsahfldsaflsajflkdajfjflajfldsakjflkdsajfldsajflsafjlasfadsafdsafsad';--删除，重新收集手动运行顾问DECLAREBEGINdbms_advisor.delete_task (task_name => 'MYTEST Advice');END;/DECLAREmy_task_id number;obj_id number;my_task_name varchar2(100);my_task_desc varchar2(500);BEGINmy_task_name := 'MYTEST Advice';my_task_desc := 'Manual Segment Advisor Run';----------- Step 1 create a task---------dbms_advisor.create_task (advisor_name => 'Segment Advisor',task_id => my_task_id,task_name => my_task_name,task_desc => my_task_desc);----------- Step 2 create a object---------dbms_advisor.create_object (task_name => my_task_name,object_type => 'TABLE',attr1 => 'HZQ',attr2 => 'TEST',attr3 => NULL,attr4 => NULL,attr5 => NULL,object_id => obj_id);----------- Step 3---------dbms_advisor.set_task_parameter(task_name => my_task_name,parameter => 'recommend_all',value => 'TRUE');----------- Step 4---------dbms_advisor.execute_task(my_task_name);END;/--查看看行链接问题SELECT'Task Name : ' || f.task_name || chr(10) ||'Segment Name : ' || o.attr2 || chr(10) ||'Segment Type : ' || o.type || chr(10) ||'Partition Name : ' || o.attr3 || chr(10) ||'Message : ' || f.message || chr(10) ||'More Info : ' || f.more_info TASK_ADVICEFROM dba_advisor_findings f,dba_advisor_objects oWHERE o.task_id = f.task_idAND o.object_id = f.object_idORDER BY f.task_name;select'Task Name : ' || f.task_name || chr(10) ||'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) ||'Segment Name : ' || o.attr2 || chr(10) ||'Segment Type : ' || o.type || chr(10) ||'Partition Name : ' || o.attr3 || chr(10) ||'Message : ' || f.message || chr(10) ||'More Info : ' || f.more_info || chr(10) ||'------------------------------------------------------' AdviceFROM dba_advisor_findings f,dba_advisor_objects o,dba_advisor_executions eWHERE o.task_id = f.task_idAND o.object_id = f.object_idAND f.task_id = e.task_idAND e. execution_start > sysdate - 1AND e.advisor_name = 'Segment Advisor'ORDER BY f.task_name;ADVICE----------------------------Task Name : MYTEST AdviceStart Run Time : 18-may-16 22:53Segment Name : TESTSegment Type : TABLEPartition Name :Message : The free space in the object is less than 10MB.More Info : Allocated Space:167772160: Used Space:165481507: Reclaimable Space :2290653:------------------------------------------------------Task Name : MYTEST AdviceStart Run Time : 18-may-16 22:53ADVICE-------------------------------------------------------Segment Name : TESTSegment Type : TABLEPartition Name :Message : The object has chained rows that can be removed by re-org.More Info : 46 percent chained rows can be removed by re-org.--有46%的行链接可以被remove，解决行链接的方法是通过move表来实现，但是表move后表的索引会失效，需要重建索引，行移动需要很多的表空间，建议表空间空余空间大于表的大小。SQL> create index idx_test on test(id);Index created.SQL> conn / as sysdbaConnected.SQL> select owner,index_name,status from dba_indexes where table_name='TEST';OWNER                   INDEX_NAME              STATUS------------------------------ ------------------------------ --------HZQ                   IDX_TEST               UNUSABLESQL> alter index hzq.IDX_TEST rebuild;Index altered.--删除，重新收集手动运行顾问DECLAREBEGINdbms_advisor.delete_task (task_name => 'MYTEST Advice');END;/DECLAREmy_task_id number;obj_id number;my_task_name varchar2(100);my_task_desc varchar2(500);BEGINmy_task_name := 'MYTEST Advice';my_task_desc := 'Manual Segment Advisor Run';----------- Step 1 create a task---------dbms_advisor.create_task (advisor_name => 'Segment Advisor',task_id => my_task_id,task_name => my_task_name,task_desc => my_task_desc);----------- Step 2 create a object---------dbms_advisor.create_object (task_name => my_task_name,object_type => 'TABLE',attr1 => 'HZQ',attr2 => 'TEST',attr3 => NULL,attr4 => NULL,attr5 => NULL,object_id => obj_id);----------- Step 3---------dbms_advisor.set_task_parameter(task_name => my_task_name,parameter => 'recommend_all',value => 'TRUE');----------- Step 4---------dbms_advisor.execute_task(my_task_name);END;/SQL> select'Task Name : ' || f.task_name || chr(10) ||'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) ||'Segment Name : ' || o.attr2 || chr(10) ||'Segment Type : ' || o.type || chr(10) ||'Partition Name : ' || o.attr3 || ch  2    3    4    5    6  r(10) ||'Message : ' || f.message || chr(10) ||'More Info : ' || f.more_info || chr(10) ||'------------------------------------------------------' AdviceFROM dba_advisor_findings f,dba_advisor_objects o,dba_advisor_executions eWHERE o.task_id = f.ta  7    8    9   10   11   12   13  sk_idAND o.object_id = f.object_idAND f.task_id = e.task_idAND e. execution_start > sysdate - 1AND e.advisor_name = 'Segment Advisor'ORDER BY f.task_name; 14   15   16   17   18  ADVICE----------------------------------------------------Task Name : MYTEST AdviceStart Run Time : 18-may-16 23:07Segment Name : TESTSegment Type : TABLEPartition Name :Message : The free space in the object is less than 10MB.More Info : Allocated Space:134217728: Used Space:126002153: Reclaimable Space :8215575:--------------------------------------------------------结果没有显示行链接问题