1

I created a table with an invisible column:

CREATE C39293.JUNK (
  id          NUMBER,
  JUNKCOL VARCHAR2(50) INVISIBLE
);

Verified that the table was created :

select * from all_tab_columns where table_name = 'JUNK'

Output: 

1   THE_OWNER_SCHEMA    JUNK    JUNKCOL VARCHAR2            50          Y           (WideMemo)                                  CHAR_CS 50  NO  NO      50  B   NO  YES NONE    NO  NO          
2   THE_OWNER_SCHEMA    JUNK    ID  NUMBER          22          Y   1       (WideMemo)                                          NO  NO      0       NO  YES NONE    NO  NO

I expected this view to show me all invisible columns it did not.

select * from dba_unused_col_tabs;

No Records found

How do I query all the invisible columns in the database?

Chad
  • 23,658
  • 51
  • 191
  • 321
  • 1
    Unused and invisible are two different terms. Column is unused if you `ALTER TABLE T SET UNUSED(c)`. It disables the column from being used without freeing any data from disk, thus preserving the same performance of the database. You delete the column later in a more idle time of a day. Invisible column is not shown to applications, yet it still exists and works the same – Alexey S. Larionov May 05 '20 at 13:20

1 Answers1

3
SELECT table_name, column_name, hidden_column 
  FROM user_tab_cols 
 WHERE hidden_column='YES';

Or, as you asked for all columns in the database:

SELECT owner, table_name, column_name, hidden_column 
  FROM all_tab_cols 
 WHERE hidden_column='YES';

EDIT:

ALL_TAB_COLS is nicely explained in another question

wolφi
  • 8,091
  • 2
  • 35
  • 64