Relocate or Restructure an Oracle Database Table

Oracle Database 23ai provides several ways to relocate, reorganize, or structurally redefine a table without dropping the original table and manually recreating all of its dependent objects. For a straightforward physical relocation, the principal command is ALTER TABLE ... MOVE. This operation creates a new segment for the table, copies the rows into that segment, and then replaces the old segment with the newly organized version. Depending on the clauses used, the move can place the table in another tablespace, change physical attributes such as PCTFREE, or apply compression and other supported storage characteristics.
A table move is a physical maintenance operation. It does not normally change the table's logical definition, column names, constraints, or application interface. Nevertheless, moving a table is not a risk-free performance shortcut. A database administrator should first determine why the table needs to be moved, estimate the required storage and maintenance window, review dependent indexes and LOB segments, and select either an offline move, an online move, or a more comprehensive online redefinition.

A table may need to be relocated or reorganized for several administrative reasons:

1. Tablespace placement: The table may belong in a tablespace with more suitable capacity, storage characteristics, encryption, backup policy, lifecycle rules, or performance objectives.
2. Space reorganization: A segment may contain unused space after extensive deletes or changes in the table's workload. Moving the table creates a new segment and can produce a more compact physical layout.
3. Physical-attribute changes: The administrator may need to change attributes such as PCTFREE, compression, or the target tablespace.
4. Storage migration: A table may need to be moved as part of hardware replacement, cloud migration, consolidation, or a broader tablespace-maintenance project.
5. Row-growth behavior: A table with rows that frequently expand after insertion may benefit from a more appropriate PCTFREE setting. A move can apply the revised setting to the new segment.

A move should follow diagnosis rather than guesswork. Segment size, row migration, chaining, I/O behavior, SQL execution plans, index design, and workload patterns should be examined before concluding that table movement is the correct remedy.

The following documentation-style syntax summarizes common options for a heap-organized table:

ALTER TABLE table_name
    MOVE
    [TABLESPACE tablespace_name]
    [PCTFREE integer]
    [UPDATE INDEXES];

The square brackets indicate optional clauses. They are not entered as part of an executable SQL statement.
When Oracle executes the move, it allocates a new segment and copies the table rows into it. Because the rows are written into new blocks, their physical row addresses can change. This behavior is important because conventional B-tree indexes store rowids that identify the corresponding table rows.

The following statement moves PRODUCT_OBJ_TABLE into the USER_NEXT tablespace:

ALTER TABLE product_obj_table
    MOVE TABLESPACE user_next
    UPDATE INDEXES;

The destination tablespace must already exist, and the table owner must have sufficient quota or the required administrative privileges. The destination tablespace does not have to be the same total size as the source tablespace. It only needs enough usable capacity for the new table segment, any explicitly moved dependent segments, and the temporary overhead associated with the operation.
The UPDATE INDEXES clause requests index maintenance as part of the move where the operation and index types support it. This is preferable to teaching an unconditional move-and-rebuild sequence because current Oracle releases can maintain affected indexes during many table-maintenance operations.

The PCTFREE attribute specifies the percentage of space Oracle reserves in each data block for future updates to existing rows. For example, the following operation reorganizes CUSTOMER_OBJ_TABLE and reserves 15 percent of each block for row expansion:

ALTER TABLE customer_obj_table
    MOVE PCTFREE 15
    UPDATE INDEXES;

A higher PCTFREE value can reduce row migration when updates cause rows to grow, but it also reduces the amount of block space initially available for inserted rows. A lower value increases row density but leaves less room for expansion. The appropriate value depends on the table's update pattern, average row size, block size, and storage objectives.
The legacy PCTUSED parameter should not be emphasized in an Oracle Database 23ai lesson. It is associated with manual segment-space management. Oracle ignores PCTUSED for objects stored in tablespaces that use Automatic Segment Space Management, which is the normal configuration for modern Oracle databases.

A conventional table move requires stronger locking and is normally scheduled during a maintenance window. It is appropriate when the table can be temporarily unavailable for normal application activity and when the operation's restrictions are understood.
For a supported heap-organized table that must remain available for most application activity, Oracle also provides an online move:

ALTER TABLE product_obj_table
    MOVE ONLINE
    TABLESPACE user_next;

An online move allows concurrent DML during most of the operation and maintains indexes as part of the move. However, ONLINE does not mean that the operation is universally lock-free. Oracle still requires brief locking at certain phases, and restrictions can apply to particular table organizations, object types, domain indexes, bitmap join indexes, and other specialized structures. Review the table definition and test the exact statement before using it in production.

