一、機器環境

資料庫伺服器環境

l  硬體環境：

一臺虛擬機器

虛擬機器名稱：Oracle Enterprise Linux 5.8 64-bit(Oracle 12CR1 racdb01)

所在目錄：J:\Virtual Machines\Oracle Linux 5.8 -Oracle 12CR1 racdb01

         J:\Virtual Machines\OracleLinux58ShareDisk-Oracle 12CR1

 硬碟：
 J:\Virtual Machines\Oracle Linux 5.8 -Oracle 12CR1 racdb01\OracleLinux5.8.vmdk                                                    30GB

 J:\Virtual Machines\OracleLinux58ShareDisk-Oracle 12CR1\DATADISK1.vmdk                                                             8GB 
 J:\Virtual Machines\OracleLinux58ShareDisk-Oracle 12CR1\DATADISK2.vmdk                                                             8GB 
 J:\Virtual Machines\OracleLinux58ShareDisk-Oracle 12CR1\OCR_VOTE1.vmdk                                                           6GB                                                    
 J:\Virtual Machines\OracleLinux58ShareDisk-Oracle 12CR1\OCR_VOTE2.vmdk                                                           6GB

 記憶體：

          5GB

 CPU：

l  軟體環境：

作業系統：Oracle Linux Server release 5.8

Oracle：12cR1 12.1.0.1.0

Container資料庫名稱: CDB$ROOT

Pluggable Database名稱: PDB$SEED

                                      RACPDB

節點名稱：racdb01

 

l  網路環境：

主機所在的公網和私網資訊如下：

機器名	IP地址	地址名稱	備註
racdb01	192.168.1.111	racdb01	公共地址
	192.168.1.112	racdb01-vip	虛擬地址
	10.10.1.11	racdb01-priv	心跳線地址
racdb02			公共地址
			虛擬地址
			心跳線地址
Oracle RAC叢集	192.168.1.115	racdb-cluster-scan	叢集服務地址

 

二、partitioning新特性介紹

本文章主要介紹在Oracle 12c內引入的新分割槽方法或新分割槽特性。

- Multi partition maintenance. 
- Online partition operations.
- Interval Reference Partitioning.
- Asynchronous Global Index maintenance. 

 

 三、Multi partition maintenance使用例子

1，開始前，建立新的分割槽表用於該文章的例子：

CREATE TABLE Tab_tst1  

       ( COL1_ID        NUMBER(6) PRIMARY KEY  

       , COL2_NAME      VARCHAR2(4000)  

       , P_DATE        DATE  

       )  

   PARTITION BY RANGE (P_DATE)  

     (PARTITION Tab_tst1_PART1 VALUES LESS THAN (TO_DATE('01-JUL-2013','DD-MON-YYYY')),  

      PARTITION Tab_tst1_PART2 VALUES LESS THAN (TO_DATE('01-AUG-2013','DD-MON-YYYY')),  

      PARTITION Tab_tst1_PART3 VALUES LESS THAN (TO_DATE('01-SEP-2013','DD-MON-YYYY')),  

      PARTITION Tab_tst1_PART4 VALUES LESS THAN (TO_DATE('01-OCT-2013','DD-MON-YYYY')),  

      PARTITION Tab_tst1_PART5 VALUES LESS THAN (TO_DATE('01-NOV-2013','DD-MON-YYYY')),  

      PARTITION Tab_tst1_PART6 VALUES LESS THAN (TO_DATE('01-DEC-2013','DD-MON-YYYY')),  

       PARTITION Tab_tst1_PART7 VALUES LESS THAN (TO_DATE('01-JAN-2014','DD-MON-YYYY')),  

      PARTITION Tab_tst1_PART_MAX VALUES LESS THAN (MAXVALUE))  

   /  

Table created  

SQL> CREATE TABLE Tab_tst2  

      ( COL1_ID        NUMBER(6) PRIMARY KEY  

      , COL2_NAME      VARCHAR2(4000)  

      , P_DATE        DATE  

      )  

  PARTITION BY RANGE (P_DATE)  

    (PARTITION Tab_tst1_PART1 VALUES LESS THAN (TO_DATE('01-JUL-2013','DD-MON-YYYY')))  

  /  

