Oracle范围分区表

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.查看分区数据

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.查看每个分区的数据

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.删除分区

SQL> ALTER TABLE rangeExample DROP PARTITION part13;

Table altered.

8.创建本地索引

SQL> create index com_index_range_example_id on rangeExample(id);

Index created.

9.创建本地分区索引

SQL> create index  local_index_range_example_id on rangeExample(data) local;

Index created.

10.创建全局分区索引

版权声明：本文为博主原创文章，未经博主允许不得转载。

Linux,oracle