A table move does not discard the table's constraint definitions. Primary-key, unique, foreign-key, and check constraints remain defined. Index definitions also remain in the data dictionary.
The important operational issue is index usability. During a traditional offline move, the table rows receive new rowids. If Oracle does not maintain the affected indexes during the operation, those indexes can become unusable and must be rebuilt before applications rely on them. Using UPDATE INDEXES, when supported, reduces the need for a separate rebuild step.
After the move, verify index status:

SELECT index_name,
       table_name,
       status
FROM user_indexes
WHERE table_name = 'PRODUCT_OBJ_TABLE'
ORDER BY index_name;

If an index is reported as UNUSABLE, rebuild it:

ALTER INDEX index_name REBUILD;

Do not drop and recreate an index unless there is a specific reason to change its definition. Rebuilding normally preserves the existing index definition and is less disruptive than manually reconstructing it.

A base-table move does not automatically imply that every LOB segment will be placed in the desired target tablespace. LOB data and its supporting LOB index can have separate storage. When a LOB column must also be relocated, specify its storage explicitly.

ALTER TABLE document_store
    MOVE TABLESPACE data_ts
    LOB (document_content)
    STORE AS (TABLESPACE lob_ts)
    UPDATE INDEXES;

This example moves the base table into DATA_TS while placing the LOB segment for DOCUMENT_CONTENT in LOB_TS. Before running the statement, inspect the table for CLOB, BLOB, NCLOB, VARRAY, nested-table, and object-type storage that may require separate handling.

The word fragmentation is often used broadly, but it should be defined carefully. A table may contain reusable free space, partially filled blocks, or a high-water mark that reflects an earlier and larger volume of data. A move creates a new segment and can compact the table into a different physical layout. It can also apply a new tablespace, block-space policy, or compression setting.
However, a move should not be performed simply because a table has free space. Oracle is designed to reuse free space, and unused blocks do not automatically indicate poor performance. The administrator should consider the expected future growth, full-table-scan workload, backup size, storage cost, and maintenance impact before deciding to reorganize the segment.
A representative offline reorganization is:

ALTER TABLE sales
    MOVE TABLESPACE sales_front
    UPDATE INDEXES;

The name SALES_FRONT is only an example. The destination should be selected according to the site's tablespace and storage-management policy. There is no requirement to create a second tablespace with exactly the same total size as the source.

After the operation, verify the table's location and physical attributes:

SELECT table_name,
       tablespace_name,
       pct_free
FROM user_tables
WHERE table_name IN (
    'PRODUCT_OBJ_TABLE',
    'CUSTOMER_OBJ_TABLE'
)
ORDER BY table_name;

To determine whether a tablespace uses automatic or manual segment-space management, query:

SELECT tablespace_name,
       segment_space_management
FROM user_tablespaces
WHERE tablespace_name = 'USER_NEXT';

Also verify invalid objects, index status, application access, constraints, grants, triggers, materialized-view dependencies, and available free space. For a production change, compare performance and storage measurements before and after the move rather than assuming that the operation improved the workload.

A physical reorganization can change the table's segment characteristics. Review the table's optimizer statistics after the operation and gather them when required by the database version, the operation performed, or the site's maintenance policy.

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname => USER,
        tabname => 'PRODUCT_OBJ_TABLE',
        cascade => TRUE
    );
END;
/

The cascade argument requests related index statistics. In a managed production environment, coordinate this step with the established statistics schedule and avoid replacing useful production statistics without first understanding the effect on execution plans.

ALTER TABLE ... MOVE is well suited to a physical relocation or a supported change to segment attributes. More extensive transformations may require online table redefinition through DBMS_REDEFINITION.
Online redefinition is appropriate when the administrator must perform a more complex structural change while keeping the source table available for application use. Depending on the design and supported features, it can be used to change storage characteristics, reorganize a large table, modify selected structural properties, or convert a table to a different partitioning strategy.
A redefinition project requires more preparation than a simple move. The administrator must validate whether the table can be redefined, create or describe the target structure, copy dependent objects, synchronize changes, complete the redefinition, and verify the result. For a small table with an acceptable maintenance window, a conventional move may remain the simpler and safer choice.

Oracle provides several table-reorganization methods:

1. ALTER TABLE ... MOVE for a direct physical relocation or reorganization.
2. ALTER TABLE ... MOVE ONLINE when the table is supported and concurrent DML must continue during most of the operation.
3. DBMS_REDEFINITION for more complex online structural transformations.
4. CREATE TABLE AS SELECT when a deliberately new table is being constructed and dependent objects will be managed separately.
5. Oracle Data Pump when the maintenance activity is part of a broader migration, export, import, or platform transition.

The correct method depends on table size, table organization, dependent objects, availability requirements, recovery objectives, available storage, and the exact structural change being performed.

Use the exercise to test your ability to select and execute an appropriate table move.
Relocate Table - Exercise
The next lesson introduces another Oracle table feature: creating and using a temporary table.