1.创建表空间
SQL> create tablespace tbs001 datafile '/oradata/orcl/tbs001.dbf' size 150M autoextend on next 10M maxsize 3000M;
SQL> create tablespace tbs002 datafile '/oradata/orcl/tbs002.dbf' size 150M autoextend on next 10M maxsize 3000M;
SQL> create tablespace tbs003 datafile '/oradata/orcl/tbs003.dbf' size 150M autoextend on next 10M maxsize 3000M;
SQL> create tablespace tbs004 datafile '/oradata/orcl/tbs004.dbf' size 150M autoextend on next 10M maxsize 3000M;
SQL> create tablespace tbs005 datafile '/oradata/orcl/tbs005.dbf' size 150M autoextend on next 10M maxsize 3000M;
SQL> create tablespace tbs006 datafile '/oradata/orcl/tbs006.dbf' size 150M autoextend on next 10M maxsize 3000M;
SQL> create tablespace tbs007 datafile '/oradata/orcl/tbs007.dbf' size 150M autoextend on next 10M maxsize 3000M;
SQL> create tablespace tbs008 datafile '/oradata/orcl/tbs008.dbf' size 150M autoextend on next 10M maxsize 3000M;
SQL> create tablespace tbs009 datafile '/oradata/orcl/tbs009.dbf' size 150M autoextend on next 10M maxsize 3000M;
SQL> create tablespace tbs010 datafile '/oradata/orcl/tbs010.dbf' size 150M autoextend on next 10M maxsize 3000M;
SQL> create tablespace tbs011 datafile '/oradata/orcl/tbs011.dbf' size 150M autoextend on next 10M maxsize 3000M;
SQL> create tablespace tbs012 datafile '/oradata/orcl/tbs012.dbf' size 150M autoextend on next 10M maxsize 3000M;
2.创建分区表
CREATE TABLE rangeExample(
range_key_column DATE,
DATA VARCHAR2(20),
ID integer
) PARTITION BY RANGE(range_key_column)
(
PARTITION part01 VALUES LESS THAN (TO_DATE('2021-01-1 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE tbs001,
PARTITION part02 VALUES LESS THAN (TO_DATE('2021-02-1 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE tbs002,
PARTITION part03 VALUES LESS THAN (TO_DATE('2021-03-1 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE tbs003,
PARTITION part04 VALUES LESS THAN (TO_DATE('2021-04-1 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE tbs004,
PARTITION part05 VALUES LESS THAN (TO_DATE('2021-05-1 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE tbs005,
PARTITION part06 VALUES LESS THAN (TO_DATE('2021-06-1 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE tbs006,
PARTITION part07 VALUES LESS THAN (TO_DATE('2021-07-1 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE tbs007,
PARTITION part08 VALUES LESS THAN (TO_DATE('2021-08-1 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE tbs008,
PARTITION part09 VALUES LESS THAN (TO_DATE('2021-09-1 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE tbs009,
PARTITION part10 VALUES LESS THAN (TO_DATE('2021-10-1 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE tbs010,
PARTITION part11 VALUES LESS THAN (TO_DATE('2021-11-1 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE tbs011,
PARTITION part12 VALUES LESS THAN (TO_DATE('2021-12-1 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE tbs012
);
3.插入数据
insert into rangeExample values ('2021-01-1 06:00:00',1,1);
insert into rangeExample values ('2021-02-1 06:00:00',2,2);
insert into rangeExample values ('2021-03-1 06:00:00',3,3);
insert into rangeExample values ('2021-04-1 06:00:00',4,4);
insert into rangeExample values ('2021-05-1 06:00:00',5,5);
insert into rangeExample values ('2021-06-1 06:00:00',6,6);
insert into rangeExample values ('2021-07-1 06:00:00',7,7);
insert into rangeExample values ('2021-08-1 06:00:00',8,8);
insert into rangeExample values ('2021-09-1 06:00:00',9,9);
insert into rangeExample values ('2021-10-1 06:00:00',10,10);
insert into rangeExample values ('2021-11-1 06:00:00',11,11);
insert into rangeExample values ('2021-11-30 06:00:00',12,12);
4.查看分区数据https://www.cndba.cn/hbhe0316/article/4908
SQL> set linesize 200
SQL> set pagesize 200
SQL> col table_name for a20
SQL> col partition_name for a20
SQL> select table_name,partition_name from user_tab_partitions where table_name='RANGEEXAMPLE';
TABLE_NAME PARTITION_NAME
-------------------- --------------------
RANGEEXAMPLE PART01
RANGEEXAMPLE PART02
RANGEEXAMPLE PART03
RANGEEXAMPLE PART04
RANGEEXAMPLE PART05
RANGEEXAMPLE PART06
RANGEEXAMPLE PART07
RANGEEXAMPLE PART08
RANGEEXAMPLE PART09
RANGEEXAMPLE PART10
RANGEEXAMPLE PART11
RANGEEXAMPLE PART12
12 rows selected.
5.查看每个分区的数据https://www.cndba.cn/hbhe0316/article/4908https://www.cndba.cn/hbhe0316/article/4908
SQL> select count(*) from RANGEEXAMPLE partition (part01);
COUNT(*)
----------
0
SQL> select count(*) from RANGEEXAMPLE partition (part02);
COUNT(*)
----------
1
SQL> select count(*) from RANGEEXAMPLE partition (part03);
COUNT(*)
----------
1
SQL> select count(*) from RANGEEXAMPLE partition (part04);
COUNT(*)
----------
1
SQL> select count(*) from RANGEEXAMPLE partition (part05);
COUNT(*)
----------
1
SQL> select count(*) from RANGEEXAMPLE partition (part06);
COUNT(*)
----------
1
SQL> select count(*) from RANGEEXAMPLE partition (part07);
COUNT(*)
----------
1
SQL> select count(*) from RANGEEXAMPLE partition (part08);
COUNT(*)
----------
1
SQL> select count(*) from RANGEEXAMPLE partition (part09);
COUNT(*)
----------
1
SQL> select count(*) from RANGEEXAMPLE partition (part10);
COUNT(*)
----------
1
SQL> select count(*) from RANGEEXAMPLE partition (part11);
COUNT(*)
----------
1
SQL> select count(*) from RANGEEXAMPLE partition (part12);
COUNT(*)
----------
2
6.新增分区
SQL> ALTER TABLE rangeExample ADD PARTITION part13 VALUES LESS THAN (TO_DATE('2022-01-31 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE tbs012;
Table altered.
7.删除分区https://www.cndba.cn/hbhe0316/article/4908https://www.cndba.cn/hbhe0316/article/4908
SQL> ALTER TABLE rangeExample DROP PARTITION part13;
Table altered.
8.创建本地索引
SQL> create index com_index_range_example_id on rangeExample(id);
Index created.
9.创建本地分区索引https://www.cndba.cn/hbhe0316/article/4908https://www.cndba.cn/hbhe0316/article/4908
SQL> create index local_index_range_example_id on rangeExample(data) local;
Index created.
10.创建全局分区索引
https://www.cndba.cn/hbhe0316/article/4908
https://www.cndba.cn/hbhe0316/article/4908
https://www.cndba.cn/hbhe0316/article/4908
版权声明:本文为博主原创文章,未经博主允许不得转载。
Linux,oracle