转载

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

正文到此结束

所属分类：编程技术

本文标签： sql ACE cat MySQL5 Oracle value Select db App tab 空间 update IDE mysql 数据 Enterprise
版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
本文海报： 生成海报一生成海报二