ORACLE清理大表历史数据

来源：这里教程网时间：2026-03-03 18:58:57 作者：

清理大表历史数据通过将非分区表Online Redefinition转换为以删除条件为Range范围分区的Partition-ed Table，再直接Truncate Partiton的方法来加速历史数据的清理，同时又不影响业务的在线。 create table order_history as select * from dba_objects; select count(*) from order_history; select count(*) from order_history where created < sysdate-365;

COUNT(*) ———- 49983 收集统计信息 exec dbms_stats.gather_table_stats('SCOTT','ORDER_HISTORY');

create table order_history_int( OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(128), SUBOBJECT_NAME VARCHAR2(30), OBJECT_ID NUMBER, DATA_OBJECT_ID NUMBER, OBJECT_TYPE VARCHAR2(19), CREATED DATE, LAST_DDL_TIME DATE, TIMESTAMP VARCHAR2(19), STATUS VARCHAR2(7), TEMPORARY VARCHAR2(1), GENERATED VARCHAR2(1), SECONDARY VARCHAR2(1)) partition by range(created) ( partition p1 values less than (to_date('2010-01-01','YYYY-MM-DD')) tablespace users, partition p2 values less than (to_date('2011-01-01','YYYY-MM-DD')) tablespace users, partition p3 values less than (to_date('2012-01-01','YYYY-MM-DD')) tablespace users, partition p4 values less than (to_date('2013-01-01','YYYY-MM-DD')) tablespace users, partition p5 values less than (maxvalue) tablespace users); begin dbms_redefinition.can_redef_table(uname => 'SCOTT', tname => 'ORDER_HISTORY', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID); end; /

begin DBMS_REDEFINITION.START_REDEF_TABLE(uname => 'SCOTT', orig_table => 'ORDER_HISTORY', int_table => 'ORDER_HISTORY_INT', options_flag => DBMS_REDEFINITION.cons_use_rowid); end; /

SQL> select count(*) from ORDER_HISTORY_INT;

COUNT(*) ———- 50731

DECLARE num_errors PLS_INTEGER; BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'SCOTT', orig_table => 'ORDER_HISTORY', int_table => 'ORDER_HISTORY_INT', copy_indexes => 0, copy_triggers => TRUE, copy_constraints => FALSE, copy_privileges => TRUE, ignore_errors => FALSE, num_errors => num_errors, copy_statistics => TRUE); END; /

begin dbms_redefinition.finish_redef_table(uname => 'SCOTT', orig_table => 'ORDER_HISTORY', int_table => 'ORDER_HISTORY_INT'); end; /

alter table ORDER_HISTORY truncate partition p1; alter table ORDER_HISTORY truncate partition p2; alter table ORDER_HISTORY truncate partition p3; SQL> select count(*) from ORDER_HISTORY;