online-move-table-12cr

online-move-table-12cr
[41]Home » [42]Articles » [43]12c » Here

Online Move of a Table in Oracle Database 12c Release 2 (12.2)

Oracle Database 12c Release 1 (12.1) introduced the ability to move
table partitions and sub-partitions online. In Oracle Database 12c
Release 2 (12.2) you can now perform an online move of a table, as well
as individual partitions and sub-partitions.
* [44]MOVE ONLINE
* [45]Restrictions

Related articles.
* [46]Online Move of Partitions and Sub-Partitions in Oracle 12c
Release 1 (12.1)

MOVE ONLINE

The following example shows how to online move a table.
-- Create table.
DROP TABLE t1 PURGE;

CREATE TABLE t1
(id NUMBER,
description VARCHAR2(50),
created_date DATE,
CONSTRAINT t1_pk PRIMARY KEY (id)
);


-- Populate it.
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;

EXEC DBMS_STATS.gather_table_stats(USER, 't1');

We can now move the table using the ONLINE keyword.
-- 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);

Restrictions

There are some restrictions associated with online moves of tables
described [47]here.
* It can't be combined with any other clause.
* It can't be used on a partitioned index-organized table or
index-organized tables that have a column defined as a LOB, VARRAY,
Oracle-supplied type, or user-defined object type.
* It can't be used if there is a domain index on the table.
* Parallel DML and direct path inserts are not supported against an
object with an ongoing online move.

For more information see:
* [48]ALTER TABLE
* [49]move_table_clause
* [50]Online Move of Partitions and Sub-Partitions in Oracle 12c
Source .... : https://oracle-base.com/articles/12c/online-move-table-12cr2
--------------------------