There can be several situations where you want to test the impact of dropping a column from a table, till 11gR2 Oracle provided an option to mark a column as UNUSED before dropping a column, and do ALTER TABLE … DROP UNUSED COLUMNS at a later time but marking a column UNUSED is a irreversible process, so if you realize that dropping that column did break something there is no going back.

To remedy this Invisible Columns was introduced in Oracle 12cR2 by which you can set a column in table as invisible either during CREATE TABLE or modifying existing table via ALTER TABLE command, so if you want to test the impact of dropping a column you can mark it as invisible and then further drop or make it back to visible depending on your testing. Other use case would be when you have an application used by many teams and you want to test a table change before collaborating with all other teams, you can create a invisible column and do your testing.

When you make a column invisible, the COL# column in COL$ dictionary is updated to 0, and that’s why it is not included in the “SELECT *” or “INSERT INTO VALUES” statements unless specifically selected.

Now lets play along with the invisible column feature:-

Create Table with Invisible Column:-

CREATE TABLE t3(
  id NUMBER,
  Name Varchar2(30),
  Salary NUMBER INVISIBLE
);

Check if the column is invisible:-

SQL> desc t3
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                             NUMBER
 NAME                                                           NUMBER

SELECT column_id,
       column_name,
       hidden_column
FROM   user_tab_cols
WHERE  table_name = 'T3'
ORDER BY column_id;

Hidden Column of USER_TAB_COLS table would be equal to “YES” for invisible columns.

Insert records in a table with invisible column:-

SQL> insert into t3 (id, name) values (1, 'Nitish');

1 row created.

SQL> commit;

SQL> insert into t3 (id, name,salary) values (2, 'DBA', 10000);

1 row created.

SQL> commit;

Query Table with Invisible Column:-

select * from T3;

        ID NAME
---------- ------------------------------
         1 Nitish
         2 DBA

SQL> select id, name, salary from t3;

        ID NAME                               SALARY
---------- ------------------------------ ----------
         1 Nitish
         2 DBA                                 10000

Thus for SELECT and INSERT INTO VALUES, the column is as good as invisible until you explicitly mention the invisible column name in the statement.

And finally to change the invisible column back to visible:

SQL> ALTER TABLE T3 MODIFY Salary VISIBLE;

Table altered.

SQL>
SQL>
SQL> select * from t3;

        ID NAME                               SALARY
---------- ------------------------------ ----------
         1 Nitish
         2 DBA                                 10000

NOTE that when you change an invisible column in Oracle 12c database to visible, the COL# assigned will be the highest available, so the column becomes the last column in the table (not storage, only display). So, if you accidentally make a column invisible and correct this by changing it to visible, the column order changes which might affect your application.

Hope this helps in case of any questions or assistance please feel free to comment on this post and we would try to assist you.