Table created  

SQL>   CREATE TABLE Tab_tst3  

      ( COL1_ID        NUMBER(6) PRIMARY KEY  

        , COL2_NAME      VARCHAR2(4000)  

        , P_DATE        DATE  

        )  

    PARTITION BY SYSTEM  

    ( PARTITION Tab_tst3_PART1,  

      PARTITION Tab_tst3_PART2,  

      PARTITION Tab_tst3_PART3,  

     PARTITION Tab_tst3_PART_MAX);  

Table created  

2，多分割槽維護:

可以同時在多個分割槽上執行add、drop、truncate、merge、split維護操作。

A、 DROP多個分割槽:

新的“ALTER TABLE … DROP PARTITIONS” 幫助使用單個語句刪除多個分割槽或子分割槽。

例子:

SQL> ALTER TABLE Tab_tst1 DROP PARTITIONS Tab_tst1_PART5, Tab_tst1_PART6, Tab_tst1_PART7;  

Table altered.

限制: - 不可以刪除表的所有分割槽。 - 如果表有單個分割槽，將碰到如下錯誤: ORA-14083: cannot drop the only partition of a partitioned table.

SQL> ALTER TABLE Tab_tst2 DROP PARTITIONS Tab_tst1_PART1; 

ALTER TABLE Tab_tst2 DROP PARTITIONS Tab_tst1_PART1

                                     *

ERROR at line 1:

ORA-14083: cannot drop the only partition of a partitioned table

 

-在操作完成後必需重建Global and local indexes 索引，除非你指定UPDATE INDEXES或UPDATE GLOBAL INDEXES子句。 例子:

SQL>ALTER TABLE Tab_tst1 DROP PARTITIONS Tab_tst1_PART3,Tab_tst1_PART4 UPDATE GLOBAL INDEXES;  

Table altered.

SQL> ALTER TABLE Tab_tst1 DROP PARTITIONS Tab_tst1_PART2 UPDATE INDEXES;

Table altered.

B、 Truncate多個分割槽:

新的“ALTER TABLE … TRUNCATE PARTITIONS” 幫助使用單個語句truncate多個分割槽或子分割槽，本地索引在操作中被truncated。

例子:

SQL>  ALTER TABLE Tab_tst1 TRUNCATE PARTITIONS Tab_tst1_PART1,Tab_tst1_PART2, Tab_tst1_PART3;

Table truncated.

 限制: --在操作完成後必需重建Global indexes 索引，除非你指定UPDATE INDEXES或UPDATE GLOBAL INDEXES子句。

 

例子:

SQL> ALTER TABLE Tab_tst1 TRUNCATE PARTITIONS Tab_tst1_PART1,Tab_tst1_PART2, Tab_tst1_PART3 UPDATE GLOBAL INDEXES;  

Table truncated.

SQL> ALTER TABLE Tab_tst1 TRUNCATE PARTITIONS Tab_tst1_PART1,Tab_tst1_PART2, Tab_tst1_PART3 UPDATE INDEXES;  

Table truncated.

C、 ADD多個分割槽:

命令“ALTER TABLE … ADD PARTITION … PARTITION … PARTITION … BEFORE” 幫助使用單個語句新增多個分割槽或子分割槽。

例子:

SQL> ALTER TABLE Tab_tst3 ADD  PARTITION Tab_tst3_PART4,PARTITION Tab_tst3_PART5  BEFORE PARTITION Tab_tst3_PART_MAX;

Table altered.

 

在11.2.0.3 database 中嘗試相同的命令;

view plaincopy to clipboardprint?

 

限制:

--分割槽表必須是系統分割槽表。嘗試在非系統分割槽表上新增多個分割槽導致“ORA-14703: The AFTER clause can be used to ADD PARTITION only to a System Partitioned table.”

