ORACLE分區表梳理系列（二）- 分區表日常維護及註意事項（紅字需要留意）

来源:http://www.cnblogs.com/yumiko/archive/2016/12/15/6163523.html
-Advertisement-
Play Games

版權聲明：本文發佈於http://www.cnblogs.com/yumiko/，版權由Yumiko_sunny所有，歡迎轉載。轉載時，請在文章明顯位置註明原文鏈接。若在未經作者同意的情況下，將本文內容用於商業用途，將保留追究其法律責任的權利。如果有問題，請以郵箱方式聯繫作者（793113046@q ...


版權聲明：本文發佈於http://www.cnblogs.com/yumiko/，版權由Yumiko_sunny所有，歡迎轉載。轉載時，請在文章明顯位置註明原文鏈接。若在未經作者同意的情況下，將本文內容用於商業用途，將保留追究其法律責任的權利。如果有問題，請以郵箱方式聯繫作者（[email protected]）。

 前言

  • 本文著重總結分區表的日常維護操作以及相應的註意事項。
  • 本文涉及的日常維護內容包括：
    • 增加分區（add）
    • 移動分區（move）
    • 截斷分區（truncate）
    • 刪除分區（drop）
    • 拆分分區（split）
    • 合併分區（merge）      --hash分區不適用
    • 交換分區（exchange）
    • 收縮分區（coalesce）   --僅適用於hash分區
  • 本文涉及一些非分區表至分區表的遷移方法的思路，以及一些日常維護操作在特殊情況下的處理方法。
  • 本文演示涉及的測試分區表，若無特殊創建或者說明，預設使用“測試表準備”部分提及的測試表。
  • 本文演示使用的資料庫版本為oracle 11.2.0.4。

 

需要註意：關於分區表日常維護操作，對於分區表索引的影響未提及，會在後面總結分區表索引時進行闡述說明。

 

 

1、測試表準備

為了便於具體的操作演示，首先準備一張RANGE型的測試分區表TEST_RANGE_PARTITION。

這裡的測試數據來源於oracle測試用戶scott下的emp表。

--創建分區表TEST_RANGE_PARTITION
--這裡通過dbms_metadata.get_ddl獲得emp表的建表結構進而修改
Yumiko_sunny@OA01> CREATE TABLE "SCOTT"."TEST_RANGE_PARTITION"
  2     (    "EMPNO" NUMBER(4,0),
  3          "ENAME" VARCHAR2(10),
  4          "JOB" VARCHAR2(9),
  5          "MGR" NUMBER(4,0),
  6          "HIREDATE" DATE,
  7          "SAL" NUMBER(7,2),
  8          "COMM" NUMBER(7,2),
  9          "DEPTNO" NUMBER(2,0)
 10     ) 
 11    PARTITION BY RANGE ("SAL")
 12     (PARTITION "TEST_RANGE_SAL_01" VALUES LESS THAN (1000)
 13      PARTITION "TEST_RANGE_SAL_02" VALUES LESS THAN (2000) 
 14      PARTITION "TEST_RANGE_SAL_03" VALUES LESS THAN (3000) 
 15      PARTITION "TEST_RANGE_SAL_MAX" VALUES LESS THAN (MAXVALUE)  
 16     );
Table created.


Yumiko_sunny@OA01> insert into TEST_RANGE_PARTITION select * from emp;
14 rows created.


Yumiko_sunny@OA01> commit;
Commit complete.

 

通過下麵的方法，瞭解關於上面創建分區表的數據分佈基本情況。

--查詢分表各分區的條件以及資料庫分佈情況
--可以看到此時NUM_ROWS列為空，主要是因為表的的統計信息未收集導致。
Yumiko_sunny@OA01> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS 
  2  from user_part_tables a,user_tab_partitions b 
  3  where a.TABLE_NAME=b.TABLE_NAME; 

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的統計信息
Yumiko_sunny@OA01> analyze table TEST_RANGE_PARTITION compute statistics;
Table analyzed.


--可以看到，此時各分區的數據情況已經顯示出來
Yumiko_sunny@OA01> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS 
  2  from user_part_tables a,user_tab_partitions b 
  3  where a.TABLE_NAME=b.TABLE_NAME; 

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

Yumiko_sunny@OA01> 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的分區
Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION drop partition TEST_RANGE_SAL_MAX;
Table altered.


--重新收集分區表的統計信息
Yumiko_sunny@OA01> analyze table TEST_RANGE_PARTITION compute statistics;
Table analyzed.


--觀察分區表的信息，可以看到此時預設條件MAXVALUE的分區已經不存在
Yumiko_sunny@OA01> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS 
  2  from user_part_tables a,user_tab_partitions b 
  3  where a.TABLE_NAME=b.TABLE_NAME; 

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
Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_04 values less than(4000);
Table altered.


