Alter and Drop an Index-Organized Table in Oracle

An index-organized table (IOT) supports many familiar ALTER TABLE and DROP TABLE operations. The important difference is that an IOT's primary key is also its physical storage structure. Oracle stores the rows in a primary-key B-tree rather than in a separate heap segment, so a change to that primary key is more fundamental than an ordinary column or metadata change.

Before changing an IOT, distinguish among three kinds of work: a direct alteration such as adding a non-key column, an IOT-specific storage change such as adding an overflow segment, and a structural redesign that requires rebuilding or replacing the primary-key B-tree. Dropping the table is a separate destructive operation with consequences for data, indexes, triggers, grants, constraints, and dependent objects.

Oracle data definition language (DDL) implicitly commits. A production change should therefore have an approved recovery or rollback plan, appropriate privileges and tablespace capacity, an understood locking and availability impact, and a tested verification procedure.

Begin by confirming the table organization and locating any related overflow object. Unquoted Oracle identifiers are stored in uppercase in data dictionary views.

SELECT table_name,
       iot_type,
       iot_name,
       tablespace_name
FROM   user_tables
WHERE  table_name = 'COIN_INVENTORY_IOT'
   OR  iot_name = 'COIN_INVENTORY_IOT'
ORDER  BY iot_type, table_name;

Inspect the constraints and indexes before deciding which operation is appropriate:

SELECT constraint_name,
       constraint_type,
       status,
       index_name
FROM   user_constraints
WHERE  table_name = 'COIN_INVENTORY_IOT'
ORDER  BY constraint_name;

SELECT index_name,
       index_type,
       uniqueness,
       status
FROM   user_indexes
WHERE  table_name = 'COIN_INVENTORY_IOT'
ORDER  BY index_name;

Also inventory dependent views, materialized views, PL/SQL units, triggers, synonyms, application grants, secondary indexes, and foreign keys in child tables. If the IOT is partitioned or has an overflow segment, include those physical components in the change plan. A schema owner can use the USER_* views shown here; a DBA examining several schemas should use the corresponding DBA_* views and include owner columns in joins.

Oracle permits adding a column to an existing IOT. The legacy rule that an IOT cannot receive new columns is incorrect. The following statement adds an optional appraisal-notes column:

ALTER TABLE coin_inventory_iot
ADD (appraisal_notes VARCHAR2(500));

For an IOT, the column addition must be the only alteration clause in that ALTER TABLE statement. Run another statement for any separate storage or constraint change.

Existing rows receive NULL unless the new column has a default. Adding a NOT NULL column to a populated table requires an appropriate default under the applicable Oracle rules. A defaulted column addition can update the existing IOT rows because the metadata-only optimization available to some heap-organized tables does not apply to an IOT. Test the operation with representative data and account for undo, redo, locks, and elapsed time.

Oracle also estimates the largest possible row from the declared maximum column sizes. If the expanded definition can require overflow storage but the IOT has no overflow segment, Oracle rejects the ALTER TABLE statement. This validation prevents a later insert from failing merely because the required overflow segment is absent.

A non-primary-key column can also be dropped, subject to normal dependency and data-type restrictions:

ALTER TABLE coin_inventory_iot
DROP COLUMN appraisal_notes;

This operation permanently removes the column's data and can invalidate application SQL or dependent objects. Verify dependencies and recovery requirements before executing it.

The primary-key boundary is different. Oracle does not permit dropping an IOT's primary-key constraint, and a primary-key column cannot be dropped even with CASCADE CONSTRAINTS. The primary key defines the B-tree in which the table rows are stored, so changing the set or order of its columns is a physical redesign rather than a routine constraint alteration.

To redesign the primary key, create a replacement IOT with the desired definition and migrate the data with explicit column lists, or evaluate DBMS_REDEFINITION when the object, change, release, privileges, and operational requirements make online redefinition appropriate. A replacement-table workflow must also recreate and validate constraints, indexes, triggers, grants, statistics, and dependent objects before a controlled cutover. Avoid SELECT * during migration because the two definitions may differ in column order, type, or count.

An existing IOT can receive an overflow segment with an IOT-specific ALTER TABLE clause:

ALTER TABLE coin_inventory_iot
ADD OVERFLOW;

The overflow segment stores the trailing non-key portion of rows that Oracle splits according to the IOT's row-storage rules. The primary-key columns always remain in the primary-key index portion.

To place the new segment in a specific tablespace, include an applicable segment attribute:

ALTER TABLE coin_inventory_iot
ADD OVERFLOW TABLESPACE iot_overflow;

The name iot_overflow is only an example. That tablespace must already exist, be online and writable, and be available to the table owner through a quota or suitable privilege. For a partitioned IOT, Oracle creates corresponding overflow segments according to the partition-level rules.

