按时间分区自动建分区表

在oracle11以前，分区维护需要手工维护，就是要手工建表分区。oracle11以后，就可以自动建时间分区了。

1、查看oracle 的版本号

select * from v$version;

我的oracle是11，支持自动建分区，查询结果如下：

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

2、用分区建表

（1）partition by range (CREATE_DATE) interval (numtoyMinterval (1,'MONTH'))根据CREATE_DATE自动按月建分区

create table T_ZNF
(
  ID          NUMBER(24) not null,
  CREATE_DATE DATE,
  STATE       VARCHAR2(10),
  T_DESC      VARCHAR2(255)
) tablespace TBS_APP_LOG --指定表空间（如果在下面的建分区时，指定表空间，后面自动建的分区表空间还是系统给的）
partition by range (CREATE_DATE) interval (numtoyMinterval (1,'MONTH'))(
  partition T_LOGTIME_main values less than (TO_DATE(' 2018-02-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);

 

插入数据

自动在指标的表空间下建立了分区表

 

按日自动建分区

create table MEAP_LOG
(
  LOGID     VARCHAR2(32) not null,
  LOGINNAME VARCHAR2(32),
  AREA      VARCHAR2(32),
  LOGTYPE   VARCHAR2(32) not null,
  MESSAGE   VARCHAR2(4000),
  DETAIL    CLOB,
  LOGTIME   DATE not null
)tablespace TBS_APP_LOG
partition by range (LOGTIME) INTERVAL(NUMTODSINTERVAL(1,'DAY'))
(
  partition T_LOGTIME_main values less than (TO_DATE(' 2018-2-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    
);

 

 

 （2）

-- Create table
create table T_ZNF
(
  ID          NUMBER(24) not null,
  CREATE_DATE DATE,
  STATE       VARCHAR2(10),
  T_DESC      VARCHAR2(255)
)
partition by range (CREATE_DATE)
(
  partition T_ZNF_201702 values less than (TO_DATE(' 2017-02-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TBS_ISAP
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_ZNF
  add constraint PK_T_ZNF_ID primary key (ID)
  using index 
  tablespace TBS_ISAP;
-- Create/Recreate indexes 
create index IDX_T_ZNF_CREATE_DATE on T_ZNF (CREATE_DATE)
  tablespace TBS_ISAP;

• 时间函数numtodsinterval和numtoyminterval：

两个函数都是时间的增加函数，只是增减的对象不一样（减是在数字前加符号‘-’）。

numtodsinterval:MONTH|YEAR;

numtoyminterval:DAY|HOUR|MINUTE|SECOND;

另：numtodsinterval 增减月份时和add_months函数相似，都是增减一个月，但有所不同。

numtodsinterval ：2.28 增减月份后，日期是该月的28号，如果该月对应的日期不存在，就会抛异常。如 计算后日期为2月30日，就会抛异常。

add_months：2.28 增减月份后，日期是该月的月底。

select  add_months(to_date('2007-02-28','yyyy-mm-dd'),1) from dual;--2007/3/31
select  add_months(to_date('2007-02-05','yyyy-mm-dd'),-1) from dual;--2007/1/5
select to_date('2007-02-28','yyyy-mm-dd')+numtoyminterval(12,'MONTH') from dual; --2008/2/28
select to_date('2007-01-30','yyyy-mm-dd')+numtoyminterval(1,'MONTH') from dual; --报错

 

 

 

• interval

根据年: INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))

根据月: INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))

根据天: INTERVAL(NUMTODSINTERVAL(1,'DAY'))

根据时分秒: NUMTODSINTERVAL( n, { 'DAY'|'HOUR'|'MINUTE'|'SECOND'})  

 

3、插入数据，验证是否自动建分区

插入如下数据

 

查看表的分区，发现表自动新建4个分区：SYS_P661、SYS_P662、SYS_P663、SYS_P664

3、表分区的查询

用表分区建表，以前的查询方式还是可以用的，但是用分区表查询效率更高。

查看分区的大小

SELECT *  FROM dba_segments t WHERE t.segment_name ='T_ZNF';

4、表分区的删除

删除分区表时，加 update global indexes 或者update indexes ，否则索引和表失效。

alter table t_znf truncate partition SYS_P663 update global indexes;--删除表分区的数据，表分区仍旧存在
alter table t_znf drop partition SYS_P663 update global indexes; --删除表分区及其数据

update global indexes 或者update indexes，就是重建索引。

下面我们不加 update global indexes 或者update indexes 删除一下表分区。

查看索引的状态

select status ,index_name,A.* from user_indexes A where index_name IN ('PK_T_ZNF_ID','IDX_T_ZNF_CREATE_DATE');

 执行删除表空间数据的语句

alter table t_znf truncate partition  SYS_P662;

再查看索引的状态，索引失效了

插入数据报错

进行表分析和索引分析时，也会报错

--表分析
  analyze table T_ZNF compute statistics;
--索引分析
analyze index PK_T_ZNF_ID compute statistics;

需将两个索引进行重建，索引和表才能正常使用。

alter index IDX_T_ZNF_CREATE_DATE rebuild /*online*/ tablespace TBS_ISAP;

将两个所以重建后，索引状态为VALID,数据也可以正常插入。