处理表的行迁移的问题

检测迁移和链接

使用ANALYZE 命令检测迁移和链接：

SQL> ANALYZE TABLE oe.orders COMPUTE STATISTICS;
Table Analyzed.
SQL> SELECT num_rows, avg_row_len, chain_cnt
2 FROM DBA_TABLES
3 WHERE table_name='ORDERS';
NUM_ROWS   AVG_ROW_LEN   CHAIN_CNT
---------- ----------- ----------
1171         67          83

使用Statspack/AWR 检测迁移和链接：

Statistic                                      Total       per Second    per Trans
table fetch continued row                      34,964            0.7          0.2

先执行utlchain.sql脚本来创建CHAINED_ROWS表

SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlchain.sql
选择已迁移的行
SQL> ANALYZE TABLE oe.orders LIST CHAINED ROWS;
Table analyzed.
SQL> SELECT owner_name, table_name, head_rowid
2 FROM chained_rows
3 WHERE table_name = 'ORDERS';
OWNER_NAME TABLE_NAME HEAD_ROWID
---------- ---------- ------------------
SALES ORDER_HIST AAAAluAAHAAAAA1AAA
SALES ORDER_HIST AAAAluAAHAAAAA1AAB

消除已迁移的行
导出/导入：
– 导出表
– 删除或截断表
– 导入表
MOVE 表命令：

– ALTER TABLE EMPLOYEES MOVE

重新定义联机表
复制已迁移的行：
– 使用ANALYZE 查找迁移的行
– 将迁移的行复制到新表
– 从原始表删除迁移行
– 将行从新表复制到原始表
下面是采用MOVE表命令的方式

-- Created on 2012-5-24 by ADMINISTRATOR
declare
 cursor lj is SELECT table_name, num_rows, avg_row_len, chain_cnt FROM DBA_TABLES a where a.owner='SSCP2'
 and a.chain_cnt>0;
 cursor sy(p_tablename in varchar2) is
 select * from dba_indexes a where a.owner='SSCP2' and a.table_name=''||''||p_tablename||''||'';
 sql_c varchar2(200):=null;
 cl_flag number(2):=1;
begin
   for r in lj loop
     sql_c:=null;
     sql_c:='alter table sscp2.'||r.table_name||' move';
     begin
     execute immediate ''||sql_c;
     exception
       when others then
       dbms_output.put_line('执行表移动出错的表'||r.table_name);
       cl_flag:=0;
     end ;
     if cl_flag=1 then
         for l in sy(r.table_name) loop
           dbms_output.put_line(l.index_name);
           sql_c:=null;
           sql_c:='alter index sscp2.'||l.index_name||' rebuild';
           begin
           execute immediate ''||sql_c;
           exception
           when others then
           dbms_output.put_line('重建出错的索引'||l.index_name);
           end ;
         end loop;
         sql_c:='analyze table sscp2.'||r.table_name||' compute statistics';
         begin
          execute immediate ''||sql_c;
         exception
           when others then
           dbms_output.put_line('分析表出错'||r.table_name);
         end ;
      end if;
   end loop;
end;
SQL> SELECT table_name, num_rows, avg_row_len, chain_cnt FROM DBA_TABLES a where a.owner='SSCP2'
  2   and a.chain_cnt>0;

TABLE_NAME                       NUM_ROWS AVG_ROW_LEN  CHAIN_CNT
------------------------------ ---------- ----------- ----------
ABSB                              2479525         114       3041
ABS8                             29230869         149     195428
IDS2                               453402          60        132
ICSW                                78087          37         37
ICS1                               759167          49       5521
IC12                               729809         111          9
IC10                               256770         170         62
ADS3                             17472005         186       2221
ADS1                               544695         130         53
ACSN                                39821          90          4
ACS6                              1940197          82        162