Adding and Modifying Table Columns in Oracle 26ai

Distinguish ADD from MODIFY

Parts omitted from a MODIFY definition remain unchanged. For example, changing only a default or nullability does not require the datatype to be repeated. This allows each statement to express the intended alteration without restating the entire column definition.

Establish the case-study tables

The examples use an ordinary heap table named CUSTOMER_CARE. The related USER_LIST table supplies a parent key for the user who last changed a care record. Keeping the setup small makes the data and constraint preconditions visible:

CREATE TABLE user_list (
    username VARCHAR2(128)
        CONSTRAINT user_list_pk PRIMARY KEY
);

CREATE TABLE customer_care (
    care_id      NUMBER GENERATED BY DEFAULT AS IDENTITY,
    comment_text VARCHAR2(200),
    CONSTRAINT customer_care_pk PRIMARY KEY (care_id)
);

USER_LIST.USERNAME is a single-column primary key. An inline clause written as REFERENCES user_list can therefore target that primary key without listing its column, but REFERENCES user_list (username) communicates the relationship more clearly.

The graphic is a syntax overview, not a guarantee that every example can be applied to every populated table. Its new LAST_CHANGE_DATE column has a default, but CHANGE_USER is declared NOT NULL without one. If CUSTOMER_CARE already contains rows, Oracle has no value to assign to that mandatory column. The reference also assumes that USER_LIST has a compatible primary key. A production change must satisfy both conditions.

Add columns to a populated table

When a column is added without a default, its initial value for existing rows is null. That behavior is suitable for an optional attribute. A populated table cannot receive a new NOT NULL column unless the operation also supplies a usable default or the deployment is divided into stages.

ALTER TABLE customer_care
ADD (
    last_change_date DATE DEFAULT DATE '2026-01-10' NOT NULL,
    change_user      VARCHAR2(128)
        REFERENCES user_list (username)
);

The ANSI date literal DATE '2026-01-10' does not depend on NLS_DATE_FORMAT. It supplies a value for existing rows and for later inserts that omit LAST_CHANGE_DATE. CHANGE_USER remains nullable until the application or a migration step can assign valid user names. Every non-null value in that column must match USER_LIST.USERNAME because the inline foreign key is enabled.

For eligible tables and default expressions, Oracle can optimize an added default by storing it as metadata and rewriting queries so preexisting rows return the default. That optimization is not universal: object, LOB, encrypted, temporary, clustered, index-organized, queue, materialized view container, and certain policy-protected tables have additional conditions. Do not promise that every defaulted addition is instantaneous or metadata-only.

Adding a column can also affect clients even when the DDL is quick. Code that uses SELECT *, positional inserts, bulk loaders, replication mappings, object-relational mappings, or generated schemas can depend on a particular column list. Explicit column lists make the database interface more stable as the schema evolves. A stored view created with SELECT * does not automatically acquire a later base-table column; recreate the view deliberately if its public contract should change.

Modify an existing column

The following statement increases the declared length of COMMENT_TEXT. Oracle permits increasing the size of a character or raw column while data is present. Increasing numeric precision is also supported, subject to the datatype's rules.

ALTER TABLE customer_care
MODIFY (comment_text VARCHAR2(350));

A declaration such as VARCHAR2(350) uses the applicable length semantics. In a database or session using byte semantics, the value is a byte limit; with character semantics, it is a character limit. When the business requirement is explicitly 350 characters, declare VARCHAR2(350 CHAR) and verify that applications, indexes, and row-size assumptions remain valid.

Reducing a character length is not limited to an empty or all-null column, as older versions of this lesson stated. Oracle can accept the reduction when every existing value fits the new limit and the operation does not require data conversion. Oracle scans the stored data and rejects the change if any value is too large. Profile the maximum value length before issuing the DDL rather than using the failure as the test.

Arbitrary datatype conversion is more restrictive. In general, Oracle can change a column to another datatype when its rows contain only nulls, while documented widening operations and selected conversions are exceptions. Partitioning and subpartitioning key columns, domain indexes, LOBs, object columns, and specialized table types introduce further restrictions. A supported syntax does not imply that Oracle will convert any existing business data automatically.

