ORACLE 创建分区表

最新推荐文章于 2024-08-27 23:26:49 发布

最新推荐文章于 2024-08-27 23:26:49 发布 · 153 阅读

文章标签：

本文详细介绍了如何在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);