Oracle中行迁移和行链接的清除及检测

核心提示： 以下是一个具体在生产数据库上清除行迁移的例子，在这之前已经调整过表的pctfree参数至一个合适的值了：SQL>@$ORACLE_HOME/rdbms/admin/utlchain.sqlTable created.SQL> ANALYZE TABLE CUSTOMER LIS

以下是一个具体在生产数据库上清除行迁移的例子，在这之前已经调整过表的pctfree参数至一个合适的值了：

SQL>@$ORACLE_HOME/rdbms/admin/utlchain.sql
Table created.
SQL> ANALYZE TABLE CUSTOMER LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL>SELECT count(*) from chained_rows；
TABLE_NAME COUNT(*)
CUSTOMER 21306
1 rows selected.

查看在CUSTOMER表上存在的限制：

SQL>select CONSTRAINT_NAME,CONSTRAINT_TYPE,
TABLE_NAME from USER_CONSTRAINTS where TABLE_NAME='CUSTOMER';
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - --
PK_CUSTOMER1 P CUSTOMER
SQL>select CONSTRAINT_NAME,CONSTRAINT_TYPE,
TABLE_NAME from USER_CONSTRAINTS
where R_CONSTRAINT_NAME='PK_CUSTOMER1';
no rows selected
SQL> CREATE TABLE CUSTOMER_temp AS
SELECT * FROM CUSTOMER WHERE rowid IN
(SELECT head_rowid FROM chained_rows
WHERE table_name = 'CUSTOMER');
Table created.
SQL>select count(*) from CUSTOMER;
COUNT(*)
----------
338299
SQL> DELETE CUSTOMER WHERE rowid IN
(SELECT head_rowid
FROM chained_rows
WHERE table_name = 'CUSTOMER');
21306 rows deleted.
SQL> INSERT INTO CUSTOMER SELECT * FROM CUSTOMER_temp;
21306 rows created.
SQL> DROP TABLE CUSTOMER_temp;
Table dropped.
SQL> commit;
Commit complete.
SQL> select count(*) from CUSTOMER;
COUNT(*)
----------
338299
SQL> truncate table chained_rows;
Table truncated.
SQL> ANALYZE TABLE CUSTOMER LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows;
COUNT(*)
----------
0

以上整个清除两万多行的行迁移过程在三分钟左右，而且全部都在联机的状态下完成，基本上不会对业务有什么影响，唯一就是在要清除行迁移的表上不能有对外键的限制，否则就不能采用这个方法去清除了。