top of page
2 days ago3 min read

Online Table Move

Online Table Move lets you to move a table, its partitions, and sub-partitions without downtime. This means that you don't need to take an outage to support the reorganization of tables. A table move can happen while transactions are running against it. Online table move also has the ability to filter and compress data as part of a move.


Why Use Online Table Move in Oracle?


You’ve got a big, active table, and you need to make some changes. Maybe you want to move it to another tablespace, enable compression, or reorganize the structure for better performance. Normally, this means downtime... But what if you could do it all while your application stays online?


Here’s where this feature helps:


  1. No Downtime

    Online Table Move lets you reorganize a table *without* locking it up meaning your queries can keep running and your inserts, updates, and deletes still work while you’re moving it.


  2. Migrate Tablespaces

    Maybe you need to move your table to a new tablespace for storage management or just tidying up


  3. Compression

    You can use Online Table Move to enable compression while migrating the table


  4. Performance Boost

    Tables get messy. Fragmentation happens. Reorganizing with an online move can clean things up, making your queries faster and your I/O more efficient.



Want to see it in action?


  1. Let's create a small table and insert some data.

CREATE TABLE STORE_LOCATIONS (
    store_id NUMBER,
    phone VARCHAR (25),
    state VARCHAR (10),
    zip_code VARCHAR (5)
);

INSERT INTO STORE_LOCATIONS VALUES (1, 202, 'New York', 10001),
								  (2, 203, 'New York', 10002),
								  (3, 209, 'New York', 10003),
								  (4, 200, 'New York', 10004);

  1. Here we can go ahead and add a constraint and create an index on the locations by state

ALTER TABLE STORE_LOCATIONS ADD CONSTRAINT location_pk PRIMARY KEY (store_id);

CREATE index location_index ON STORE_LOCATIONS(state);

  1. To move our table and automatically maintain its indexes, we'll use the ONLINE keyword. This means we don't need to rebuild the indexes manually after our move

ALTER TABLE STORE_LOCATIONS MOVE ONLINE TABLESPACE test_tbs;

SELECT index_name, status FROM user_indexes where index_name = 'LOCATION_INDEX' OR index_name = 'LOCATION_PK';


  1. Now we can also move our table with compression enabled. 

ALTER TABLE STORE_LOCATIONS MOVE ONLINE TABLESPACE test_tbs_2 COMPRESS;

SELECT compression FROM user_tables WHERE table_name = 'STORE_LOCATIONS';


Warning About Statistics

One thing to keep in mind - moving a table invalidates its statistics. You'll want to collect fresh stats after the move to keep your query optimizer happy.


Partition and Subpartition Moves

You can also move individual partitions and subpartitions online. This is useful for large partitioned tables where you only need to reorganize a smaller number segments. The UPDATE INDEXES clause works here too, meaning both local and global indexes stay valid during the move.


Recap

Online Table Move allows moving Oracle tables, partitions, and subpartitions without any downtime. The feature keeps data available during the moves, meaning queries continue to run and DML operations (inserts, updates, deletes) remain uninterrupted.


Key capabilities:

  • Move tables or partitions between tablespaces while keeping them online

  • Enable compression during the move operation

  • Maintain index availability by using ONLINE with UPDATE INDEXES

  • Move partitions and subpartitions individually

  • Handle LOB columns if explicitly specified

  • Support automatic index maintenance for both local and global indexes


The code above highlights the basic usage and shows how to move tables with compression and maintain indexes in the process.


-Killian

Comments

bottom of page