在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'))
);

 

插入数据

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

 

按日自动建分区

 1 create table MEAP_LOG
 2 (
 3   LOGID     VARCHAR2(32) not null,
 4   LOGINNAME VARCHAR2(32),
 5   AREA      VARCHAR2(32),
 6   LOGTYPE   VARCHAR2(32) not null,
 7   MESSAGE   VARCHAR2(4000),
 8   DETAIL    CLOB,
 9   LOGTIME   DATE not null
10 )tablespace TBS_APP_LOG
11 partition by range (LOGTIME) INTERVAL(NUMTODSINTERVAL(1,'DAY'))
12 (
13   partition T_LOGTIME_main values less than (TO_DATE(' 2018-2-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
14     
15 );

 

 

 (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,数据也可以正常插入。