oracle提供哪几种分区表的特点(oracle分区表分区备份有哪些方法)

技术ORACLE分区表日常维护方法是什么这篇文章主要讲解了“ORACLE分区表日常维护方法是什么”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“ORACLE分区表日常维护方

这篇文章主要讲解了“ORACLE分区表日常维护方法是什么",文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“ORACLE分区表日常维护方法是什么"吧!

1、测试表准备为了便于具体的操作演示,首先准备一张RANGE型的测试分区表TEST_RANGE_PARTITION。这里的测试数据来源于oracle测试用户scott下的emp表。--创建分区表TEST_RANGE_PARTITION--这里通过dbms_metadata.get_ddl获得emp表的建表结构进而修改SQL CREATE TABLE "SCOTT"."TEST_RANGE_PARTITION" ( "EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10),http://

ng>

通过下面的方法,了解关于上面创建分区表的数据分布基本情况。
复制代码

--查询分表各分区的条件以及数据库分布情况
--可以看到此时NUM_ROWS列为空,主要是因为表的的统计信息未收集导致。
SQL> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS from user_part_tables a,user_tab_partitions b where a.TABLE_NAME=b.TABLE_NAME and a.table_name='TEST_RANGE_PARTITION';

TABLE_NAME                     PARTITION PARTITION_NAME       HIGH_VALUE    NUM_ROWS
------------------------------ --------- -------------------- ----------- ----------
TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_01    1000
TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_02    2000
TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_03    3000
TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_MAX   MAXVALUE

--收集分区表TEST_RANGE_PARTITION的统计信息
SQL> analyze table TEST_RANGE_PARTITION compute statistics;

Table analyzed.

--可以看到,此时各分区的数据情况已经显示出来
SQL> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS from user_part_tables a,user_tab_partitions b where a.TABLE_NAME=b.TABLE_NAME and a.table_name='TEST_RANGE_PARTITION';

TABLE_NAME                     PARTITION PARTITION_NAME       HIGH_VALUE    NUM_ROWS
------------------------------ --------- -------------------- ----------- ----------
TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_01    1000                 2
TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_02    2000                 6
TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_03    3000                 3
TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_MAX   MAXVALUE             3

通过上面的操作,已经成功创建了一张RANGE型的分区表。

下面将依托这张表,介绍分区表的日常维护操作。

2、增加分区维护操作(add)
增加分区维护操作,顾名思义,主要针对当前分区表进行添加新分区的操作。

当分区表存在默认条件分区,如:RANGE分区表的MAXVALUE分区、LIST分区表的DEFAULT分区,此时增加分区操作会报错。

下面尝试通过增加分区操作,直接为测试表增加分区TEST_RANGE_SAL_04

SQL> alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_04 values less than(4000);
alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_04 values less than(4000)
                                               *
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition

可以看到,针对存在默认条件的分区表,无法执行增加分区操作。

解决办法:
1、删除原默认条件分区,待增加分区后,再重新添加默认条件分区。
2、使用拆分分区(split)的方式,后面介绍。

这里,我们尝试下解决办法1的方法进行操作。
--删除存在默认条件MAXVALUE的分区
SQL> alter table TEST_RANGE_PARTITION drop partition TEST_RANGE_SAL_MAX;

Table altered.

--重新收集分区表的统计信息
SQL> analyze table TEST_RANGE_PARTITION compute statistics;
Table analyzed.

--观察分区表的信息,可以看到此时默认条件MAXVALUE的分区已经不存在
SQL> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS from user_part_tables a,user_tab_partitions b where a.TABLE_NAME=b.TABLE_NAME and a.table_name='TEST_RANGE_PARTITION';

TABLE_NAME                     PARTITION PARTITION_NAME       HIGH_VALUE    NUM_ROWS
------------------------------ --------- -------------------- ----------- ----------
TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_01    1000                 2
TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_02    2000                 6
TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_03    3000                 3

--增加新分区TEST_RANGE_SAL_04
SQL> alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_04 values less than(4000);
Table altered.

--重新增加默认条件MAXVALUE分区
SQL> alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_MAX values less than(maxvalue);
Table altered.

通过上面的方法,已经完成了增加分区的操作。下面进一步验证增加分区的操作。

--重新收集测试分区表的统计信息
SQL> analyze table TEST_RANGE_PARTITION compute statistics;
Table analyzed.

--查看分区表信息,可以看到上面增加的新分区
SQL> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS from user_part_tables a,user_tab_partitions b where a.TABLE_NAME=b.TABLE_NAME and a.table_name='TEST_RANGE_PARTITION';

TABLE_NAME            PARTITION PARTITION_NAME     HIGH_VALUE   NUM_ROWS
--------------------- --------- ------------------ ----------- ---------
TEST_RANGE_PARTITION  RANGE     TEST_RANGE_SAL_01  1000                2
TEST_RANGE_PARTITION  RANGE     TEST_RANGE_SAL_02  2000                6
TEST_RANGE_PARTITION  RANGE     TEST_RANGE_SAL_03  3000                3
TEST_RANGE_PARTITION  RANGE     TEST_RANGE_SAL_MAX MAXVALUE            0
TEST_RANGE_PARTITION  RANGE     TEST_RANGE_SAL_04  4000                0

需要注意的是:对于默认条件的分区进行删除,其数据不会重分布到其他分区,而是删除数据。因此在生产环境使用需慎重。
至此,增加分区维护操作的介绍结束。
 
3、移动分区维护操作(move)
移动分区维护操作,主要是将分区从一个表空间迁移至另一个表空间中。

--查看当前分区对应的表空间情况
SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME       TABLESPACE_NAME
------------------------------ -------------------- ------------------------------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_02    USERS
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03    USERS
TEST_RANGE_PARTITION           TEST_RANGE_SAL_01    USERS
TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX   USERS
TEST_RANGE_PARTITION           TEST_RANGE_SAL_04    USERS

--执行移动分区操作
SQL> alter table TEST_RANGE_PARTITION move partition TEST_RANGE_SAL_01 tablespace PARTITION_TS;
Table altered.

--验证移动后,分区所在的表空间
SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME       TABLESPACE_NAME
------------------------------ -------------------- ------------------------------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_02    USERS
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03    USERS
TEST_RANGE_PARTITION           TEST_RANGE_SAL_01    PARTITION_TS
TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX   USERS
TEST_RANGE_PARTITION           TEST_RANGE_SAL_04    USERS

需要注意的是:
对于组合分区,无法直接移动分区,否则会抛出ORA-14257错误,示例如下:

--准备一张list-list的组合分区表
SQL> CREATE TABLE "EMPLOYEE_LIST_LIST_PART"
      ( "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
     )
     PARTITION BY LIST (DEPTNO)
     SUBPARTITION BY LIST (JOB)
     (
     PARTITION EMPLOYEE_DEPTNO_10 VALUES (10)
       ( SUBPARTITION EMPLOYEE_10_JOB_MAGAGER VALUES ('MANAGER'),
         SUBPARTITION EMPLOYEE_10_JOB_DEFAULT VALUES (DEFAULT)
       ),
     PARTITION EMPLOYEE_DEPTNO_20 VALUES (20)
       ( SUBPARTITION EMPLOYEE_20_JOB_MAGAGER VALUES ('MANAGER'),
         SUBPARTITION EMPLOYEE_20_JOB_DEFAULT VALUES (DEFAULT)
       ),
     PARTITION EMPLOYEE_DEPTNO_OTHERS VALUES (DEFAULT)
       ( SUBPARTITION EMPLOYEE_30_JOB_MAGAGER VALUES ('MANAGER'),
         SUBPARTITION EMPLOYEE_30_JOB_DEFAULT VALUES (DEFAULT)
       )
     );

Table created.

--查看当前该组合分区所在表空间的信息
SQL> select TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,TABLESPACE_NAME from user_tab_subpartitions;

TABLE_NAME              PARTITION_NAME         SUBPARTITION_NAME        TABLESPACE_NAME
----------------------- ---------------------- ------------------------ ---------------
EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10     EMPLOYEE_10_JOB_MAGAGER  USERS
EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10     EMPLOYEE_10_JOB_DEFAULT  USERS
EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20     EMPLOYEE_20_JOB_MAGAGER  USERS
EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20     EMPLOYEE_20_JOB_DEFAULT  USERS
EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_MAGAGER  USERS
EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_DEFAULT  USERS

--移动组合分区表的区分
SQL> alter table EMPLOYEE_LIST_LIST_PART  move partition EMPLOYEE_DEPTNO_20 tablespace PARTITION_TS;
alter table EMPLOYEE_LIST_LIST_PART  move partition EMPLOYEE_DEPTNO_20 tablespace PARTITION_TS
                                                    *
ERROR at line 1:
ORA-14257: cannot move partition other than a Range, List, System, or Hash partition

通过上面的演示,可以清楚的看到,对于组合分区,无法直接移动分区至新的表空间。

 
解决办法:
移动分区表的子分区,然后修改当前所在分区的属性即可。具体演示如下:

--移动子分区
SQL> alter table EMPLOYEE_LIST_LIST_PART  move subpartition EMPLOYEE_20_JOB_MAGAGER tablespace PARTITION_TS;
Table altered.

SQL> alter table EMPLOYEE_LIST_LIST_PART  move subpartition EMPLOYEE_20_JOB_DEFAULT tablespace PARTITION_TS;
Table altered.

--修改分区的默认属性
SQL> ALTER TABLE EMPLOYEE_LIST_LIST_PART MODIFY DEFAULT ATTRIBUTES FOR PARTITION EMPLOYEE_DEPTNO_20 tablespace PARTITION_TS;

Table altered.

--验证移动分区后的结果
SQL> select TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,TABLESPACE_NAME from user_tab_subpartitions;

TABLE_NAME              PARTITION_NAME         SUBPARTITION_NAME        TABLESPACE_NAME
----------------------- ---------------------  -----------------------  ---------------
EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10     EMPLOYEE_10_JOB_MAGAGER  USERS
EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10     EMPLOYEE_10_JOB_DEFAULT  USERS
EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20     EMPLOYEE_20_JOB_MAGAGER  PARTITION_TS
EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20     EMPLOYEE_20_JOB_DEFAULT  PARTITION_TS
EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_MAGAGER  USERS
EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_DEFAULT  USERS

可以看到,通过移动子分区的方法,完成了对于组合分区的移动操作。

 
4、截断分区维护操作(truncate)
截断分区维护操作,相对于传统的delete操作,删除数据的效率会更高。而且会降低高水位线。

演示如下:

--查看当前测试表分区情况及分区中的记录数
SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions where PARTITION_NAME='TEST_RANGE_SAL_02' or PARTITION_NAME='TEST_RANGE_SAL_03';

TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                    6
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3

--执行截断分区操作
SQL> alter table TEST_RANGE_PARTITION truncate partition TEST_RANGE_SAL_02;
Table truncated.

--重新收集最新的测试表的统计信息
SQL> analyze table TEST_RANGE_PARTITION compute statistics;

Table analyzed.

--验证截断操作后,分区的记录数变化
SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions where PARTITION_NAME='TEST_RANGE_SAL_02' or PARTITION_NAME='TEST_RANGE_SAL_03';

TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3

从上面的演示中可以看到,通过truncate操作,测试表的TEST_RANGE_SAL_02分区数据被清空。至此,演示完毕。

 
5、删除分区维护操作(drop)
对于分区的删除操作,需要注意,在删除分区后,分区所记录的数据,不会重分布至其他分区中,而是被一并删除。

--检查当前分区表的分区情况,以及数据的分布情况
SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3
TEST_RANGE_PARTITION           TEST_RANGE_SAL_01         PARTITION_TS             2
TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    0

--执行分区的删除操作
SQL> alter table TEST_RANGE_PARTITION drop partition TEST_RANGE_SAL_04;

Table altered.

--再次检查分区表的分区情况,以及数据的分布情况
SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3
TEST_RANGE_PARTITION           TEST_RANGE_SAL_01         PARTITION_TS             2
TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0

可以看到,分区的删除操作不会影响数据的分布情况。

6、拆分分区维护操作(split)
在“增加分区维护操作”部分,提到了对于存在默认条件的分区表增加分区的的两种办法,这里将介绍通过拆分分区的办法来增加分区。

需要注意:在目标分区拆分后,被拆分的分区会按照拆分规则,将数据进行重分布。

演示实例:
首先,将测试表的数据分布还原至初建时的数据分布态。

--清空测试分区表中的所有数据
SQL> truncate table TEST_RANGE_PARTITION;

Table truncated.

--重新加载测试分区表的数据
SQL> insert into TEST_RANGE_PARTITION select * from emp;

14 rows created.

SQL> commit;

Commit complete.

--重新收集测试表的统计信息
SQL> analyze table TEST_RANGE_PARTITION compute statistics;

Table analyzed.

--查看此时,数据在分区间的分布情况
SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                    6
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3
TEST_RANGE_PARTITION           TEST_RANGE_SAL_01         PARTITION_TS             2
TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    3

查看此时,存在默认条件MAXVALUE的分区TEST_RANGE_SAL_MAX的具体数据信息:
SQL> select * from TEST_RANGE_PARTITION partition(TEST_RANGE_SAL_MAX);

     EMPNO ENAME      JOB              MGR HIREDATE          SAL     COMM    DEPTNO
---------- ---------- --------- ---------- ------------ -------- -------- ---------
      7788 SCOTT      ANALYST         7566 19-APR-87        3000                 20
      7839 KING       PRESIDENT            17-NOV-81        5000                 10
      7902 FORD       ANALYST         7566 03-DEC-81        3000                 20
 

下面针对上面的分区TEST_RANGE_SAL_MAX进行拆分处理,其中:
将SAL>=3000且SAL<4000的数据放入新的分区TEST_RANGE_SAL_04。
将SAL>=4000的数据保留在分区TEST_RANGE_SAL_MAX中。

--针对目标分区,执行拆分分区维护操作
--依据上面的需求,将数据拆分至分区TEST_RANGE_SAL_04以及TEST_RANGE_SAL_MAX中
SQL> alter table TEST_RANGE_PARTITION split partition TEST_RANGE_SAL_MAX at (4000) into (partition TEST_RANGE_SAL_04,partition TEST_RANGE_SAL_MAX);

Table altered.

--查看此时测试分区表的分区情况,以及数据分布情况
SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                    6
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3
TEST_RANGE_PARTITION           TEST_RANGE_SAL_01         PARTITION_TS             2
TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    2
TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    1
 

验证分区中实际的数据内容:

SQL> select * from TEST_RANGE_PARTITION partition(TEST_RANGE_SAL_04);

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20

SQL> select * from TEST_RANGE_PARTITION partition(TEST_RANGE_SAL_MAX);

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81          5000                    10

可以看到,经过拆分,数据已按之前的需求,分别存储在两个分区中。

7、合并分区维护操作(merge)
合并分区操作,主要是将不同的分区,通过分区的合并,进行整合。

需要注意:
    对于list分区,合并的分区无限制要求。
    对于range分区,合并的分区必须相邻,否则无法进行合并操作。
    对于hash分区,无法进行合并分区操作。

此外,对于range分区,下限值由边界值较低的分区决定,上限值由边界值较高的分区决定。

演示示例:
通过合并分区技术,将测试表的分区TEST_RANGE_SAL_01以及分区TEST_RANGE_SAL_02进行合并,具体如下:

--查看当前分区表的分区情况
SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                    6
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3
TEST_RANGE_PARTITION           TEST_RANGE_SAL_01         PARTITION_TS             2
TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    2
TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    1

--查询分区TEST_RANGE_SAL_01、TEST_RANGE_SAL_02值分布情况:
SQL> select * from TEST_RANGE_PARTITION partition(TEST_RANGE_SAL_01);

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30

SQL> select * from TEST_RANGE_PARTITION partition(TEST_RANGE_SAL_02);

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

6 rows selected.

--进行合并分区操作
SQL> alter table TEST_RANGE_PARTITION merge partitions TEST_RANGE_SAL_01,TEST_RANGE_SAL_02 into partition TEST_RANGE_SAL_00;

Table altered.

--验证合并分区后的结果
SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3
TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    2
TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    1
TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                    8

SQL> select * from TEST_RANGE_PARTITION partition(TEST_RANGE_SAL_00);

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

8 rows selected.

 
8、交换分区维护操作(exchange)
交换分区技术,主要是将一个非分区表的数据同“一个分区表的一个分区”进行数据交换。支持双向交换,既可以从分区表的分区中迁移到非分区表,也可以从非分区表迁移至分区表的分区中。

原则上,非分区表的结构、数据分布等,要符合分区表的目标分区的定义规则。

演示如下:
首先,清空测试分区表的数据
SQL> truncate table TEST_RANGE_PARTITION;

Table truncated.

---查询:
SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                    0
 

---创建一张基于emp表,sal<2000的测试非分区表emp_test。

SQL> create table emp_test as select * from emp where sal < 2000;

Table created.

SQL> select count(*) from emp_test;

  COUNT(*)
----------
         8

注意,此时非分区表的数据量为8条记录。

---执行交换分区操作,观察分区表的记录变化,以及非分区表的记录变化
---执行分区交换操作
SQL> alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test;

Table altered.

SQL> analyze table TEST_RANGE_PARTITION compute statistics;

Table analyzed.

SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                    8
TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0

SQL> select count(*) from emp_test;

  COUNT(*)
----------
         0

可以看到,通过分区交换,非分区表的数据转移至分区表中,同时非分区表的记录被清除。
 

---再次执行交换分区操作,观察分区表的记录变化,以及非分区表的记录变化
SQL> alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test;

Table altered.

SQL> analyze table TEST_RANGE_PARTITION compute statistics;

Table analyzed.

SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                    0

SQL> select count(*) from emp_test;

  COUNT(*)
----------
         8

可以看到,此时分区表的数据又再次转移回至非分区表,证明了前面所述,分区交换技术,既可以从分区表的分区中迁移到非分区表,也可以从非分区表迁移至分区表的分区中。

注意:若非分区表的数据,不符合分区表的分区规则,此时交换会抛出ORA-14099错误。

--清空上面测试非分区表的数据
SQL> truncate table emp_test;

Table truncated.

--加载emp的所有数据至该测试非分区表
--之所以使用测试非分区表,是考虑emp表以后做其他实验时可能还需要其中的数据
--通过这样操作,测试非分区表的数据,既存在sal<2000的数据,也存在sal>2000的数据
SQL> insert into emp_test select * from emp;

14 rows created.

SQL> commit;

Commit complete.

--尝试交换分区,观察结果
SQL> alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test;
alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test
                                                                                 *
ERROR at line 1:
ORA-14099: all rows in table do not qualify for specified partition

可以看到,由于TEST_RANGE_SAL_00分区的限制条件为sal<2000,而测试非分区表的数据包含了sal>2000的数据,因此交换失败。

解决办法:
通过without validation子句,可以避免数据校验,而交换成功。但会存在与分区规则相悖的数据,因此该方法要慎重。

SQL> alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test without validation;

Table altered.

SQL> analyze table TEST_RANGE_PARTITION compute statistics;

Table analyzed.

SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                   14
TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0

技术方案扩展思路:
若打算采用交换分区的方法,以实现非分区表到分区表的转换,可以采用先创建一个只有默认条件的单一分区的分区表,在分区交换数据后,根据实际需要,通过前面提到的“拆分分区”的方法进行分区操作。即大表改分区表(交换分区+分区分裂)

 
9、收缩分区维护操作(coalesce)
收缩分区维护操作,仅仅可以在hash分区以及组合分区的hash子分区上进行使用。
通过使用收缩分区技术,可以收缩当前hash分区的分区数量。
对于hash分区的数据,在收缩过程中,oracle会自动完成数据在分区间的重分布。

演示如下:
首先基于emp表的数据,创建一张hash分区表

SQL> CREATE TABLE "EMPLOYEE_HASH_PART"
      ( "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
      )
      PARTITION BY HASH (ENAME)
      (
      PARTITION EMPLOYEE_PART01,
      PARTITION EMPLOYEE_PART02
     );  

Table created.

SQL> insert into EMPLOYEE_HASH_PART select * from emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> analyze table EMPLOYEE_HASH_PART compute statistics;

Table analyzed.

SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
EMPLOYEE_HASH_PART             EMPLOYEE_PART02           USERS                    6
EMPLOYEE_HASH_PART             EMPLOYEE_PART01           USERS                    8

执行收缩分区操作
SQL> alter table EMPLOYEE_HASH_PART coalesce partition;

Table altered.

SQL> analyze table EMPLOYEE_HASH_PART compute statistics;

Table analyzed.

SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
EMPLOYEE_HASH_PART             EMPLOYEE_PART01           USERS                   14

可以看到,通过收缩分区,原本两个分区整合到一个,而且数据也同时被整合。

需要注意:
当hash分区中只有一个分区时,此时无法进行收缩操作。

SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
EMPLOYEE_HASH_PART             EMPLOYEE_PART01           USERS                   14

SQL> alter table EMPLOYEE_HASH_PART coalesce partition;
alter table EMPLOYEE_HASH_PART coalesce partition
            *
ERROR at line 1:
ORA-14285: cannot COALESCE the only partition of this hash partitioned table or index

感谢各位的阅读,以上就是“ORACLE分区表日常维护方法是什么”的内容了,经过本文的学习后,相信大家对ORACLE分区表日常维护方法是什么这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是,小编将为大家推送更多相关知识点的文章,欢迎关注!

内容来源网络,如有侵权,联系删除,本文地址:https://www.230890.com/zhan/155151.html

(0)

相关推荐

  • 关于应用程序连接Oracle 12C ORA-01017问题是怎么解决的

    技术关于应用程序连接Oracle 12C ORA-01017问题是怎么解决的这篇文章将为大家详细讲解有关关于应用程序连接Oracle 12C ORA-01017问题是怎么解决的,文章内容质量较高,因此小编分享给大家做个参

    攻略 2021年11月30日
  • 导致JVM物理内存消耗大的Bug是怎么样的

    技术导致JVM物理内存消耗大的Bug是怎么样的导致JVM物理内存消耗大的Bug是怎么样的,很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。概述最

    攻略 2021年10月23日
  • mongodb副本集

    技术mongodb副本集 mongodb副本集服务器配置
    HOSTNAME IP 应用
    1-81 172.16.1.81 mongodb-3.6.23
    1-82

    礼包 2021年10月26日
  • Python如何爬取腾讯招聘信息

    技术Python如何爬取腾讯招聘信息Python如何爬取腾讯招聘信息,很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。1. 目标目标:https

    攻略 2021年10月26日
  • EditPlus密钥算法源码是怎么样的

    技术EditPlus密钥算法源码是怎么样的这篇文章主要为大家展示了“EditPlus密钥算法源码是怎么样的”,内容简而易懂,条理清晰,希望能够帮助大家解决疑惑,下面让小编带领大家一起研究并学习一下“EditPlus密钥算

    攻略 2021年11月23日
  • 赞美柳树的句子最短的,用简单一句话赞美倒垂柳的句子

    技术赞美柳树的句子最短的,用简单一句话赞美倒垂柳的句子1.春天是个温柔的小女孩,她跳着舞来了赞美柳树的句子最短的。这时的柳树发芽了,那些嫩绿的小芽犹如一个个刚出生的小宝宝探出小小的脑袋,看看这陌生的世界。一阵春风从远处吹

    生活 2021年10月22日