DBAtlas · Database operations atlas

Move a table or partition online (ALTER TABLE MOVE ONLINE)

OracleOnline Operations

Command

-- Move the table to another tablespace / reorganize it (DML continues):
ALTER TABLE customer_app.orders MOVE ONLINE TABLESPACE customer_data;

-- Compress while moving (BASIC; for OLTP compression use ROW STORE COMPRESS ADVANCED):
ALTER TABLE customer_app.orders MOVE ONLINE COMPRESS;

-- A single partition:
ALTER TABLE customer_app.sale_part
  MOVE PARTITION p2026_h1 ONLINE TABLESPACE customer_archive UPDATE INDEXES;

-- Verify: indexes must stay VALID
SELECT index_name, status FROM dba_indexes
WHERE  owner = 'CUSTOMER_APP' AND table_name = 'ORDERS';
SELECT table_name, tablespace_name, compression, compress_for
FROM   dba_tables
WHERE  owner = 'CUSTOMER_APP' AND table_name = 'ORDERS';

What it does

Rewrites the table segment: reduces fragmentation and the high water mark, changes tablespace, applies compression. With ONLINE, DML is not blocked during the operation and indexes are maintained automatically and stay VALID.

When to use it

To reclaim space after mass deletes, to move a table to a new tablespace/storage, and to compress old partitions into an archive tablespace.

Caution

Requires Enterprise Edition; ROW STORE COMPRESS ADVANCED requires the Advanced Compression option (extra cost). The operation needs additional space equal to the table size and generates heavy redo. Short locks are taken at the start and end; long open transactions make it wait. A MOVE without ONLINE or UPDATE INDEXES leaves all indexes UNUSABLE.

Open in DBAtlas →

Related