--重新增加預設條件MAXVALUE分區
Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_MAX values less than(maxvalue);
Table altered.

 

通過上面的方法，已經完成了增加分區的操作。下麵進一步驗證增加分區的操作。

--重新收集測試分區表的統計信息
Yumiko_sunny@OA01> analyze table TEST_RANGE_PARTITION compute statistics;
Table analyzed.


--查看分區表信息，可以看到上面增加的新分區
Yumiko_sunny@OA01> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS 
  2  from user_part_tables a,user_tab_partitions b 
  3  where a.TABLE_NAME=b.TABLE_NAME; 

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）

移動分區維護操作，主要是將分區從一個表空間遷移至另一個表空間中。

--查看當前分區對應的表空間情況
Yumiko_sunny@OA01> 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


--執行移動分區操作
Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION move partition TEST_RANGE_SAL_01 tablespace PARTITION_TS;
Table altered.


--驗證移動後，分區所在的表空間
Yumiko_sunny@OA01> 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的組合分區表
Yumiko_sunny@OA01> CREATE TABLE "EMPLOYEE_LIST_LIST_PART"
  2      ( "EMPNO" NUMBER(4,0),
  3        "ENAME" VARCHAR2(10),
  4        "JOB" VARCHAR2(9),
  5        "MGR" NUMBER(4,0),
  6        "HIREDATE" DATE,
  7        "SAL" NUMBER(7,2),
  8        "COMM" NUMBER(7,2),
  9        "DEPTNO" NUMBER(2,0)
 10     )
 11     PARTITION BY LIST (DEPTNO)
 12     SUBPARTITION BY LIST (JOB)
 13     (
 14     PARTITION EMPLOYEE_DEPTNO_10 VALUES (10) 
 15       ( SUBPARTITION EMPLOYEE_10_JOB_MAGAGER VALUES ('MANAGER'),
 16         SUBPARTITION EMPLOYEE_10_JOB_DEFAULT VALUES (DEFAULT)
 17       ),
 18     PARTITION EMPLOYEE_DEPTNO_20 VALUES (20) 
 19       ( SUBPARTITION EMPLOYEE_20_JOB_MAGAGER VALUES ('MANAGER'),
 20         SUBPARTITION EMPLOYEE_20_JOB_DEFAULT VALUES (DEFAULT)
 21       ),
 22     PARTITION EMPLOYEE_DEPTNO_OTHERS VALUES (DEFAULT) 
 23       ( SUBPARTITION EMPLOYEE_30_JOB_MAGAGER VALUES ('MANAGER'),
 24         SUBPARTITION EMPLOYEE_30_JOB_DEFAULT VALUES (DEFAULT)
 25       )
 26     );

Table created.



--查看當前該組合分區所在表空間的信息
Yumiko_sunny@OA01> 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


--移動組合分區表的區分
Yumiko_sunny@OA01> 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

通過上面的演示，可以清楚的看到，對於組合分區，無法直接移動分區至新的表空間。

 

解決辦法：

移動分區表的子分區，然後修改當前所在分區的屬性即可。具體演示如下：

--移動子分區
Yumiko_sunny@OA01> alter table EMPLOYEE_LIST_LIST_PART  move subpartition EMPLOYEE_20_JOB_MAGAGER tablespace PARTITION_TS;
Table altered.


Yumiko_sunny@OA01> alter table EMPLOYEE_LIST_LIST_PART  move subpartition EMPLOYEE_20_JOB_DEFAULT tablespace PARTITION_TS;
Table altered.


--修改分區的預設屬性
Yumiko_sunny@OA01> ALTER TABLE EMPLOYEE_LIST_LIST_PART MODIFY DEFAULT ATTRIBUTES FOR PARTITION EMPLOYEE_DEPTNO_20
  2  tablespace PARTITION_TS;
Table altered.



--驗證移動分區後的結果
Yumiko_sunny@OA01> 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操作，刪除數據的效率會更高。而且會降低高水位線。

演示如下：

--查看當前測試表分區情況及分區中的記錄數
Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions
  2  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



--執行截斷分區操作
Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION truncate partition TEST_RANGE_SAL_02;
Table truncated.



--重新收集最新的測試表的統計信息
Yumiko_sunny@OA01> analyze table TEST_RANGE_PARTITION compute statistics;
Table analyzed.



--驗證截斷操作後，分區的記錄數變化
Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions
  2  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）

對於分區的刪除操作，需要註意，在刪除分區後，分區所記錄的數據，不會重分佈至其他分區中，而是被一併刪除。

--檢查當前分區表的分區情況，以及數據的分佈情況
Yumiko_sunny@OA01> 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


