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

上一篇 / 下一篇 2008-02-29 20:20:52 / 个人分类：技术文章

以下是一个具体在生产数据库上清除行迁移的例子，在这之前已经调整过表的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