本文共 6051 字，大约阅读时间需要 20 分钟。
概念https://www.cndba.cn/leo1990/article/2159
https://www.cndba.cn/leo1990/article/2159https://www.cndba.cn/leo1990/article/2159https://www.cndba.cn/leo1990/article/2159
https://www.cndba.cn/leo1990/article/2159
Nonpartitioned tables can be moved as an online operation without blocking any concurrent DML operations. A table move operation now also supports automatic index maintenance as part of the move.
非分区表可以作为在线操作移动，而不会阻塞任何并发的DML操作。一个表移动操作现在也支持自动索引维护作为移动的一部分。
Data maintenance on nonpartitioned tables does not require any maintenance window since it does not impact any DML or query operation.
非分区表上的数据维护不需要任何维护窗口，因为它不会影响任何DML或查询操作。
When you use the ONLINE keyword with this statement, data manipulation language (DML) operations can continue to run uninterrupted on the table that is being moved. If you do not include the ONLINE keyword, then concurrent DML operations are not possible on the data in the table during the move operation.
当您使用该语句使用在线关键字时，数据操作语言(DML)操作可以继续在被移动的表上不间断地运行。如果不包含在线关键字，那么在移动操作期间，表中的数据不可能并发DML操作。
限制
这里有一些关于表的在线移动的限制。
它不能与任何其他子句相结合。
它不能用于分区的索引有组织的表或索引有索引的表，其中有一个列定义为LOB、VARRAY、oracle提供的类型，或者用户定义的对象类型。
如果表上有一个域索引，就不能使用它。
并行DML和直接路径插入不支持正在进行在线活动的对象。
实验
有时因为表空间转移或整理碎片的目的需要MOVE TABLE， 但是该操作在12.2之前如果有业务在运行中常常因为排它锁的原因而失败无法有时间点切入。或move 成功后会导致索引无效而又影响了业务，无法像索引那个可以rebuild online, 在12C版本引入了几个重要的online操作，其实move table online就是12C R2版本引入的一个重要特性。
1 Oracle 11g move table 索引会失效
[oracle@localhost ~]$ sqlplus "/as sysdba"
1.1 创建表 及插入数据
SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 21 22:27:34 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CREATE TABLESPACE test
DATAFILE '/u01/app/oracle/oradata/cndba/test01.dbf'
SIZE 1M AUTOEXTEND ON NEXT 1M; 2 3
Tablespace created.
SQL> CREATE TABLE t1
(id NUMBER,
description VARCHAR2(50),
created_date DATE,
CONSTRAINT t1_pk PRIMARY KEY (id)
) tablespace test;
Table created.
SQL> INSERT INTO t1
SELECT level,
'Description for ' || level,
CASE
WHEN MOD(level,2) = 0 THEN TO_DATE('01/07/2015', 'DD/MM/YYYY')
ELSE TO_DATE('01/07/2016', 'DD/MM/YYYY')
END
FROM dual
CONNECT BY level <= 1000;
COMMIT;
1000 rows created.
SQL> COMMIT;
Commit complete.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'T1');
PL/SQL procedure successfully completed.
1.2 move online 分区表 报错
SQL> ALTER TABLE t1 MOVE ONLINE;
ALTER TABLE t1 MOVE ONLINE
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
1.3 move 非分区表 ，不带 online 参数
SQL> ALTER TABLE t1 MOVE;
Table altered.
1.4 查看move table 之后索引失效
SQL> select index_name from user_Indexes where table_name='T1';
INDEX_NAME
------------------------------------------------------------
T1_PK
SQL> col index_name for a25
SQL> col table_name for a20
SQL> col COMPRESSION for a20
SQL> set lines 400
SQL> select table_name,index_name,status,COMPRESSION,blevel,leaf_blocks from user_Indexes where index_name ='T1_PK';
TABLE_NAME INDEX_NAME STATUS COMPRESSION BLEVEL LEAF_BLOCKS
-------------------- ------------------------- ---------------- -------------------- ---------- -----------
T1 T1_PK UNUSABLE DISABLED 1 2
2 Oracle 12c move table
2.1 创建表并插入数据
[oracle@host1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 21 22:45:12 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> CREATE TABLESPACE test
DATAFILE '/u01/app/oracle/oradata/cndba/pdbcndba/test01.dbf'
SIZE 1M AUTOEXTEND ON NEXT 1M;
SQL> conn test/test@pdbcndba
Connected.
SQL> CREATE TABLE t1
(id NUMBER,
description VARCHAR2(50),
created_date DATE,
CONSTRAINT t1_pk PRIMARY KEY (id)
) tablespace test;
Table created.
SQL> INSERT INTO t1
SELECT level,
'Description for ' || level,
CASE
WHEN MOD(level,2) = 0 THEN TO_DATE('01/07/2015', 'DD/MM/YYYY')
ELSE TO_DATE('01/07/2016', 'DD/MM/YYYY')
END
FROM dual
CONNECT BY level <= 1000;
1000 rows created.
SQL> COMMIT;
Commit complete.
SQL>
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'T1');
PL/SQL procedure successfully completed.
2.2 session 2 插入一条数据 不提交
[oracle@host1 ~]$ sqlplus "test/test@pdbcndba"
SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 21 22:38:04 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Mon Aug 21 2017 22:33:09 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> insert into t1 values(1001,'test',sysdate);
1 row created.
2.3 session 1 使用11g 语法move table 提示报错
SQL> alter table t1 move;
alter table t1 move
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
2.4 session 1 使用online move table
SQL> alter table t1 move online;
--一直等待session 2 提交
2.5 session 2 提交数据
SQL> commit;
Commit complete.
2.6 session 1 move table 成功
SQL> alter table t1 move online;
Table altered.
2.7 查看索引状态有效，未受到表迁移的影像。
SQL> select index_name from user_Indexes where table_name='T1';
INDEX_NAME
--------------------------------------------------------------------------------------------------------------------------------
T1_PK
SQL> col index_name for a25
SQL> col table_name for a20
SQL> col COMPRESSION for a20
SQL> set lines 400
SQL> select table_name,index_name,status,COMPRESSION,blevel,leaf_blocks from user_Indexes where index_name ='T1_PK';
TABLE_NAME INDEX_NAME STATUSCOMPRESSION BLEVEL LEAF_BLOCKS
-------------------- ------------------------- -------- -------------------- ---------- -----------
T1 T1_PK VALIDDISABLED 1 2
-- Basic move.
ALTER TABLE t1 MOVE ONLINE TABLESPACE users;
-- Change table compression.
ALTER TABLE t1 MOVE ONLINE TABLESPACE users COMPRESS UPDATE INDEXES;
ALTER TABLE t1 MOVE ONLINE TABLESPACE users NOCOMPRESS UPDATE INDEXES;
-- Change storage parameters.
ALTER TABLE t1 MOVE ONLINE STORAGE (PCTINCREASE 0);
https://www.cndba.cn/leo1990/article/2159https://www.cndba.cn/leo1990/article/2159
https://www.cndba.cn/leo1990/article/2159
版权声明：本文为博主原创文章，未经博主允许不得转载。