Change a default without rewriting existing values

ALTER TABLE customer_care
MODIFY (last_change_date DEFAULT SYSDATE);

This statement changes only the default. The column remains a DATE and remains NOT NULL. A later insert that omits LAST_CHANGE_DATE receives the database's current date and time from SYSDATE. Existing values are not replaced merely because the default changed. To discontinue a default for future omitted values, use MODIFY (last_change_date DEFAULT NULL) when that definition is compatible with the column's other rules.

An ordinary default is used when the insert omits the column; it does not replace an explicitly supplied null. Oracle also supports DEFAULT ON NULL definitions, but that is a separate behavior and should be selected because the business rule requires it, not as a substitute for understanding ordinary defaults.

Strengthen or relax nullability

Before making CHANGE_USER mandatory, populate every missing value with a legitimate parent key. The example assumes that USER_LIST already contains the user name SYSTEM:

UPDATE customer_care
SET    change_user = 'SYSTEM'
WHERE  change_user IS NULL;

ALTER TABLE customer_care
MODIFY (change_user NOT NULL);

If SYSTEM is not present in USER_LIST, the update violates the foreign key. In a real migration, the chosen value must have a valid business meaning; a convenient placeholder can damage accountability. Validate the backfill before the DDL. Oracle then scans the existing rows when it enables the NOT NULL rule and rejects the alteration if any null remains.

The reverse operation uses MODIFY (change_user NULL) when permitted. This removes the mandatory-value requirement; it does not create a separate “null constraint.” Nullability and foreign-key enforcement remain distinct: a nullable foreign key allows an absent relationship, but every non-null child value must still match the referenced key.

Choose the operation that matches the change

Rename a column without rebuilding the table

ALTER TABLE customer_care
RENAME COLUMN comment_text TO care_comment;

Oracle supports column renaming directly, so dropping and recreating the table is not the normal solution. Function-based indexes and check constraints that depend on the renamed column can remain valid, but dependent views, triggers, functions, procedures, and packages can be invalidated. Application SQL, reports, APIs, ETL mappings, and generated code may still contain the old identifier and require coordinated deployment changes.

Drop a column or defer its physical removal

Oracle also supports direct removal. Assuming an independently created OBSOLETE_CODE column exists, its definition and stored data can be removed with this statement:

ALTER TABLE customer_care
DROP COLUMN obsolete_code;

Indexes on the target column are dropped, and constraints or dependent objects can be removed or invalidated according to their relationships. A referenced key or a multicolumn constraint can require CASCADE CONSTRAINTS, but do not add that clause reflexively. Inventory every constraint it would remove before authorizing the cascade.

For a large internal heap table, marking a column unused can make it inaccessible sooner while deferring the work of removing its data from each row. The following independent example assumes that LEGACY_NOTE exists:

ALTER TABLE customer_care
SET UNUSED (legacy_note);

ALTER TABLE customer_care
DROP UNUSED COLUMNS;

SET UNUSED is not a reversible hiding mechanism. The name and data become inaccessible, no SET USED counterpart restores them, and the space is not reclaimed until unused columns are dropped. Schedule the physical cleanup according to table size, workload, undo, redo, and maintenance-window requirements.

Treat column order as an interface concern

Ordinary ADD syntax places a new visible column after the current visible columns; it does not insert a column at an arbitrary physical position. That limitation rarely justifies reconstructing the table. Queries, reports, and APIs should name columns in the order required by their interface, and a view can publish a stable logical order without changing the base table.

Invisible columns are another feature, but they are not equivalent to unused columns. An invisible column remains stored and accessible when it is named explicitly, can be made visible again, and is omitted from wildcard projections. Changing visibility can also affect implicit-value inserts, so it should be deliberate rather than a cosmetic reordering trick:

ALTER TABLE customer_care
MODIFY (change_user INVISIBLE);

ALTER TABLE customer_care
MODIFY (change_user VISIBLE);

Recognize identity and special-table boundaries

