oracle 12C 查多个分区,学习笔记:Oracle 12C支持多个分区维护multiple partitions同时操作...

天萃荷净

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 oracleplus;

User created.

SQL> grant dba to xff;

Grant succeeded.

SQL> conn xff/oracleplus@ff

Connected.

SQL> show con_name;

CON_NAME

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

FF

1.创建分区表RANGE PARTITIONS

SQL> CREATE TABLE t_oracleplus

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_oracleplus';

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_oracleplus 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_oracleplus';

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_oracleplus 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_oracleplus';

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.

2.Oracle插入分区数据

SQL>INSERT INTO t_oracleplus VALUES('www.oracleplus.com',SYSDATE-100);

1 row created.

SQL> INSERT INTO t_oracleplus VALUES('www.oracleplus.com',SYSDATE-200);

1 row created.

SQL> INSERT INTO t_oracleplus VALUES('www.oracleplus.com',SYSDATE-300);

1 row created.

SQL> INSERT INTO t_oracleplus VALUES('www.oracleplus.com',SYSDATE-10);

1 row created.

SQL> commit;

Commit complete.

SQL> col name for a20

SQL> select * from t_oracleplus;

NAME TIME_ID

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

www.oracleplus.com 17-FEB-12

www.oracleplus.com 27-MAY-12

www.oracleplus.com 04-SEP-12

www.oracleplus.com 03-DEC-12

SQL>select * from t_oracleplus PARTITION(XFF_2012_03);

NAME TIME_ID

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

www.oracleplus.com 17-FEB-12

SQL> select * from t_oracleplus PARTITION(XFF_2012_06);

NAME TIME_ID

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

www.oracleplus.com 27-MAY-12

SQL> select * from t_oracleplus PARTITION(XFF_2012_09);

no rows selected

SQL> select * from t_oracleplus PARTITION(XFF_2012);

NAME TIME_ID

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

www.oracleplus.com 04-SEP-12

www.oracleplus.com 03-DEC-12

3.TRUNCATE 多个分区

SQL> Alter table t_oracleplus truncate partitions XFF_2012_03, XFF_2012_06, XFF_2012_09;

Table truncated.

--剩下两条记录存在于XFF_2012中

SQL> select * from t_oracleplus;

NAME TIME_ID

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

www.oracleplus.com 04-SEP-12

www.oracleplus.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_oracleplus';

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_oracleplus 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_oracleplus';

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_oracleplus 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_oracleplus';

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来说,带来了不少的方便,省去了很多重复行工作.

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle 12C支持多个分区维护multiple partitions同时操作