--執行分區的刪除操作
Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION drop partition TEST_RANGE_SAL_04;
Table altered.



--再次檢查分區表的分區情況，以及數據的分佈情況
Yumiko_sunny@OA01> 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）

在“增加分區維護操作”部分，提到了對於存在預設條件的分區表增加分區的的兩種辦法，這裡將介紹通過拆分分區的辦法來增加分區。

需要註意：在目標分區拆分後，被拆分的分區會按照拆分規則，將數據進行重分佈。

 

演示實例：

首先，將測試表的數據分佈還原至初建時的數據分佈態。

--清空測試分區表中的所有數據
Yumiko_sunny@OA01> truncate table TEST_RANGE_PARTITION;
Table truncated.


--重新載入測試分區表的數據
Yumiko_sunny@OA01> insert into TEST_RANGE_PARTITION select * from emp;
14 rows created.


Yumiko_sunny@OA01> commit;
Commit complete.


--重新收集測試表的統計信息
Yumiko_sunny@OA01> analyze table TEST_RANGE_PARTITION compute statistics;
Table analyzed.


--查看此時，數據在分區間的分佈情況
Yumiko_sunny@OA01> 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的具體數據信息。

Yumiko_sunny@OA01> 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中
Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION split partition TEST_RANGE_SAL_MAX at (4000) into
  2  (partition TEST_RANGE_SAL_04,partition TEST_RANGE_SAL_MAX);
Table altered.


--查看此時測試分區表的分區情況，以及數據分佈情況
Yumiko_sunny@OA01> 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

 

驗證分區中實際的數據內容

Yumiko_sunny@OA01> 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



Yumiko_sunny@OA01> 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進行合併，具體如下： 

--查看當前分區表的分區情況
Yumiko_sunny@OA01> 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



--進行合併分區操作
Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION merge partitions 
  2  TEST_RANGE_SAL_01,TEST_RANGE_SAL_02
  3  into partition TEST_RANGE_SAL_00;
Table altered.



--驗證合併分區後的結果
Yumiko_sunny@OA01> 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

 

 

8、交換分區維護操作（exchange） 

交換分區技術，主要是將一個非分區表的數據同“一個分區表的一個分區”進行數據交換。支持雙向交換，既可以從分區表的分區中遷移到非分區表，也可以從非分區表遷移至分區表的分區中。
原則上，非分區表的結構、數據分佈等，要符合分區表的目標分區的定義規則。

 

演示如下：

首先，清空測試分區表的數據

Yumiko_sunny@OA01> truncate table TEST_RANGE_PARTITION;
Table truncated.


Yumiko_sunny@OA01> 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。

Yumiko_sunny@OA01> create table emp_test as select * from emp where sal < 2000;
Table created.


Yumiko_sunny@OA01> select count(*) from emp_test;
  COUNT(*)
----------
         8

註意，此時非分區表的數據量為8條記錄。

 

 

執行交換分區操作，觀察分區表的記錄變化，以及非分區表的記錄變化

--執行分區交換操作
Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test;
Table altered.



Yumiko_sunny@OA01> 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



Yumiko_sunny@OA01> select count(*) from emp_test;

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

可以看到，通過分去交換，非分區表的數據轉移至分區表中，同時非分區表的記錄被清除。

 

 

再次執行交換分區操作，觀察分區表的記錄變化，以及非分區表的記錄變化

Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test;
Table altered.


Yumiko_sunny@OA01> 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



Yumiko_sunny@OA01> select count(*) from emp_test;

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

可以看到，此時分區表的數據又再次轉移回至非分區表，證明瞭前面所述，分區交換技術，既可以從分區表的分區中遷移到非分區表，也可以從非分區表遷移至分區表的分區中。

 

 

若非分區表的數據，不符合分區表的分區規則，此時交換會拋出ORA-14099錯誤。

--清空上面測試非分區表的數據
Yumiko_sunny@OA01> truncate table emp_test;
Table truncated.


--載入emp的所有數據至該測試非分區表
--之所以使用測試非分區表，是考慮emp表以後做其他實驗時可能還需要其中的數據
--通過這樣操作，測試非分區表的數據，既存在sal<2000的數據，也存在sal>2000的數據
Yumiko_sunny@OA01> insert into emp_test select * from emp;
14 rows created.


Yumiko_sunny@OA01> commit;
Commit complete.



--嘗試交換分區，觀察結果
Yumiko_sunny@OA01> 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子句，可以避免數據校驗，而交換成功。但會存在與分區規則相悖的數據，因此該方法要慎重。

Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test without validation;
Table altered.


Yumiko_sunny@OA01> 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分區表

