date字段类型的分区表切换为自动分区表

目录

• 适用范围
• 问题概述
• 问题原因
• 解决方案

适用范围

oracle 11g+

问题概述

字段date类型的range分区(按月划分分区表)，历史的分区表都是人工新增分区，先要改为自动分区，减少漏加分区导致的异常。

问题原因

查看当前用户下存在的分区表

解决方案

01、查看当前用户下存在的自动分区表

   select distinct(table_name) from dba_tab_partitions where table_owner='gwx' and table_name='DDL_EVENTS'  and interval = 'YES';

02、查看max分区是否存在数据

  
select count(*) from gwx.DDL_EVENTS  partition(PART_MAX);

03、存在数据则数据备份出来

  
create table gwx.DDL_EVENTS_bak as select * from gwx.ddl_ecents partition(PART_MAX);

04、删除max分区

SQL> alter table gwx.DDL_EVENTS drop partition  PART_MAX;
alter table gwx.DDL_EVENTS drop partition  PART_MAX
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'gwx.DTR_DDLEVENTS'
ORA-00604: error occurred at recursive SQL level 1
ORA-30512: cannot modify gwx.DDL_EVENTS more than once in a transaction
ORA-06512: at line 18

05、 禁触发器

SQL> alter trigger gwx.DTR_DDLEVENTS disable;

Trigger altered.

SQL>  alter table gwx.DDL_EVENTS drop partition  PART_MAX;

Table altered.

06、设置月度自动添加分区，开启触发器

SQL>  ALTER TABLE gwx.DDL_EVENTS  SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH'));

Table altered.

SQL> alter trigger gwx.DTR_DDLEVENTS enable;

Trigger altered.