Oracle 12c online move Partitions

在Oracle12c中，可以在线进行表分区和子分区的move操作，下面是一个示例。

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> alter session set container=pdb1;
Session altered.
SQL> CREATE USER bmw IDENTIFIED BY mysql57 CONTAINER=CURRENT;
User created.
SQL> GRANT CREATE SESSION TO bmw CONTAINER=CURRENT;
Grant succeeded.
SQL> GRANT dba TO bmw CONTAINER=CURRENT;
Grant succeeded.
[oracle@odb12c ~]$ sqlplus bmw/mysql57@pdb1
SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 12 10:52:02 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> CREATE TABLE t
(  2  tid            NUMBER,
   3  name   VARCHAR2(50),
  4   cdate  DATE)
  5  PARTITION BY RANGE (cdate)
  6  (PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE bmw,
 PA  7  RTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE bmw);
Table created.

SQL> INSERT INTO t
S  2  ELECT level,
  3         'name for ' || level,
  4         CASE
   5          WHEN MOD(level,2) = 0 THEN TO_DATE('01/07/2014', 'DD/MM/YYYY')
  6           ELSE TO_DATE('01/07/2015', 'DD/MM/YYYY')
  7         END
  8  FROM   dual CONNECT BY level <= 1000;
1000 rows created.

SQL> commit;
Commit complete.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'T');
PL/SQL procedure successfully completed.
SQL> l
  1  SELECT table_name,
  2  partition_name,
  3  tablespace_name,
  4  num_rows
  5  FROM   dba_tab_partitions
  6  WHERE  table_name='T'
  7* ORDER BY 1,2
SQL> /
TABLE_NAME           PARTITION_NAME       TABLESPACE_NAME        NUM_ROWS
-------------------- -------------------- -------------------- ----------
T                    PART_2014            BMW                         500
T                    PART_2015            BMW                         500


SQL> ALTER TABLE T MOVE PARTITION part_2015 ONLINE TABLESPACE ben UPDATE INDEXES;
Table altered.

SQL> SELECT table_name,
p  2  artition_name,
ta  3  blespace_name,
  4  num_rows
  5  FROM   dba_tab_partitions
  6  WHERE  table_name='T'
  7  ORDER BY 1,2;


TABLE_NAME           PARTITION_NAME       TABLESPACE_NAME        NUM_ROWS
-------------------- -------------------- -------------------- ----------
T                    PART_2014            BMW                         500
T                    PART_2015            BEN                         500

这里可以看到PART_2015 分区被move到了ben表空间，这对于分区表的数据归档带来操作的便利。


而在Oracle 11g的版本中无法使用online关键字对表分区进行move

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security and Real Application Testing options

SQL> ALTER TABLE t1 MOVE PARTITION part_2015 ONLINE TABLESPACE users UPDATE INDEXES;
ALTER TABLE t1 MOVE PARTITION part_2015 ONLINE TABLESPACE users UPDATE INDEXES
                                        *
ERROR at line 1:
ORA-14020: this physical attribute may not be specified for a table partition

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26390465/viewspace-1832459/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26390465/viewspace-1832459/