Report and Remove Unused Columns in Oracle Database 23ai

Database requirements change over time. A column that once supported an application feature, report, or integration may no longer be needed after a redesign. Oracle Database 23ai provides two principal approaches for removing such a column: mark it unused first and physically remove it later, or drop it immediately.
The correct choice depends on the size of the table, the maintenance window, application dependencies, constraints, and the urgency of reclaiming storage. For a large production table, SET UNUSED can remove a column from application use quickly while postponing the more expensive row rewrite. For a smaller table or a planned maintenance period, DROP COLUMN may be the simpler operation.

The three Oracle operations serve different purposes:

1. SET UNUSED removes one or more columns logically without immediately reclaiming their storage.
2. DROP UNUSED COLUMNS physically removes every column previously marked unused.
3. DROP COLUMN immediately removes a specified column and its stored data.

These are separate operations. Do not combine DROP COLUMN and DROP UNUSED COLUMNS into one statement.

The following documentation-style syntax shows how Oracle marks one or more columns unused:

ALTER TABLE table_name
    SET UNUSED
    {
        COLUMN column_name
        | (column_name [, column_name]...)
    }
    [CASCADE CONSTRAINTS | INVALIDATE]
    [ONLINE];

The braces, brackets, ellipsis, and vertical bar are documentation notation. They are not entered literally in executable SQL.
To mark two columns in STORM_TRACK as unused:

ALTER TABLE storm_track
    SET UNUSED (lat, lon);

After this operation, LAT and LON no longer appear in normal column listings, DESCRIBE output, or SELECT * results. Applications cannot reference the old column names, and those names can be reused for new columns.
The underlying column data still occupies storage until Oracle physically removes the unused columns. The operation is not reversible through ordinary DDL, so the administrator should preserve any data that may still be needed before executing the statement.

For an eligible table, Oracle can mark a column unused with the ONLINE clause:

ALTER TABLE shipping_action
    SET UNUSED COLUMN log_time
    ONLINE;

The online option can reduce application disruption, but it does not make every table alteration lock-free. Specialized structures such as temporary tables, clustered tables, external tables, and index-organized tables may have restrictions. Test the exact operation against the table definition before using it in production.

Oracle provides data dictionary views that report tables containing columns marked unused:

• USER_UNUSED_COL_TABS reports unused-column counts for tables in the current schema.
• ALL_UNUSED_COL_TABS reports accessible tables across schemas.
• DBA_UNUSED_COL_TABS reports unused columns throughout the database and requires the appropriate privileges.

The correct term is unused, not unusable. Oracle commonly uses unusable for objects such as indexes.
Report unused columns in the current schema:

SELECT table_name,
       count
FROM user_unused_col_tabs
ORDER BY table_name;

Report accessible tables across schemas:

SELECT owner,
       table_name,
       count
FROM all_unused_col_tabs
ORDER BY owner,
         table_name;

A DBA-level report is:

SELECT owner,
       table_name,
       count
FROM dba_unused_col_tabs
ORDER BY owner,
         table_name;

These views identify the affected tables and the number of unused columns. They do not restore the original column names because Oracle removes those names from normal use when the columns are marked unused.

When the maintenance window permits, physically remove all unused columns:

ALTER TABLE storm_track
    DROP UNUSED COLUMNS;

This operation rewrites stored rows as needed and reclaims the space occupied by the unused column values. On a large table, the physical removal may consume substantial undo and take significant time.
Oracle supports checkpoints during the operation:

ALTER TABLE storm_track
    DROP UNUSED COLUMNS
    CHECKPOINT 250;

The checkpoint value specifies how frequently Oracle establishes progress during the physical removal. This can limit accumulated undo during a long operation, but it does not eliminate the need for capacity planning, testing, and a suitable maintenance period.

To remove one named column and its data immediately:

ALTER TABLE shipping_action
    DROP COLUMN log_time;

To remove multiple columns:

ALTER TABLE shipping_action
    DROP (log_time, audit_note);

An immediate drop is appropriate when the table is small enough, the operation's impact is acceptable, and there is no reason to defer the physical row rewrite.

