Online Table Move

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

Why Use Online Table Move in Oracle?

Imagine 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);

2. 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);

3. 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';

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

How about using it against a table with more data?

To keep the following video somewhat short, I've set up a demo that moves a half a gig table while compressing it to a new tablespace. While the move is happening there is about 45,000 transactions per second across 50 active sessions hitting the table. The code to set up the following demo is available on my Github here

Video Explanation.

In our demo video, we're looking at two database sessions side by side:

• On the left: The source table (CUSTOMERS) sitting at 646.7MB, currently uncompressed

• On the right: The same table in the SOE schema, where we'll track the action

  
  

Here's what happens:

1. Start the Load We start a workload with about 45,000 transactions per second spread across 50 active sessions. The monitoring session shows us real-time stats like transactions per minute, any errors that pop up, and the mix of DML operations hitting the table.

2. Initiate the Move With the table under load, the session on the right starts the move operation. We are moving the table to a new tablespace and enabling compression at the same time.

3. Outcome After about 90 seconds, the customers table has been moved and compressed. The left session confirms our CUSTOMERS table is now compressed, while the right session shows it's in the the SOE_MOVE_DEMO schema.

4. Verify Everything's Working A quick check shows all our customer indexes are still valid as well meaning no manual rebuilding needed. The table move handled everything for us, all while keeping our heavy transaction load running smoothly.

   
   

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 shows the basic usage and shows how to move tables with compression and maintain indexes in the process.

-Killian