联系：手机/微信(+86 17813235971) QQ(107644445)
标题：ORACLE 12C 支持multiple partitions同时操作
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
ORACLE 12C在分区维护方面有了不少的增强,在12C的beta版本中已经支持多分区的add/truncate/drop/merge操作,大大的提高了分区维护的效率.
数据库版本
SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit 0 PL/SQL Release 12.1.0.0.2 0 CORE 12.1.0.0.2 0 TNS for Linux: Version 12.1.0.0.2 0 NLSRTL Version 12.1.0.0.2 0
在FF PDB中创建xff用户
SQL> alter session set container=ff; Session altered. SQL> create user xff identified by xifenfei; User created. SQL> grant dba to xff; Grant succeeded. SQL> conn xff/xifenfei@ff Connected. SQL> show con_name; CON_NAME ------------------------------ FF
创建分区表RANGE PARTITIONS
SQL> CREATE TABLE t_xifenfei
2 (name varchar2(100),time_id DATE)
3 partition by range(time_id)
4 (partition xff_2006 values less than (TO_DATE('01-01-2007','dd-MM-yyyy')),
5 partition xff_2007 values less than (TO_DATE('01-01-2008','dd-MM-yyyy')),
6 partition xff_2008 values less than (TO_DATE('01-01-2009','dd-MM-yyyy')),
7 partition xff_2009 values less than (TO_DATE('01-01-2010','dd-MM-yyyy')));
Table created.
SQL> SET LONG 30
SQL> select PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';
PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------
XFF_2006 TO_DATE(' 2007-01-01 00:00:00'
XFF_2007 TO_DATE(' 2008-01-01 00:00:00'
XFF_2008 TO_DATE(' 2009-01-01 00:00:00'
XFF_2009 TO_DATE(' 2010-01-01 00:00:00'
ADD 多个分区
SQL> ALTER TABLE t_xifenfei ADD
2 PARTITION XFF_2010 VALUES LESS THAN (TO_DATE('01-01-2011','dd-MM-yyyy')),
3 PARTITION XFF_2011 VALUES LESS THAN (TO_DATE('01-01-2012','dd-MM-yyyy')),
4 PARTITION XFF_2012 VALUES LESS THAN (TO_DATE('01-01-2013','dd-MM-yyyy'));
Table altered.
SQL> select PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';
PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------
XFF_2006 TO_DATE(' 2007-01-01 00:00:00'
XFF_2007 TO_DATE(' 2008-01-01 00:00:00'
XFF_2008 TO_DATE(' 2009-01-01 00:00:00'
XFF_2009 TO_DATE(' 2010-01-01 00:00:00'
XFF_2010 TO_DATE(' 2011-01-01 00:00:00'
XFF_2011 TO_DATE(' 2012-01-01 00:00:00'
XFF_2012 TO_DATE(' 2013-01-01 00:00:00'
7 rows selected.
Split多个分区
SQL> ALTER TABLE t_xifenfei split PARTITION XFF_2012 INTO
2 (PARTITION XFF_2012_03 VALUES LESS THAN (TO_DATE('01-03-2012','dd-MM-yyyy')),
3 PARTITION XFF_2012_06 VALUES LESS THAN (TO_DATE('01-06-2012','dd-MM-yyyy')),
4 PARTITION XFF_2012_09 VALUES LESS THAN (TO_DATE('01-09-2012','dd-MM-yyyy')),
5 PARTITION XFF_2012);
Table altered.
SQL> select PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';
PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------
XFF_2006 TO_DATE(' 2007-01-01 00:00:00'
XFF_2007 TO_DATE(' 2008-01-01 00:00:00'
XFF_2008 TO_DATE(' 2009-01-01 00:00:00'
XFF_2009 TO_DATE(' 2010-01-01 00:00:00'
XFF_2010 TO_DATE(' 2011-01-01 00:00:00'
XFF_2011 TO_DATE(' 2012-01-01 00:00:00'
XFF_2012 TO_DATE(' 2013-01-01 00:00:00'
XFF_2012_03 TO_DATE(' 2012-03-01 00:00:00'
XFF_2012_06 TO_DATE(' 2012-06-01 00:00:00'
XFF_2012_09 TO_DATE(' 2012-09-01 00:00:00'
10 rows selected.
插入分区数据
SQL>INSERT INTO t_xifenfei VALUES('www.xifenfei.com',SYSDATE-100);
1 row created.
SQL> INSERT INTO t_xifenfei VALUES('www.xifenfei.com',SYSDATE-200);
1 row created.
SQL> INSERT INTO t_xifenfei VALUES('www.xifenfei.com',SYSDATE-300);
1 row created.
SQL> INSERT INTO t_xifenfei VALUES('www.xifenfei.com',SYSDATE-10);
1 row created.
SQL> commit;
Commit complete.
SQL> col name for a20
SQL> select * from t_xifenfei;
NAME TIME_ID
-------------------- ---------
www.xifenfei.com 17-FEB-12
www.xifenfei.com 27-MAY-12
www.xifenfei.com 04-SEP-12
www.xifenfei.com 03-DEC-12
SQL>select * from t_xifenfei PARTITION(XFF_2012_03);
NAME TIME_ID
-------------------- ---------
www.xifenfei.com 17-FEB-12
SQL> select * from t_xifenfei PARTITION(XFF_2012_06);
NAME TIME_ID
-------------------- ---------
www.xifenfei.com 27-MAY-12
SQL> select * from t_xifenfei PARTITION(XFF_2012_09);
no rows selected
SQL> select * from t_xifenfei PARTITION(XFF_2012);
NAME TIME_ID
-------------------- ---------
www.xifenfei.com 04-SEP-12
www.xifenfei.com 03-DEC-12
TRUNCATE 多个分区
SQL> Alter table t_xifenfei truncate partitions XFF_2012_03, XFF_2012_06, XFF_2012_09;
Table truncated.
--剩下两条记录存在于XFF_2012中
SQL> select * from t_xifenfei;
NAME TIME_ID
-------------------- ---------
www.xifenfei.com 04-SEP-12
www.xifenfei.com 03-DEC-12
SQL> SELECT SUBOBJECT_NAME,object_id,data_object_id from user_objects where SUBOBJECT_NAME like 'XFF_2012_0%';
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
XFF_2012_09 90603 90603 <---为什么没有变
XFF_2012_06 90602 90606
XFF_2012_03 90601 90605
SQL> select PARTITION_NAME,HIGH_VALUE,SEGMENT_CREATED FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';
PARTITION_NAME HIGH_VALUE SEGM
------------------------------ ------------------------------ ----
XFF_2006 TO_DATE(' 2007-01-01 00:00:00' NO
XFF_2007 TO_DATE(' 2008-01-01 00:00:00' NO
XFF_2008 TO_DATE(' 2009-01-01 00:00:00' NO
XFF_2009 TO_DATE(' 2010-01-01 00:00:00' NO
XFF_2010 TO_DATE(' 2011-01-01 00:00:00' NO
XFF_2011 TO_DATE(' 2012-01-01 00:00:00' NO
XFF_2012 TO_DATE(' 2013-01-01 00:00:00' YES
XFF_2012_03 TO_DATE(' 2012-03-01 00:00:00' YES
XFF_2012_06 TO_DATE(' 2012-06-01 00:00:00' YES
XFF_2012_09 TO_DATE(' 2012-09-01 00:00:00' NO
--XFF_2012_09因为块延迟创建,没有segment导致truncate对应的dataobj#不变
10 rows selected.
DROP 多个分区
SQL> Alter table t_xifenfei DROP partitions XFF_2012_03, XFF_2012_06, XFF_2012_09;
Table altered.
SQL> select PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';
PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------
XFF_2006 TO_DATE(' 2007-01-01 00:00:00'
XFF_2007 TO_DATE(' 2008-01-01 00:00:00'
XFF_2008 TO_DATE(' 2009-01-01 00:00:00'
XFF_2009 TO_DATE(' 2010-01-01 00:00:00'
XFF_2010 TO_DATE(' 2011-01-01 00:00:00'
XFF_2011 TO_DATE(' 2012-01-01 00:00:00'
XFF_2012 TO_DATE(' 2013-01-01 00:00:00'
7 rows selected.
MERGE 多分区
SQL> Alter table t_xifenfei merge partitions XFF_2006, XFF_2007, XFF_2008 into partition XFF_OLD;
Table altered.
SQL> select PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';
PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------
XFF_2009 TO_DATE(' 2010-01-01 00:00:00'
XFF_2010 TO_DATE(' 2011-01-01 00:00:00'
XFF_2011 TO_DATE(' 2012-01-01 00:00:00'
XFF_2012 TO_DATE(' 2013-01-01 00:00:00'
XFF_OLD TO_DATE(' 2009-01-01 00:00:00'
本测试是基于Range partitions进行,其实在ORACLE 12C中对于分区表的维护做了比较大的增强,上面试验的多分区操作,也支持List partitions和subpartitions.ddl一次性操作多个分区,给分区经常做维护的DBA来说,带来了不少的方便,省去了很多重复行工作.