Oracle中的chained rows

The difference between utlchain.sql and utlchn1.sql is the column head_rowid data type:

::::::::::::::
utlchain.sql
::::::::::::::

create table CHAINED_ROWS (
  owner_name varchar2(30),
  table_name varchar2(30),
  cluster_name varchar2(30),
  partition_name varchar2(30),
  subpartition_name varchar2(30),
  *head_rowid rowid,*
  analyze_timestamp date
);

::::::::::::::
utlchn1.sql
::::::::::::::

create table CHAINED_ROWS (
  owner_name varchar2(30),
  table_name varchar2(30),
  cluster_name varchar2(30),
  partition_name varchar2(30),
  subpartition_name varchar2(30),
  *head_rowid         urowid,*
  analyze_timestamp date
);

ROWID and UROWID Datatypes

Oracle uses a ROWID datatype to store the address (rowid) of every row in the database.

Physical rowids store the addresses of rows in ordinary tables (excluding index-organized tables), clustered tables, table partitions and subpartitions, indexes, and index partitions and subpartitions.

Logical rowids store the addresses of rows in index-organized tables.
A single datatype called the universal rowid, or UROWID, supports both logical and physical rowids, as well as rowids of foreign tables such as non-Oracle tables accessed through a gateway.

ROWID:
Physical rowids store the addresses of rows in ordinary tables (excluding index-organized tables), clustered tables, table partitions and subpartitions, indexes, and index partitions and subpartitions.

UROWID
U as Universal, supports both logical and physical rowids, as well as rowids of foreign tables such as non-Oracle tables accessed through a gateway. UROWIDs can save both "physical" as well as "logical" ROW IDs.

So if you have IOT in your database use utlchn1.sql to create the CHAINED_ROWS or else you will get ORA-01496: specified chain row table form incorrect

How to resolve

1. Create an intermediate table with the same columns as the existing table to hold the migrated and chained rows:

   CREATE TABLE int_order_hist
      AS SELECT *
         FROM order_hist
         WHERE ROWID IN
            (SELECT HEAD_ROWID
               FROM CHAINED_ROWS
               WHERE TABLE_NAME = 'ORDER_HIST');
   

2. Delete the migrated and chained rows from the existing table:

   DELETE FROM order_hist
      WHERE ROWID IN
         (SELECT HEAD_ROWID
            FROM CHAINED_ROWS
            WHERE TABLE_NAME = 'ORDER_HIST');
   

3. Insert the rows of the intermediate table into the existing table:

   INSERT INTO order_hist
      SELECT *
      FROM int_order_hist;
   

4. Drop the intermediate table:

   DROP TABLE int_order_history;
   

5. Delete the information collected in step 1 from the output table:

   DELETE FROM CHAINED_ROWS
      WHERE TABLE_NAME = 'ORDER_HIST';