ORACLE 创建分区表

--  创建 tablespace
create tablespace OPT_TIME_01
datafile 'E:\TABLESPACE\OPT_TIME_01.DBF' size 10m autoextend on next 10m maxsize unlimited;

create tablespace OPT_TIME_02
datafile 'E:\TABLESPACE\OPT_TIME_02.DBF' size 10m autoextend on next 10m maxsize unlimited;

create tablespace OPT_TIME_03
datafile 'E:\TABLESPACE\OPT_TIME_03.DBF' size 10m autoextend on next 10m maxsize unlimited;

create tablespace OPT_TIME_04
datafile 'E:\TABLESPACE\OPT_TIME_04.DBF' size 10m autoextend on next 10m maxsize unlimited;

create tablespace OPT_TIME_05
datafile 'E:\TABLESPACE\OPT_TIME_05.DBF' size 10m autoextend on next 10m maxsize unlimited;

create tablespace OPT_TIME_06
datafile 'E:\TABLESPACE\OPT_TIME_06.DBF' size 10m autoextend on next 10m maxsize unlimited;

create tablespace OPT_TIME_07
datafile 'E:\TABLESPACE\OPT_TIME_07.DBF' size 10m autoextend on next 10m maxsize unlimited;

create tablespace OPT_TIME_08
datafile 'E:\TABLESPACE\OPT_TIME_08.DBF' size 10m autoextend on next 10m maxsize unlimited;

create tablespace OPT_TIME_09
datafile 'E:\TABLESPACE\OPT_TIME_09.DBF' size 10m autoextend on next 10m maxsize unlimited;

create tablespace OPT_TIME_10
datafile 'E:\TABLESPACE\OPT_TIME_10.DBF' size 10m autoextend on next 10m maxsize unlimited;

create tablespace OPT_TIME_11
datafile 'E:\TABLESPACE\OPT_TIME_11.DBF' size 10m autoextend on next 10m maxsize unlimited;

create tablespace OPT_TIME_12
datafile 'E:\TABLESPACE\OPT_TIME_12.DBF' size 10m autoextend on next 10m maxsize unlimited;

create tablespace OPT_TIME_other
datafile 'E:\TABLESPACE\OPT_TIME_other.DBF' size 10m autoextend on next 10m maxsize unlimited;


------分区表
create table SYS_LOG
(
  ID          VARCHAR2(100) not null,
  OPT_CONTENT VARCHAR2(4000),
  OPT_TIME    VARCHAR2(25),
  USER_CODE   VARCHAR2(36),
  OPT_IP      VARCHAR2(20),
  LOG_TYPE    VARCHAR2(2),
  OPT_NAME    VARCHAR2(100),
  USER_NAME   VARCHAR2(50)
)
partition by range (OPT_TIME)(
partition OPT_TIME_01 values less than('2013-01-01') tablespace OPT_TIME_01,
partition OPT_TIME_02 values less than('2013-02-01') tablespace OPT_TIME_02,
partition OPT_TIME_03 values less than('2013-03-01') tablespace OPT_TIME_03,
partition OPT_TIME_04 values less than('2013-04-01') tablespace OPT_TIME_04,
partition OPT_TIME_05 values less than('2013-05-01') tablespace OPT_TIME_05,
partition OPT_TIME_06 values less than('2013-06-01') tablespace OPT_TIME_06,
partition OPT_TIME_07 values less than('2013-07-01') tablespace OPT_TIME_07,
partition OPT_TIME_08 values less than('2013-08-01') tablespace OPT_TIME_08,
partition OPT_TIME_09 values less than('2013-09-01') tablespace OPT_TIME_09,
partition OPT_TIME_10 values less than('2013-10-01') tablespace OPT_TIME_10,
partition OPT_TIME_11 values less than('2013-11-01') tablespace OPT_TIME_11,
partition OPT_TIME_12 values less than('2013-12-01') tablespace OPT_TIME_12,
partition OPT_TIME_other values less than(maxvalue) tablespace OPT_TIME_other
);


----- 本地索引
create index  local_index_OPT_TIME   on SYS_LOG(OPT_TIME) local;

---查询
select * from SYS_LOG partition(OPT_TIME_01);
select * from SYS_LOG partition(OPT_TIME_02);