ITPub博客

首页 > 数据库 > Oracle > 【12c Partitioning】Oracle 12c Partitioning特性

【12c Partitioning】Oracle 12c Partitioning特性

翻译 Oracle 作者:landf 时间:2014-06-26 14:29:58 0 删除 编辑

一、机器环境

数据库服务器环境

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,多分区维护:
可以同时在多个分区上执行adddroptruncatemergesplit维护操作。

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 INDEXESUPDATE 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 INDEXESUPDATE 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?

1.  SQL> ALTER TABLE Tab_tst3 ADD  

2.    2    PARTITION Tab_tst3_PART4,  

3.    3    PARTITION Tab_tst3_PART5  

4.    4    BEFORE PARTITION Tab_tst3_PART_MAX;  

5.     

6.  ALTER TABLE Tab_tst3 ADD  

7.    PARTITION Tab_tst3_PART4,  

8.    PARTITION Tab_tst3_PART5  

9.    BEFORE PARTITION Tab_tst3_PART_MAX  

10.    

11. ORA-14043: only one partition may be added  

 

限制:

--分区表必须是系统分区表。尝试在非系统分区表上添加多个分区导致“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使用例子

ADBMS_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

 

 

BDBMS_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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-02-14

  • 博文量
    68
  • 访问量
    492766