检测迁移和链接
使用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