从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/
,如需转载,请注明出处,否则将追究法律责任。