ITPub博客

首页 > 应用开发 > IT综合 > 清除行迁移的例子

清除行迁移的例子

原创 IT综合 作者:fanhongjie 时间:2007-06-13 19:40:17 0 删除 编辑

从http://tech.ccidnet.com/art/1107/20070306/1030207_5.html转载:

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

[@more@]

从http://tech.ccidnet.com/art/1107/20070306/1030207_5.html转载:

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

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

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/73920/viewspace-918780/ ,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-05-22

  • 博文量
    111
  • 访问量
    685243