Yumiko_sunny@OA01> CREATE TABLE "EMPLOYEE_HASH_PART"
  2      ( "EMPNO" NUMBER(4,0),
  3        "ENAME" VARCHAR2(10),
  4        "JOB" VARCHAR2(9),
  5        "MGR" NUMBER(4,0),
  6        "HIREDATE" DATE,
  7        "SAL" NUMBER(7,2),
  8        "COMM" NUMBER(7,2),
  9        "DEPTNO" NUMBER(2,0)
 10      )
 11      PARTITION BY HASH (ENAME)
 12      (
 13      PARTITION EMPLOYEE_PART01,
 14      PARTITION EMPLOYEE_PART02
 15     );  

Table created.


Yumiko_sunny@OA01> insert into EMPLOYEE_HASH_PART select * from emp;
14 rows created.


Yumiko_sunny@OA01> commit;
Commit complete.


Yumiko_sunny@OA01> 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

 

執行收縮分區操作

Yumiko_sunny@OA01> alter table EMPLOYEE_HASH_PART coalesce partition;
Table altered.


Yumiko_sunny@OA01> 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分區中只有一個分區時，此時無法進行收縮操作。

Yumiko_sunny@OA01> 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



Yumiko_sunny@OA01> 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

 

至此，關於分區表的日常維護操作及註意事項總結結束，後續會抽時間總結分區表索引的維護。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 八大方法搞清 "我是誰" 之SQLServer版本
    你正在使用 SQL Server 的哪個版本？ "我是誰" SQL Server 及其組件的版本、版本類別和更新級別？ 作為一個SQL Server資料庫管理者或維護、支持人員，應該會經常問自己這樣一個問題：我當前SQL Server版本號是？當前版本已經有的累計更新、安全更新包有哪些？這麼多包要選 ...
  • zookeeper集群的搭建以及hadoop ha的相關配置
    1、環境 centos7 hadoop2.6.5 zookeeper3.4.9 jdk1.8 master作為active主機，data1作為standby備用機，三台機器均作為數據節點，yarn資源管理器在master上開啟，在data1上備用，data1上開啟歷史伺服器 主要參考見下表 Data ...
  • InMemory：在記憶體中創建臨時表和表變數
    在Disk-Base資料庫中，如果系統頻繁地創建和更新臨時表，大量的IO操作集中在tempdb中，tempdb很可能成為系統性能的瓶頸。在SQL Server 2016的記憶體（Memory-Optimized）資料庫中，如果考慮使用記憶體優化結構來存儲臨時表，表變數，表值參數的數據，那麼將完全消除IO ...
  • 監控 SQL Server (2005/2008) 的運行狀況
    Microsoft SQL Server 2005 提供了一些工具來監控資料庫。方法之一是動態管理視圖。動態管理視圖 (DMV) 和動態管理函數 (DMF) 返回的伺服器狀態信息可用於監控伺服器實例的運行狀況、診斷問題和優化性能。常規伺服器動態管理對象包括：– dm_db_*：資料庫和資料庫對象– ...
  • ORACLE中STATUS為INACTIVE但是SERVER為SHARED狀態的會話淺析
    我們知道當ORACLE資料庫啟用共用伺服器模式時，通過共用伺服器模式連接到資料庫的會話是有一些特征的。在v$session裡面，其SERVER的狀態一般為SHARED和NONE， 為SHARED時，表示當前會話正在執行SQL語句，其占用共用伺服器進程，會話的STATUS狀態為ACTIVE；當會話狀態... ...
  • Security Policy：行級安全（Row-Level Security）
    行級安全RLS(Row-Level Security)是在數據行級別上控制用戶的訪問，控制用戶只能訪問資料庫表的特定數據行。斷言是邏輯表達式，在SQL Server 2016中，RLS是基於安全斷言（Security Predicate）的訪問控制，Security Predicate是由內聯表值函 ...
  • （資源整理）帶你入門Spark
    一、Spark簡介： 以下是百度百科對Spark的介紹： Spark 是一種與 Hadoop 相似的開源集群計算環境，但是兩者之間還存在一些不同之處，這些有用的不同之處使 Spark 在某些工作負載方面表現得更加優越，換句話說，Spark 啟用了記憶體分佈數據集，除了能夠提供互動式查詢外，它還可以優化 ...
  • 輕鬆精通資料庫管理之道——運維巡檢系列
    巡檢是資料庫管理員保證資料庫健康的必要維護項，全面的巡檢可以及早的發現問題、解決問題、預防問題。 很多資料庫維護人員其實對資料庫瞭解的並不深入（常常集中在傳統行業），不是專業的DBA，同時又身兼多職（業務、軟體、網路、硬體），在每天繁雜的工作中已經身心俱疲。這樣的一種狀態也必然讓系統管理員即使有意精 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類