Oracle 12c中如何移除新增DEFAULT NULL列时产生的隐藏列？

Let’s start by breaking down the context here: In Oracle 12c, when you add a column using ALTER TABLE ... ADD (column TYPE DEFAULT xxx NULL), Oracle creates a hidden system column (like SYS_NC00002$ in your test case) to support its DDL optimization feature. Here’s the example you shared that demonstrates this behavior:

CREATE TABLE bar (i NUMBER);
ALTER TABLE bar ADD (d NUMBER DEFAULT 1 NULL);
SELECT column_name, data_type, hidden_column FROM user_tab_cols WHERE table_name = 'BAR';

The output confirms the hidden column exists:

COLUMN_NAME  DATA_TYPE HIDDEN_COLUMN
------------ --------- -------------
I            NUMBER    NO
SYS_NC00002$ RAW       YES
D            NUMBER    NO

As documented in DDL Optimization in Oracle Database 12c, this hidden column enables fast DDL operations for adding default-valued columns without rewriting the entire table upfront.

Methods You’ve Tried (That Don’t Work) #

You already tested a few approaches that fail to eliminate the hidden column:

• Full table rebuild: CREATE TABLE newbar AS SELECT * FROM bar; technically works but requires manually recreating all dependent objects (comments, triggers, permissions, indexes) — a costly and error-prone process for production tables.
• Drop unused columns: ALTER TABLE bar DROP UNUSED COLUMNS; leaves the hidden column intact, as your follow-up query results show.
• Table move: ALTER TABLE bar MOVE; also does not remove the hidden system column.

We also know we can prevent this hidden column from being created in the first place by disabling the optimization feature:

ALTER SESSION SET "_add_col_optim_enabled" = FALSE;

But this is a proactive measure, not a fix for tables where the hidden column already exists.

A Lightweight Alternative (Similar to ALTER TABLE MOVE) #

If you want to remove the hidden column without rebuilding all dependencies, online table redefinition is the optimal solution. This operation keeps the table accessible during the process and automatically migrates all dependent objects (indexes, triggers, grants, etc.). Here’s step-by-step how to do it:

1. Create an interim table containing only the visible columns you need:

CREATE TABLE bar_temp AS SELECT i, d FROM bar WHERE 1=0;

2. Start the redefinition process, explicitly mapping only your visible columns (excluding the hidden system column):

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname       => USER,
    orig_table  => 'BAR',
    int_table   => 'BAR_TEMP',
    col_mapping => 'i AS i, d AS d'
  );
END;
/

3. Sync any incremental changes that occurred during the initial redefinition (critical for active, write-heavy tables):

BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
    uname       => USER,
    orig_table  => 'BAR',
    int_table   => 'BAR_TEMP'
  );
END;
/

4. Complete the redefinition — this swaps the interim table with the original table in the data dictionary:

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
    uname       => USER,
    orig_table  => 'BAR',
    int_table   => 'BAR_TEMP'
  );
END;
/

5. Clean up the now-unused interim table:

DROP TABLE bar_temp;

After running these steps, query user_tab_cols again and you’ll see the hidden column is gone, with all your table’s dependent objects fully intact.

内容的提问来源于stack exchange，提问作者wolφi