SQL> ALTER TABLE Tab_tst1 ADD 

      PARTITION Tab_tst1_PART4, 

      PARTITION Tab_tst1_PART5 

      BEFORE PARTITION Tab_tst1_PART_MAX;

ALTER TABLE Tab_tst1 ADD

            *

ERROR at line 1:

ORA-14703: The AFTER clause can be used to ADD PARTITION only to a System Partitioned table.

 

D、 合併多個分割槽:

新的“ALTER TABLE … MERGE PARTITIONS” 幫助使用單個語句merge多個分割槽或子分割槽。當合並多個分割槽時，本地索引和全域性索引操作和用於繼承未指定物理屬性的語義和合並的兩個分割槽相同。

例子:

SQL>  ALTER TABLE Tab_tst1 MERGE PARTITIONS Tab_tst1_PART5, Tab_tst1_PART6, Tab_tst1_PART7;

Table altered.

 

E、 分割成多個分割槽:

你可以使用如下語法：

ALTER TABLE Tab_tst1 SPLIT PARTITION Tab_tst1_PART1 INTO 

  ( PARTITION Tab_tst1_PART1_1 VALUES LESS THAN (TO_DATE('01-JUN-2013','dd-MON-yyyy')), 

    PARTITION Tab_tst1_PART1_2 VALUES LESS THAN (TO_DATE('15-JUN-2013','dd-MON-yyyy')), 

    PARTITION Tab_tst1_PART1_3 VALUES LESS THAN (TO_DATE('30-JUN-2013','dd-MON-yyyy')), 

PARTITION Tab_tst1_PART1_4 ); 

 

 

四、Online partition operations使用例子

A、 聯機遷移分割槽:

在聯機遷移分割槽期間DML被允許。“ALTER TABLE … MOVE PARTITIONS”成為non-blocking的聯機DDL。Global indexes and local indexes在聯機遷移分割槽期間被維護，因此不再需要手工索引重建。聯機分割槽遷移為現行的MOVE PARTITION命令刪除只讀狀態。

 

例子:

SQL>  ALTER TABLE Tab_tst1 MOVE PARTITION Tab_tst1_PART1 ONLINE; 

Table altered.

 

SQL> ALTER TABLE Tab_tst1 MOVE PARTITION   Tab_tst1_PART1 ONLINE UPDATE INDEXES; 

Table altered.

 

SQL>  ALTER TABLE Tab_tst1 MOVE PARTITION  Tab_tst1_PART1 ONLINE UPDATE GLOBAL INDEXES; 

Table altered.

 

限制:

--IOT表上的聯機操作不被支援

 

B、 聯機壓縮分割槽:

在聯機壓縮分割槽期間，Global indexes and local indexes被維護，因此不再需要手工索引重建。

 

例子:

-啟用基本的分割槽壓縮操作:

SQL> ALTER TABLE Tab_tst1 MOVE PARTITION Tab_tst1_PART1 COMPRESS BASIC UPDATE INDEXES ONLINE; 

Table altered.

- ROW STORE COMPRESS ADVANCED /FOR OLTP:Oracle資料庫在表上的所有DML操作期間壓縮資料。這種形式的壓縮推薦在OLTP環境使用。在早期版本內，使用COMPRESS FOR OLTP啟用Advanced Row Compression。這個語法已被廢棄(FOR OLTP在12.1內仍合法)。

SQL>  ALTER TABLE Tab_tst1 MOVE PARTITION Tab_tst1_PART1   COMPRESS FOR OLTP UPDATE INDEXES ONLINE; 

Table altered.

 

SQL> ALTER TABLE Tab_tst1 MOVE PARTITION Tab_tst1_PART1 ROW STORE COMPRESS ADVANCED  UPDATE INDEXES ONLINE;

Table altered.

 

- COMPRESS FOR QUERY:該壓縮選項在data warehousing環境有用。

SQL>  ALTER TABLE Tab_tst1 MOVE PARTITION Tab_tst1_PART1   COMPRESS FOR QUERY UPDATE INDEXES ONLINE; 

Table altered.

 

