间隔分区 Interval Partition partition

分区(Partition)一直是Oracle数据库引以为傲的一项技术，正是分区的存在让Oracle高效的处理海量数据成为可能，在Oracle 11g中，分区技术在易用性和可扩展性上再次得到了增强。在10g的Oracle版本中，要对分区表做调整，尤其是对RANGE分区添加新的分区都需要DBA手动定期添加，或都使用存储过程进行管理。在11G的版本中的Interval Partition不再需要DBA去干预新分区的添加，Oracle会自动去执行这样的操作，减少了DBA的工作量。Interval Partition是Range分区的一个扩展。

使用Interval Partition也有一些限制：

You can only specify one partitioning key column, and it must be of NUMBER or DATE type.

Interval partitioning is not supported for index-organized tables.

You cannot create a domain index on an interval-partitioned table.

Interval partitioning is not supported at the subpartition level. ...
Interval Partition也可以创建复合分区：

Interval-range

Interval-hash

Interval-list

创建Interval分区表：

CREATE TABLE t
(
id number,
create_date date
)
partition by range(create_date) interval(numtoyminterval(1,'MONTH'))
(
partition p201303 values less than (date '2013-03-01'),
partition p201304 values less than (date '2013-04-01'),
partition p201305 values less than (date '2013-05-01')
);
这里就创建了一个每个月会自动生成一个分区的表。

interval函数以及numtoninterval函数的说明

SQL> SQL> select partition_name, interval, high_value from dba_tab_partitions where table_owner=user and table_nam e = 'T' order by partition_position;

PARTITION_NAME INT HIGH_VALUE
------------------------------ --- -------------------------------------------------------------------------------- -----
P201303 NO TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P201304 NO TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P201305 NO TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
--可以发现interval=NO，目前是指定的3个分区。

接着插入一笔非这个指定分区的数据：

SQL> insert into t values(1 ,date'2013-05-11');

1 row created.

SQL> commit;

Commit complete.

SQL> select partition_name, interval, high_value from dba_tab_partitions where table_owner=user and table_name = 'T' order by partition_position;

PARTITION_NAME INT HIGH_VALUE
------------------------------ --- -------------------------------------------------------------------------------------
P201303 NO TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P201304 NO TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P201305 NO TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P41 YES TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
发现自动建立了一个新分区sys_p41,interval值为YES,说明这个是inteval分区，high_value是TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

试一下删除分区：

SQL> alter table t drop partition P201304;

Table altered.

将P201304分区删除，这个分区的数据也会同时删除。

SQL> select partition_name, interval, high_value from dba_tab_partitions where table_owner=user and table_name = 'T' order by partition_position;

PARTITION_NAME INT HIGH_VALUE
------------------------------ --- -------------------------------------------------------------------------------------
P201303 NO TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P201305 NO TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P41 YES TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL> alter table t drop partition P201305;
alter table t drop partition P201305
*
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped
删除P201305的时候出错，提示最后一个range分区无法删除

那怎么删除这个分区？

步骤如下：

--先执行一下set interval(),使得interval的值都为NO
SQL> alter table t set interval (NUMTOYMINTERVAL(1,'month'));

Table altered.

SQL> select partition_name, interval, high_value from dba_tab_partitions where table_owner=user and table_name = 'T' order by partition_position;

PARTITION_NAME INT HIGH_VALUE
------------------------------ --- -------------------------------------------------------------------------------------
P201303 NO TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P201305 NO TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P41 NO TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
--再删除的时间就没有问题了
SQL> alter table t drop partition P201305;
Table altered.
--验证删除成功！
SQL> select partition_name, interval, high_value from dba_tab_partitions where table_owner=user and table_name = 'T' order by partition_position;
PARTITION_NAME INT HIGH_VALUE
------------------------------ --- -------------------------------------------------------------------------------------
P201303 NO TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P41 NO TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

统计审计基表AUD$UNIFIED ，不允许直接更改，且默认是按月分区。
通过以下 DBMS_AUDIT_MGMT 更改分区为1天

BEGINDBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL(interval_number       => 1,interval_frequency    => 'DAY');END;
/SQL> select table_name,partition_name,INTERVAL,HIGH_VALUE,HIGH_VALUE_LENGTH from dba_tab_partitions where table_name='AUD$UNIFIED';TABLE_NAME                     PARTITION_NAME                 INT HIGH_VALUE                               HIGH_VALUE_LENGTH
------------------------------ ------------------------------ --- ---------------------------------------- -----------------
AUD$UNIFIED                    SYS_P306                       NO  TIMESTAMP' 2017-09-01 00:00:00'                         31SYS@ORCLCDB>col owner  for a20 
SYS@ORCLCDB>col INTERVAL  for a50
SYS@ORCLCDB>select owner,table_name,interval,partitioning_type,partition_count,def_tablespace_name from dba_part_Tables where owner='AUDSYS';OWNER                TABLE_NAME           INTERVAL                                           PARTITIONING_TYPE  PARTITION_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- -------------------------------------------------- ------------------ --------------- ------------------------------------------------------------
AUDSYS               AUD$UNIFIED          NUMTODSINTERVAL(1, 'DAY')

