從轉載：

以下是一個具體在生產資料庫上清除行遷移的例子，在這之前已經調整過表的pctfree引數至一個合適的值了：

[@more@]

從轉載：

以下是一個具體在生產資料庫上清除行遷移的例子，在這之前已經調整過表的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

以上整個清除兩萬多行的行遷移過程在三分鐘左右，而且全部都在聯機的狀態下完成，基本上不會對業務有什麼影響，唯一就是在要清除行遷移的表上不能有對外來鍵的限制，否則就不能採用這個方法去清除了。

清除行遷移的例子

相關文章