ORACLE分区表日常维护方法是什么

网友投稿 527 2023-12-05

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),           "JOB" VARCHAR2(9),"MGR" NUMBER(4,0),           "HIREDATE" DATE,           "SAL" NUMBER(7,2),           "COMM" NUMBER(7,2),           "DEPTNO" NUMBER(2,0)      ) PARTITION BY RANGE ("SAL")      (PARTITION "TEST_RANGE_SAL_01" VALUES LESS THAN (1000),PARTITION "TEST_RANGE_SAL_02" VALUES LESS THAN (2000),       PARTITION "TEST_RANGE_SAL_03" VALUES LESS THAN (3000), PARTITION "TEST_RANGE_SAL_MAX" VALUES LESS THAN (MAXVALUE)      );Table created.

SQL> insert into TEST_RANGE_PARTITION select * from emp;

14 rows created.

SQL> commit;

Commit complete.通过下面的方法，了解关于上面创建分区表的数据分布基本情况。复制代码--查询分表各分区的条件以及数据库分布情况--可以看到此时NUM_ROWS列为空，主要是因为表的的统计信息未收集导致。SQL> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWSfrom 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    1000TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_02    2000TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_03    3000TEST_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_NAMEand a.table_name=TEST_RANGE_PARTITION;TABLE_NAME                     PARTITION PARTITION_NAME       HIGH_VALUE    NUM_ROWS------------------------------ --------- -------------------- ----------- ----------TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_01    1000                 2TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_02    2000                 6TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_03    3000                 3TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_MAX   MAXVALUE             3通过上面的操作，已经成功创建了一张RANGE型的分区表。下面将依托这张表，介绍分区表的日常维护操作。2、增加分区维护操作（add）增加分区维护操作，顾名思义，主要针对当前分区表进行添加新分区的操作。当分区表存在默认条件分区，如：RANGE分区表的MAXVALUE分区、LIST分区表的DEFAULT分区，此时增加分区操作会报错。下面尝试通过增加分区操作，直接为测试表增加分区TEST_RANGE_SAL_04SQL> 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_NAMEand a.table_name=TEST_RANGE_PARTITION; TABLE_NAME                     PARTITION PARTITION_NAME       HIGH_VALUE    NUM_ROWS------------------------------ --------- -------------------- ----------- ----------TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_01    1000                 2TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_02    2000                 6TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_03    3000                 3--增加新分区TEST_RANGE_SAL_04SQL> 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_NAMEand a.table_name=TEST_RANGE_PARTITION; TABLE_NAME            PARTITION PARTITION_NAME     HIGH_VALUE   NUM_ROWS--------------------- --------- ------------------ ----------- ---------TEST_RANGE_PARTITION  RANGE     TEST_RANGE_SAL_01  1000                2TEST_RANGE_PARTITION  RANGE     TEST_RANGE_SAL_02  2000                6TEST_RANGE_PARTITION  RANGE     TEST_RANGE_SAL_03  3000                3TEST_RANGE_PARTITION  RANGE     TEST_RANGE_SAL_MAX MAXVALUE            0TEST_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    USERSTEST_RANGE_PARTITION           TEST_RANGE_SAL_03    USERSTEST_RANGE_PARTITION           TEST_RANGE_SAL_01    USERSTEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX   USERSTEST_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    USERSTEST_RANGE_PARTITION           TEST_RANGE_SAL_03    USERSTEST_RANGE_PARTITION           TEST_RANGE_SAL_01    PARTITION_TSTEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX   USERSTEST_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  USERSEMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10     EMPLOYEE_10_JOB_DEFAULT  USERSEMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20     EMPLOYEE_20_JOB_MAGAGER  USERSEMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20     EMPLOYEE_20_JOB_DEFAULT  USERSEMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_MAGAGER  USERSEMPLOYEE_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  USERSEMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10     EMPLOYEE_10_JOB_DEFAULT  USERSEMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20     EMPLOYEE_20_JOB_MAGAGER  PARTITION_TSEMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20     EMPLOYEE_20_JOB_DEFAULT  PARTITION_TSEMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_MAGAGER  USERSEMPLOYEE_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                    6TEST_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_partitionswhere 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                    0TEST_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                    0TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3TEST_RANGE_PARTITION           TEST_RANGE_SAL_01         PARTITION_TS             2TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0TEST_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                    0TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3TEST_RANGE_PARTITION           TEST_RANGE_SAL_01         PARTITION_TS             2TEST_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                    6TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3TEST_RANGE_PARTITION           TEST_RANGE_SAL_01         PARTITION_TS             2TEST_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                 207839 KING       PRESIDENT            17-NOV-81        5000                 107902 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                    6TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3TEST_RANGE_PARTITION           TEST_RANGE_SAL_01         PARTITION_TS             2TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    2TEST_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                    20SQL> 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                    6TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3TEST_RANGE_PARTITION           TEST_RANGE_SAL_01         PARTITION_TS             2TEST_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                    3TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    2TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    1TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                    8SQL> 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                    207900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    307499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         307521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         307844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         307876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    207934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    108 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                    0TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    0TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0TEST_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                    0TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                    8TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    0TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0SQL> 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                    0TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    0TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                    0SQL> 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                    0TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                   14TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    0TEST_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                    6EMPLOYEE_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                   14SQL> 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分区表日常维护方法是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。

标签：方法 日常 属性 实例 学习
上一篇:Oracle内存和架构知识点有哪些
下一篇:LOB类型有哪些
相关文章

 发表评论

暂时没有评论，来抢沙发吧~