（五）关于间隔分区的常见问题

（5.1）如何将现有普通表转换为间隔分区

可以使用如下命令将现有的范围分区表转换为间隔分区表，注意，仅仅支持范围分区表:

ALTER TABLE <table_name> SET INTERVAL <number or interval expression>;

（5.2）如何为现有表设置新的间隔

可以使用如下命令修改间隔,该操作不会使index不可用：

ALTER TABLE <table_name> SET INTERVAL(interval express);

（5.3）如何为间隔分区指定/更改表空间

INTERVAL子句的STORE IN用于指定创建间隔分区的表空间。如果指定了表空间列表，将以循环方式在这些表空间上创建间隔分区。

INTERVAL expr [STORE IN (tablespace1,[tablespace2,...])]

需要注意的是，在INTERVAL子句中使用”PARTITION“创建的范围分区需要指出表空间，否则会将范围分区创建到用户默认的表空间中，而不是[STORE IN]的表空间中。

对于已经创建的分区，可以使用以下命令将其移动到特定的表空间：

ALTER TABLE <table_name> MOVE PARTITION <partition_name> TABLESPACE <tablespace_name>;

（5.4）自动创建的间隔分区的名称是什么

数据库创建的间隔分区的名称是系统自动生成的，可以通过dba_tab_partition视图查看。目前无法为分区指定创建模板，但是可以重命名分区。

例子：自动创建的表空间的名称

insert into INTERVAL_NUMBER_TABLE01 values (201209, 'name09'); insert into INTERVAL_NUMBER_TABLE01 values (201210, 'name10'); insert into INTERVAL_NUMBER_TABLE01 values (201211, 'name11'); insert into INTERVAL_NUMBER_TABLE01 values (201212, 'name12'); insert into INTERVAL_NUMBER_TABLE01 values (201301, 'name01'); insert into INTERVAL_NUMBER_TABLE01 values (201402, 'name02'); insert into INTERVAL_NUMBER_TABLE01 values (201503, 'name03');  SQL> select  table_owner,table_name,partition_name,high_value,tablespace_name,interval   2 from    dba_tab_partitions   3 where   table_name = 'INTERVAL_NUMBER_TABLE01';  TABLE_OWNER    TABLE_NAME                     PARTITION_NAME     HIGH_VALUE    TABLESPACE_NAME   INTERVAL  LIJIAMAN       INTERVAL_NUMBER_TABLE01        PARTITION10        10            USERS             NO LIJIAMAN       INTERVAL_NUMBER_TABLE01        SYS_P54            20            TBS02             YES LIJIAMAN       INTERVAL_NUMBER_TABLE01        SYS_P55            110           TBS02             YES LIJIAMAN       INTERVAL_NUMBER_TABLE01        SYS_P56            120           TBS03             YES LIJIAMAN       INTERVAL_NUMBER_TABLE01        SYS_P57            130           TBS01             YES

（5.5）使用DBMS_METADATA.GET_DDL检索表时，为什么缺少系统生成的间隔分区？

"DBMS_METADATA.GET_DDL"只提供用户手段创建的分区，而不提供系统自动生成的分区。以下为测试例子：

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','INTERVAL_NUMBER_TABLE01','LIJIAMAN') FROM DUAL; DBMS_METADATA.GET_DDL('TABLE',  CREATE TABLE "LIJIAMAN"."INTERVAL_NUMBER_TABLE01"     (  "EMPLOYEE_ID" NUMBER,        "EMPLOYEE_NAME" VARCHAR2(20),        "BIRTHDAY" DATE    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255    STORAGE(   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "USERS"    PARTITION BY RANGE ("EMPLOYEE_ID") INTERVAL (10) STORE IN ("TBS01", "TBS02", "TBS03")   (PARTITION "PARTITION10"  VALUES LESS THAN (10) SEGMENT CREATION IMMEDIATE    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255    NOCOMPRESS LOGGING    STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "USERS" )

如果要输出系统自动创建的分区的脚本，需将DBMS_METDATA的EXPORT参数设置为true

exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'EXPORT',true);

（六）关于间隔分区的bug

以前在使用分区表时，遇到过由延迟段创建（deferred_segment_creation）引起的段分配异常问题。间隔分区也有类似的bug，使用需要谨慎。

（七）间隔分区的利弊思考

好处：间隔分区通过系统自动创建分区，减少了DBA的日常运维工作，避免了ORA-14400这类错误，每年年终不需要为下一年手动创建分区，想想还是挺开心的；

坏处：因为系统自动创建分区名称，我们无法通过分区名称来判断数据的存放位置，增加了后期的维护难度。举个例子，如果是DBA手动维护，假设表的分区”part_201901“存储的就是2019年1月的数据，假如我们想要删除1月份的数据，直接删除该分区即可，如果数据库里面有500个类似的表，直接写批量脚本”ALTER TABLE <table_name> DROP PARTITION part_201901“就将全部表的1月份的数据删除了，但是对于系统自动创建的分区，在不同的表里面，2019年1月的数据对应的分区名不同，自然无法使用脚本批量删除，即使有脚本，也非常麻烦。