ADD OVERFLOW provides the separate segment; it is not by itself a complete row-redistribution strategy. As explained in the preceding lesson, PCTTHRESHOLD limits the index portion and INCLUDING expresses a preferred column boundary. Moving existing data or changing those rules is a structural reorganization addressed in the next lesson.

When an overflow segment already exists, place the OVERFLOW keyword after the table name to direct applicable physical attributes to that segment. For example:

ALTER TABLE coin_inventory_iot
OVERFLOW PCTFREE 20;

This changes PCTFREE for the overflow segment rather than the IOT's primary-key index segment. Select a value from measured update behavior; do not copy one from a generic tuning checklist.

Several settings in the legacy lesson no longer belong in a modern IOT tutorial. PCTUSED is not valid for the index segment of an IOT and is ignored where automatic segment-space management otherwise applies. MAXTRANS is deprecated, and Oracle ignores attempts to change it. In locally managed tablespaces, Oracle automatically manages concerns previously addressed through routine manipulation of NEXT, MAXEXTENTS, free lists, and related legacy parameters.

INITRANS and PCTFREE remain available in applicable contexts, but they should be changed only when workload evidence shows concurrent block-update pressure or a specific free-space requirement. Oracle generally recommends retaining default INITRANS behavior unless measurement supports a different value.

The primary-key B-tree of an IOT is not an ordinary index that happens to point to a separate table. It is the table's storage structure. Consequently, ALTER INDEX ... REBUILD is not the correct way to rebuild the IOT itself.

An IOT move rebuilds its primary-key index segment:

ALTER TABLE coin_inventory_iot MOVE;

This is a structural operation, not a routine step after every column change. The overflow segment is not automatically rebuilt unless the move explicitly addresses OVERFLOW, changes PCTTHRESHOLD or the INCLUDING boundary, or explicitly moves applicable out-of-line columns. A partitioned IOT must be handled through partition-level operations rather than moving the entire partitioned table with one table-level command. Online-move support also has restrictions for partitioned IOTs and IOTs containing LOBs, varrays, object types, or certain domain-index configurations.

Secondary indexes are separate schema objects. Check their status after operations that can affect them, and rebuild only an index that is unusable or that has an approved storage or maintenance reason:

ALTER INDEX coin_inventory_status_ix REBUILD;

This example rebuilds the named secondary index; it does not rebuild the IOT's primary-key storage. Detailed IOT movement, coalescing, shrinking, and reorganization belong in the next lesson.

Use the ordinary DROP TABLE statement when the intention is to remove the IOT itself:

DROP TABLE coin_inventory_iot;

Dropping an IOT removes all its rows, table-owned indexes and triggers, overflow and mapping components where present, and other associated storage. Object privileges granted on the table are lost. Dependent views, materialized views, and stored PL/SQL objects can become invalid rather than being automatically rewritten for a replacement table.

Because this is DDL, it implicitly commits. It is not equivalent to DELETE, and it should not be used when the intention is only to remove the rows while retaining the table definition and dependent-object relationships.

Referencing foreign keys

If child-table foreign keys reference the IOT's primary or unique key, Oracle rejects the ordinary drop. CASCADE CONSTRAINTS tells Oracle to remove those referencing constraints:

DROP TABLE coin_inventory_iot CASCADE CONSTRAINTS;

This is a broader destructive action. Inventory the child tables and obtain approval to remove their constraints before using it. The child tables remain, but the affected referential-integrity constraints do not.

Recycle bin and PURGE

Without PURGE, an eligible dropped table can enter the recycle bin when that feature is enabled. Flashback Drop may then provide a recovery path, subject to configuration, available space, and naming conditions. Recycle-bin recovery does not replace a tested backup, export, or migration plan.

The following statement bypasses the recycle bin:

DROP TABLE coin_inventory_iot PURGE;

Use PURGE only when immediate permanent removal is intentional. A purged table cannot be recovered with Flashback Drop. Do not combine CASCADE CONSTRAINTS and PURGE casually: together they remove referring constraints and recycle-bin protection.

After an alter operation, verify the requested change rather than relying only on a success message. Confirm the column definition:

SELECT column_id,
       column_name,
       data_type,
       data_length,
       nullable
FROM   user_tab_columns
WHERE  table_name = 'COIN_INVENTORY_IOT'
ORDER  BY column_id;

Repeat the earlier USER_TABLES, USER_CONSTRAINTS, and USER_INDEXES checks to verify the IOT type, overflow metadata, constraint status, and index status. Test critical primary-key and secondary-index queries, trigger and constraint behavior, dependent-object validity, and the grants required by application accounts.

Manual statistics gathering is not automatically necessary after every simple column change. After substantial data movement, a replacement-table migration, or a move operation, confirm whether the existing automatic statistics strategy has collected suitable statistics. If manual collection is warranted, a schema owner can use:

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

Document the executed DDL, verification results, application effects, and rollback status. In the next lesson, you will learn how to reorganize an index-organized table.