以下是一个具体在生产数据库上清除行迁移的例子，在这之前已经调整过表的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
|
以上整个清除两万多行的行迁移过程在三分钟左右，而且全部都在联机的状态下完成，基本上不会对业务有什么影响，唯一就是在要清除行迁移的表上不能有对外键的限制，否则就不能采用这个方法去清除了。
（责任编辑 火凤凰 sunsj@51cto.com QQ：34067741 TEL：（010）68476636-8007）
