1.执行
utlchain.sql脚本
SQL> CREATE TABLE chained_rows (
2 owner_name VARCHAR2(30),
3 table_name VARCHAR2(30),
4 cluster_name VARCHAR2(30),
5 partition_name VARCHAR2(30),
6 head_rowid ROWID,
7 analyze_timestamp DATE );
创建chained_rows表，然后分析
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
2.对行迁移的处理
a).exp/drop/imp table
b).alter table emp move ...;
c).查找迁移的行，拷贝迁移的行到一个新表，删除原记录，然后从新表将行拷贝到原表
3.相关的脚本
/* Get the name of the table with migrated rows */
ACCEPT table_name PROMPT 'Enter the name of the table with migrated rows: '
/* Clean up from last execution */
SET ECHO OFF
DROP TABLE migrated_rows;
DROP TABLE chained_rows;
/* Create the CHAINED_ROWS table */
@?/rdbms/admin/utlchain
SET ECHO ON
SPOOL fix_mig
/* List the chained & migrated rows */
ANALYZE TABLE &table_name LIST CHAINED ROWS;
------------------------------------------------------------------------
/* Copy the chained/migrated rows to another table */
CREATE TABLE migrated_rows AS
SELECT orig.*
FROM &table_name orig, chained_rows cr
WHERE orig.rowid = cr.head_rowid
AND cr.table_name = upper('&table_name');
/* Delete the chained/migrated rows from the original table */
DELETE FROM &table_name
WHERE rowid IN (
SELECT head_rowid
FROM chained_rows);
/* Copy the chained/migrated rows back into the original table */
INSERT INTO &table_name
SELECT *
FROM migrated_rows;
SPOOL OFF