Oracle prevents a column from being dropped or marked unused when dependent constraints make the operation invalid. Primary-key, unique-key, foreign-key, and check constraints must be reviewed before the change.
A column that participates in a primary key is not permanently protected from removal, but the dependent constraint must first be removed or handled through supported cascading syntax.
For example:

ALTER TABLE shipping_action
    DROP COLUMN log_time
    CASCADE CONSTRAINTS;

Use CASCADE CONSTRAINTS only after identifying every affected constraint. A cascade can remove more metadata than expected, including relationships relied on by other tables.

Views, indexes, triggers, materialized views, virtual columns, PL/SQL units, and application SQL may reference the column being removed. Oracle dependency tracking can identify many schema-level dependencies, but source code outside the database must also be searched.
Inspect direct dependencies before the change:

SELECT name,
       type,
       referenced_name,
       referenced_type
FROM user_dependencies
WHERE referenced_name IN
(
    'STORM_TRACK',
    'SHIPPING_ACTION'
)
ORDER BY referenced_name,
         name;

After the change, identify invalid schema objects:

SELECT object_name,
       object_type,
       status
FROM user_objects
WHERE status = 'INVALID'
ORDER BY object_type,
         object_name;

The INVALIDATE keyword should not be treated as a replacement for dependency analysis. Removing a referenced column can invalidate dependent objects, and those objects must be reviewed, recompiled, corrected, or removed according to the application design.

Query USER_TAB_COLUMNS before and after the operation:

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

A column marked unused no longer appears as a normal named column in this view.
In SQL*Plus or SQLcl, the client command:

DESC storm_track

also shows the visible table structure. DESC is a client command rather than a SQL statement executed by the database SQL engine.

Before changing a production table:

1. Confirm that application code, views, reports, ETL jobs, triggers, procedures, indexes, constraints, and materialized views no longer require the column.
2. Archive or export any data that must be retained.
3. Test the operation on a representative copy of the table.
4. Estimate the required maintenance window, undo, redo, storage, and recovery impact.
5. Choose between immediate DROP COLUMN and deferred SET UNUSED.
6. Verify dependent objects and application behavior after the operation.
7. Schedule DROP UNUSED COLUMNS later if the logical removal was performed first.

Marking a column unused is effectively irreversible through normal DDL. Restoring the data requires a backup, export, retained copy, or another authoritative source.

General examples apply most directly to ordinary heap-organized tables. Specialized objects may have additional restrictions:

• Object-relational tables distinguish between dropping a table column and evolving an attribute of the underlying object type.
• Nested-table and collection columns have separate storage objects and dependencies.
• Partitioned, clustered, temporary, external, index-organized, immutable, and blockchain tables may impose feature-specific restrictions.
• Oracle-maintained SYS objects should not be altered manually.
• A read-only table must be returned to read/write mode before an authorized structural change can occur.

Review the exact table definition and the Oracle Database 23ai SQL Language Reference before applying the general syntax to specialized structures.

First, inspect the existing columns:

SELECT column_id,
       column_name,
       data_type
FROM user_tab_columns
WHERE table_name = 'STORM_TRACK'
ORDER BY column_id;

Mark the columns unused:

ALTER TABLE storm_track
    SET UNUSED (lat, lon);

Confirm that the table contains unused columns:

SELECT table_name,
       count
FROM user_unused_col_tabs
WHERE table_name = 'STORM_TRACK';

Verify that the columns no longer appear in the visible definition:

SELECT column_id,
       column_name,
       data_type
FROM user_tab_columns
WHERE table_name = 'STORM_TRACK'
ORDER BY column_id;

During a later maintenance window, remove the unused columns physically:

ALTER TABLE storm_track
    DROP UNUSED COLUMNS
    CHECKPOINT 250;

Finally, verify that no unused-column entry remains:

SELECT table_name,
       count
FROM user_unused_col_tabs
WHERE table_name = 'STORM_TRACK';

Use the exercise to practice reporting, marking, and removing columns.
Report Remove Unused Column - Exercise
The next lesson examines Oracle Database limits.