分区(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天
BEGIN
DBMS_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' 31
SYS@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月的数据对应的分区名不同,自然无法使用脚本批量删除,即使有脚本,也非常麻烦。