How to Use Invisible Columns in Oracle

Adding a column to a table can break code that relies on SELECT * or on INSERT statements without a column list. An invisible column avoids that: it is left out of SELECT *, of DESCRIBE, and of INSERT without a column list, but anyone who names it can read and write it. It lets a table grow without disturbing existing code.

Code for This Guide

The main examples are in the examples/tables folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

create table t (... column type invisible ...)
alter table t add (column type invisible [default expr])
alter table t modify (column {visible | invisible})

Add an Invisible Audit Column

This adds an invisible column recording who made a change, with the user as its default, to a table that existing code inserts into without a column list.

Example:

create table gate_changes (flight_id number, gate varchar2(4));
alter table gate_changes add (changed_by varchar2(30) invisible default user);

insert into gate_changes values (2800, 'B7');        -- no column list, no invisible column

select * from gate_changes;

select flight_id, gate, changed_by from gate_changes;

Output:

Table GATE_CHANGES created.

Table GATE_CHANGES altered.

1 row inserted.

   FLIGHT_ID GATE
____________ _______
        2800 B7

   FLIGHT_ID GATE    CHANGED_BY
____________ _______ _____________
        2800 B7      NIMBUS

The INSERT without a column list still works, since it does not see the new column, and the default fills it. SELECT * shows two columns; naming changed_by shows its value.

Column Positions

An invisible column has no column position. Making it visible gives it the last position, which is how to reorder columns without rebuilding the table.

Example:

create table gate_log (flight_id number, note varchar2(20) invisible, gate varchar2(4));
select column_name, column_id, hidden_column from user_tab_cols where table_name = 'GATE_LOG' order by internal_column_id;

-- making it visible gives it the last position
alter table gate_log modify (note visible);
select column_name, column_id from user_tab_columns where table_name = 'GATE_LOG' order by column_id;

Output:

Table GATE_LOG created.

COLUMN_NAME       COLUMN_ID HIDDEN_COLUMN
______________ ____________ ________________
FLIGHT_ID                 1 NO
NOTE                        YES
GATE                      2 NO

Table GATE_LOG altered.

COLUMN_NAME       COLUMN_ID
______________ ____________
FLIGHT_ID                 1
GATE                      2
NOTE                      3

In USER_TAB_COLS, the invisible column has no COLUMN_ID and HIDDEN_COLUMN = YES. After MODIFY ... VISIBLE, it is column 3, after gate. Drop the work table afterward with drop table gate_log purge.

Things to Know

• Invisible is not secret: it hides a column from implicit references, not from users with access to the table. Use privileges or Data Redaction to protect data.
• Views created with SELECT * do not include invisible columns.
• Make several columns invisible and then visible again in the desired order to reorder them.

Related Guides

• How to Create Virtual Columns in Oracle
• Mask Sensitive Data in Oracle APEX with Oracle Data Redaction (DBMS_REDACT)

Conclusion

Invisible columns are left out of SELECT *, DESCRIBE, and INSERT without a column list, but work normally when named. Use them to add columns without breaking existing code, and to change the order of columns by making them visible again.