本文深入介绍了Oracle 12c引入的多项分区新特性和在线分区操作，包括多分区维护、在线迁移和压缩、区间引用分区、异步全局索引维护等。详细演示了如何在分区表上执行多种维护操作，优化数据库性能。
数据库服务器环境
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 | 集群服务地址 |
本文章主要介绍在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?
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使用例子
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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/14359/viewspace-1194730/
扫一扫
1374
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
