concept
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. Non partitioned tables can be moved as online operations without blocking any concurrent DML operations. A table move operation now also supports automatic index maintenance as part of the move. Data maintenance on nonpartitioned tables does not require any maintenance window since it does not impact any DML or query operation. Data maintenance on non partitioned tables does not require any maintenance windows because it does not affect any DML or query operations. 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. When you use this statement to use online keywords, data manipulation language (DML) operations can continue to run continuously on the moved tables. If the online keyword is not included, it is impossible for the data in the table to be concurrently operated by DML during the move operation. limit Here are some restrictions on the online movement of tables. It cannot be combined with any other clause. It cannot be used for partitioned index organized tables or indexed tables, where one column is defined as LOB, VARRAY, oracle provided type, or user-defined object type. If there is a domain index on the table, it cannot be used. Parallel DML and direct path insertion do not support online active objects.
Experiment
Sometimes it is necessary for the purpose of tablespace transfer or defragmentation MOVE TABLE， But the operation is in 12.2 Before, if a business often failed due to exclusive lock during operation, there was no time point to cut in. or move After success, the index will be invalid and affect the business. It can't be like the index rebuild online, At 12 C Several important online Operation, in fact move table online That's 12. C R2 An important feature of version introduction.
1 Oracle 11g move table Index will fail
[oracle@localhost ~]$ sqlplus "/as sysdba"
1.1 Create table and insert data
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 District report error
SQL> ALTER TABLE t1 MOVE ONLINE;
ALTER TABLE t1 MOVE ONLINE
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
1.3 move Non partitioned table, without online parameter
SQL> ALTER TABLE t1 MOVE;
Table altered.
1.4 See move table Index failure after
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 Create table and insert data
[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 Insert a data not submitted
[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 Use 11 g grammar move table Prompt for error
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 Use online move table
SQL> alter table t1 move online;
--Have been waiting for session 2 Submission
2.5 session 2 Submit data
SQL> commit;
Commit complete.
2.6 session 1 move table Success
SQL> alter table t1 move online;
Table altered.
2.7 View images with index status valid and not migrated by tables.
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 VALID DISABLED 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);Reference document