ORACLE 12C新特性-在线迁移表或分区

今天测试一下Oracle 12C的一个新功能-在线移动表或分区，这其实在10g版本就已经支持了，难道是官方文档写错了，先不按照文档描述的去测试，看看是否堆表也可以在线迁移。以下是实验过程：
下面先看下11g版本，测试环境是OEL5.7 ORACLE 11.2.0.3。

session 1 > select * from v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production

创建测试表。

session 1 > conn stream/stream

session 1 > create table move_test as select * from dba_tables;

Table created.

session 1 > delete from move_test where rownum=1;

1 row deleted.

SESSION1创建测试表并删除一条记录，没有提交，在SESSION2移动这个表到其他表空间看看是否可以。

session 2 >alter table move_test move tablespace ogg;

alter table move_test move tablespace ogg

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SESSION1回滚DELETE操作。

session 1 >rollback;

Rollback complete.

此时SESSION2可以移动表。

session 2 >alter table move_test move tablespace ogg;

Table altered.

可见，11g的堆表上是不可以直接在线移动表的，在看看12C是否可以呢？

测试环境：OEL5.7 ORACLE 12.1.0.1。

SESSION 1>select BANNER from v$version;

BANNER

——————————————————————————–

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production

PL/SQL Release 12.1.0.1.0 – Production

CORE    12.1.0.1.0      Production

TNS for Linux: Version 12.1.0.1.0 – Production

NLSRTL Version 12.1.0.1.0 – Production

创建测试表。

SESSION 1>create table move_test as select * from dba_tables;

Table created.

SESSION 1>delete from move_test where rownum=1;

1 row deleted.

删除一条记录后不提交，看看其他会话是否可以移动这张表。

SESSION 2>alter table move_test move tablespace test;

alter table move_test move tablespace test

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

直接移动是不行的，那么加上ONLINE子句呢？

SESSION 2>alter table move_test move tablespace move_tbs online;

alter table move_test move tablespace move_tbs online

*

ERROR at line 1:

ORA-01735: invalid ALTER TABLE option

很奇怪吧，ORACLE12C既然说可以在线移动表或分区，为什么移动不了呢？看看官方文档的介绍。

move_table_clause

The move_table_clause lets you relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment, optionally in a different tablespace, and optionally modify any of its storage attributes.

You can also move any LOB data segments associated with the table or partition using the LOB_storage_clause and varray_col_properties clause. LOB items not specified in this clause are not moved.

If you move the table to a different tablespace and the COMPATIBLE parameter is set to 10.0 or higher, then Oracle Database leaves the storage table of any nested table columns in the tablespace in which it was created. If COMPATIBLE is set to any value less than 10.0, then the database silently moves the storage table to the new tablespace along with the table.

ONLINE Clause This clause is valid only for top-level index-organized tables and for nested table storage tables that are index organized. Specify ONLINE if you want DML operations on the index-organized table to be allowed during rebuilding of the primary key index of the table.

Restrictions on Moving Tables Online Moving tables online is subject to the following restrictions:

•You cannot combine this clause with any other clause in the same statement.

• You cannot specify this clause for a partitioned index-organized table.

• Parallel DML and direct path INSERT operations require an exclusive lock on the table. Therefore, these operations are not supported concurrently with an ongoing online table MOVE, due to conflicting locks.

• You cannot specify this clause if the index-organized table contains any LOB, VARRAY, Oracle-supplied type, or user-defined object type columns.

  
  

原来只支持IOT表，可是在10g开始就已经支持在线移动IOT表了，下面测试12C版本的这个功能。

12C：

SESSION1>CREATE TABLE MOVE_TEST_IOT(OBJECT_ID NUMBER,OBJECT_NAME VARCHAR2(40),CONSTRAINT O_ID_PK PRIMARY KEY (OBJECT_ID)) ORGANIZATION INDEX;

Table created.

SESSION1>INSERT INTO MOVE_TEST_IOT SELECT OBJECT_ID,OBJECT_NAME FROM DBA_OBJECTS;

90784 rows created.

SESSION1创建表并插入数据，不提交。SESSION2执行迁移操作。

SESSION2>ALTER TABLE MOVE_TEST_IOT MOVE TABLESPACE MOVE_TBS ONLINE;

此时操作会被挂起，等待SESSION1提交。此时SESSION1还可以进行其他DML操作，不会受影响。

SESSION1>delete from MOVE_TEST_IOT where rownum <=10000;

10000 rows deleted.

此时查看到相关的锁信息。

SESSION3>select p.spid,a.serial#, c.object_name,b.session_id,b.oracle_username,b.os_user_name from v$process p,v$session a, v$locked_object b,all_objects c where p.addr=a.paddr and a.process=b.process and c.object_id=b.object_id;

SPID          SERIAL# OBJECT_NAME          SESSION_ID ORACLE_USERNAME OS_USER_NA

———- ———- ——————– ———- ————— ———-

15734            1089 MOVE_TEST_IOT                24 STREAM          oracle

15570            1705 MOVE_TEST_IOT               261 STREAM          oracle

15570            1705 SYS_JOURNAL_91703           261 STREAM          oracle

在线移动表时，会自动生成一张中间表，本案例中间表名字是SYS_JOURNAL_91703，迁移过程完成后，ORACLE会自动清理中间表。下面看下SESSION1提交后，移动是否可以成功完成。

SESSION1>commit;

Commit complete.

在SESSION1提交后，SESSION2迁移表操作成功完成。

SESSION2>ALTER TABLE MOVE_TEST_IOT MOVE TABLESPACE USERS ONLINE;

Table altered.

在线迁移IOT表并不能说是12C的新功能，在10g版本就已经支持在线迁移IOT表了(测试过程和12C的测试过程一样，这里就不描述了)，我猜测是在下一个版本可能ORACLE会推出在线迁移堆表的特性，之所以在12.1.0.1版本提前说支持在线迁移表了，可能是ORACLE的一贯作风(忽悠)，但一般ORACLE提说出这个功能在下一个版本就会推出，一起期待吧。

———————————————————-end——————————————————————