- COMPRESS FOR ARCHIVE:該壓縮選項對壓縮將儲存很長一段時間的資料有用。

SQL>  ALTER TABLE Tab_tst1 MOVE PARTITION Tab_tst1_PART1   COMPRESS FOR ARCHIVE UPDATE INDEXES ONLINE; 

Table altered.

 

五、Interval Reference Partitioning使用例子

Reference partitioning是在Oracle 11g內引入的一種分割槽方法。使用Reference partitioning，子表可以從父表繼承分割槽特性。由於在設計分割槽方案時，有一個典型的問題：不是所有表有需在其上分割槽的相同列。當你希望使用同分割槽主表相同的方式分割槽一個子表時(無相同列，而又不希望僅僅為了分割槽在子表中引入它們)。

 

從12c開始，你可以使用interval分割槽表作為reference partitioning的主表。

 

例子:

SQL> CREATE TABLE Tab_tst4(COL1_ID NUMBER PRIMARY KEY, COL2_NAME  INT)

PARTITION BY RANGE(COL2_NAME) INTERVAL (10) 

(PARTITION PART1 VALUES LESS THAN (10)); 

Table created.

 

 

SQL> CREATE TABLE Tab_tst4_child(COL1_ID_FK INT NOT NULL, COL2_NAME INT,CONSTRAINT COL1_ID_FK FOREIGN KEY(COL1_ID_FK) REFERENCES Tab_tst4(COL1_ID)) 

PARTITION BY REFERENCE(COL1_ID_FK); 

Table created.

 

SQL> conn pdbadmin/pdbadmin@racpdb

Connected.

SQL> INSERT INTO Tab_tst4 VALUES(10, 10); 

1 row created.

 

SQL> INSERT INTO Tab_tst4 VALUES(20, 20); 

1 row created.

 

SQL> INSERT INTO Tab_tst4 VALUES(30, 30); 

1 row created.

 

SQL> commit;

Commit complete.

SQL> conn sys/oracle@racpdb as sysdba

Connected.

SQL> col table_name for a20

SQL>  SELECT table_name, partition_position, high_value, interval 

  2    FROM DBA_TAB_PARTITIONS WHERE table_name IN ('TAB_TST4', 'TAB_TST4_CHILD') 

  3    AND TABLE_OWNER = 'PDBADMIN' 

  4    ORDER BY 1, 2; 

 

TABLE_NAME           PARTITION_POSITION HIGH_VALUE                                                                       INT

-------------------- ------------------ -------------------------------------------------------------------------------- ---

TAB_TST4                              1 10                                                                               NO

TAB_TST4                              2 20                                                                               YES

TAB_TST4                              3 30                                                                               YES

TAB_TST4                              4 40                                                                               YES

TAB_TST4_CHILD                        1                                                                                  NO

 

六、Asynchronous Global Index maintenance使用例子

A、DBMS_PART.CLEANUP_GIDX:

在分割槽表上的維護操作可以留下指向不存在資料段的全域性索引。

Oracle 12c引入了新的稱為DBMS_PART.CLEANUP_GIDX的過程。這個過程清理全域性索引，並導致效能和儲存管理上的提升。

維護JOB通過SMON以非同步模式執行，清理所有全域性索引；

SYS.PMO_DEFERRED_GIDX_MAINT_JOB  job負責清理所有全域性索引。該job預設被安排在每天上午2:00執行。如果你想主動地清理索引，可以任何時候使用DBMS_SCHEDULER.RUN_JOB執行SYS.PMO_DEFERRED_GIDX_MAINT_JOB  job。也可以基於特定的需求修改SYS.PMO_DEFERRED_GIDX_MAINT_JOB  job的執行時間表。然而，Oracle推薦不要刪除該job。

 

SQL> conn pdbadmin/pdbadmin@racpdb

Connected.

SQL> DESC DBMS_PART

PROCEDURE CLEANUP_GIDX

 Argument Name                  Type                    In/Out Default?

 ------------------------------ ----------------------- ------ --------

 SCHEMA_NAME_IN                 VARCHAR2                IN     DEFAULT

 TABLE_NAME_IN                  VARCHAR2                IN     DEFAULT