An identity clause within ALTER TABLE ... MODIFY applies only to a column that is already an identity column. It can change supported generation properties or sequence-generator options; it cannot convert an ordinary number column into an identity column merely by appending GENERATED ALWAYS AS IDENTITY. The separate DROP IDENTITY operation removes identity generation without deleting the values already stored in the column.

Oracle AI Database 26ai adds column-evolution support for blockchain and immutable tables: user columns can be added or dropped while Oracle retains data needed for crypto-hash-chain continuity. That enhancement does not remove the special restrictions of those table types and should not be generalized to ordinary heap, cluster, external, temporary, object, or duplicated tables. Check the rules for the actual table type before deploying a structural change.

Verify column metadata

Do not treat a successful DDL message as the complete verification. Query the data dictionary to confirm the resulting names, sequence, datatype, length semantics, nullability, default, and identity status:

SELECT column_id,
       column_name,
       data_type,
       data_length,
       char_length,
       char_used,
       nullable,
       data_default,
       identity_column
FROM   user_tab_columns
WHERE  table_name = 'CUSTOMER_CARE'
ORDER  BY column_id;

DATA_LENGTH is measured in bytes. CHAR_LENGTH reports declared character length where applicable, while CHAR_USED distinguishes byte (B) from character (C) semantics. NULLABLE is N when a NOT NULL or primary-key rule applies. DATA_DEFAULT exposes the declared default expression, and IDENTITY_COLUMN identifies identity generation.

USER_TAB_COLUMNS filters system-generated hidden columns. Use USER_TAB_COLS when deeper inspection of hidden, invisible, or internal metadata is required. If a column was marked unused, the dedicated view reports the number of unused columns on the table:

SELECT table_name,
       count AS unused_column_count
FROM   user_unused_col_tabs
WHERE  table_name = 'CUSTOMER_CARE';

This view can confirm that unused columns remain pending physical removal, but it cannot restore their former names or data.

Know when direct ALTER TABLE is not enough

The legacy lesson treated table reconstruction as the normal answer for renaming, removing, or rearranging columns. Current Oracle syntax makes that advice unnecessarily destructive. Direct clauses handle the common cases while preserving the table as the same schema object. At the other extreme, however, not every requested transformation belongs in one MODIFY statement.

A replacement or redefinition workflow can be appropriate when an incompatible datatype must be converted while preserving non-null data, when several columns need data transformations, when physical organization must change, or when the availability requirement cannot be met by the direct clause. Such a workflow may create an interim definition, copy or transform rows, synchronize changes, recreate dependent objects, and exchange or rename objects during a controlled cutover. Oracle's online redefinition facilities can reduce interruption for eligible tables, but they introduce their own prerequisites, storage demand, verification work, and failure-recovery plan.

Choose that heavier method because the required transformation and service level justify it, not merely to change the order shown by DESC. Conversely, do not force an unsupported conversion through implicit casts or several untested DDL statements simply to avoid a migration. The safest design is the least disruptive supported operation that preserves the intended data, constraints, dependencies, and application contract.

Plan a production column change

1. Capture the current table definition and the definitions of dependent constraints, indexes, views, triggers, and stored programs.
2. Profile nulls, maximum lengths, distinct values, and rows that could violate the proposed datatype or constraint.
3. Test the exact DDL with representative data and dependencies in a nonproduction environment.
4. Estimate locking, scanning, row rewriting, undo, redo, storage, and maintenance-window requirements for the specific clause.
5. Coordinate application SQL, ETL, reports, replication, APIs, and deployment order with the database change.
6. Apply the DDL through the controlled schema-change process; do not assume that successful DDL can be rolled back like ordinary DML.
7. Verify dictionary metadata, recompile or test affected objects, and validate application behavior after deployment.
8. Confirm that backup and recovery procedures cover the change before removing data or making an unused column permanent.

Not every supported column operation is online or inexpensive. A change can scan or rewrite a large table, acquire locks, generate substantial undo and redo, or invalidate dependent objects. When availability requirements exceed what the exact clause provides, evaluate a controlled migration or online redefinition rather than assuming that all forms of ALTER TABLE behave alike.

Adding Modifying Column-Exercise

The next lesson examines the related effects of dropping tables and constraints.