ORACLE 创建分区表
来源:互联网 发布:碗扣式脚手架计算软件 编辑:程序博客网 时间:2024/04/30 18:54
-- 创建 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);
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);
- Oracle 创建分区表
- oracle创建分区表
- oracle分区表创建
- ORACLE 创建分区表
- oracle创建月分区表
- oracle创建日分区表
- Oracle 创建分区表
- Oracle 创建分区表
- Oracle分区表创建
- Oracle 创建分区表
- oracle创建分区表
- oracle创建分区表
- ORACLE 分区表种类以及分区表的创建
- oracle创建分区表,以及非分区表转分区表
- 在Oracle中创建分区表
- oracle按时间创建分区表
- oracle按时间创建分区表
- oracle 创建分区表,增(追)加分区
- 内容正文选中一段文字分享到qq微博、新浪微博的代码
- 冒泡排序
- java基础知识
- 关于面向对象编程时的原则
- Apache安装配置ModSecurity
- ORACLE 创建分区表
- android获取图片和视频的缩略图
- InstallShield制作软件安装包:在安装过程中启动一个c#写的程序
- mongodb客户端编程
- ORACLE 语句技巧
- Spring Nested exception: Invalid byte 3 of 3-byte UTF-8 sequence
- std::vector删除重复元素和查找
- Windows平台上编译安装ACE
- printk()的使用说明