检查行链接和行迁移的方法

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