PROCEDURE CLEANUP_GIDX_INTERNAL

 Argument Name                  Type                    In/Out Default?

 ------------------------------ ----------------------- ------ --------

 SCHEMA_NAME_IN                 VARCHAR2                IN     DEFAULT

 TABLE_NAME_IN                  VARCHAR2                IN     DEFAULT

 ORPHANS_ONLY_IN                NUMBER(38)              IN     DEFAULT

 NOOP_OKAY_IN                   NUMBER(38)              IN     DEFAULT

PROCEDURE CLEANUP_ONLINE_OP

 Argument Name                  Type                    In/Out Default?

 ------------------------------ ----------------------- ------ --------

 SCHEMA_NAME                    VARCHAR2                IN     DEFAULT

 TABLE_NAME                     VARCHAR2                IN     DEFAULT

 PARTITION_NAME                 VARCHAR2                IN     DEFAULT

 

 

B、DBMS_PART.CLEANUP_ONLINE_OP:

這個過程主動地清理聯機表分割槽遷移操作而不用等待後臺程式SMON執行清理。

PROCEDURE CLEANUP_ONLINE_OP

 Argument Name                  Type                    In/Out Default?

 ------------------------------ ----------------------- ------ --------

 SCHEMA_NAME                    VARCHAR2                IN     DEFAULT

 TABLE_NAME                     VARCHAR2                IN     DEFAULT

 PARTITION_NAME                 VARCHAR2                IN     DEFAULT

 

註釋：

-如果schema_name, table_name 和partition_name被指定，為指定分割槽清理失敗的聯機遷移操作。

-如果schema_name, table_name被指定，為指定表的所有分割槽清理失敗的聯機遷移操作。

-如果僅指定了schema_name，為指定使用者清理失敗的聯機遷移操作。

-如果僅未提供引數，清理系統內所有失敗的聯機遷移操作。

-所有其它情況，raise ORA-20000以通知使用者非法的輸入作為引數。

 

 

七、附錄一:參考文件

Oracle 12c: Managing Partitioning

http://www.oracle-class.com/?p=2930

 

Oracle 12c: DBMS_PART Package: Clean up global indexes on partitioned tables

http://www.oracle-class.com/?p=2915

 

DBMS_PART

http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_part.htm

 

 

八、附錄二: ORA-01950: no privileges on tablespace 'USERS'

1，錯誤描述：

SQL> INSERT INTO Tab_tst4 VALUES(10, 10); 

INSERT INTO Tab_tst4 VALUES(10, 10)

            *

ERROR at line 1:

ORA-01950: no privileges on tablespace 'USERS'

 

2，問題分析

因為在表空間上無配額

 

$ oerr ora 01950

01950, 00000, "no privileges on tablespace '%s'"

// *Cause:  User does not have privileges to allocate an extent in the

//          specified tablespace.

// *Action: Grant the user the appropriate system privileges or grant the user

//          space resource on the tablespace.

 

 

3，解決方案

以DBA使用者執行如下命令：

ALTER USER quota unlimited on ;

or

ALTER USER quota 100M on ;

(依賴於你需要多少空間/想授予)。

 

SQL> conn sys/oracle@racpdb as sysdba

Connected.

 

SQL> alter user pdbadmin quota 1G on users;

User altered.

 

SQL> conn pdbadmin/pdbadmin@racpdb

Connected.

SQL> INSERT INTO Tab_tst4 VALUES(10, 10); 

1 row created.

 

SQL> INSERT INTO Tab_tst4 VALUES(20, 20); 

1 row created.

 

SQL> INSERT INTO Tab_tst4 VALUES(30, 30); 

1 row created.

 

 

4，參考文件：

ORA-01950: no privileges on tablespace 'USERS'? [closed]

http://stackoverflow.com/questions/21671008/ora-01950-no-privileges-on-tablespace-users

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14359/viewspace-1194730/，如需轉載，請註明出處，否則將追究法律責任。