8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 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.

Related articles.

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 here.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.