How can I see my invisible columns

February 24, 2016

1–2 minutes

A cool new feature in 12c is the ability to make a column invisible. The concept has existed since 8i to handle things like “set unused” and function based indexes, but now it is available to developers directly.


SQL> create table T ( c1 int, c2 int );

Table created.

SQL> desc T
 Name                          Null?    Type
 ----------------------------- -------- -----------------
 C1                                     NUMBER(38)
 C2                                     NUMBER(38)

SQL> alter table T modify c2 invisible;

Table altered.

SQL> desc T
 Name                          Null?    Type
 ----------------------------- -------- -----------------
 C1                                     NUMBER(38)

So at this point… how I can tell in SQL Plus that I even have an invisible column, without querying the data dictionary.

It’s easy, we have a new setting – COLINVISIBLE



SQL> set colinvisible on
SQL> desc T
 Name                          Null?    Type
 ----------------------------- -------- -----------------
 C1                                     NUMBER(38)
 C2 (INVISIBLE)                         NUMBER(38)

Of course, if you want to play a practical joke on your work colleagues, you could do this:


SQL> desc t
 Name                          Null?    Type
 ----------------------------- -------- -------------
 C1                                     NUMBER(38)
 C2 (INVISIBLE)                         DATE
 C2 (INVISIBLE)                         NUMBER(38)

Smile

4 responses to “How can I see my invisible columns”

Mikhail L

April 9, 2020 at 5:18 pm

Hi Connor,

I don’t get it. How did you get the result below?)

Name Null? Type
—————————– ——– ————-
C1 NUMBER(38)
C2 (INVISIBLE) DATE
C2 (INVISIBLE) NUMBER(38)

Reply
1. Mikhail L
  
  April 9, 2020 at 5:26 pm
  
  I have achieved only this:
  
  SQL> create table t (c1 int, c2 int);
  
  Table T created.
  
  SQL> desc t;
  Name Null? Type
  —- —– ———-
  C1 NUMBER(38)
  C2 NUMBER(38)
  SQL> alter table t modify c2 invisible;
  
  Table T altered.
  
  SQL> set colinvisible on;
  SQL> desc t;
  Name Null? Type
  ————– —– ———-
  C1 NUMBER(38)
  C2 (INVISIBLE) NUMBER(38)
  SQL> alter table t set unused column c2;
  
  Table T altered.
  
  SQL> alter table t add c2 date;
  
  Table T altered.
  
  SQL> alter table t modify c2 invisible;
  
  Table T altered.
  
  SQL> desc t;
  Name Null? Type
  ————————————– —– ———-
  C1 NUMBER(38)
  C2 (INVISIBLE) DATE
  SYS_C00002_20040909:23:36$ (INVISIBLE) NUMBER(38)
  SQL> drop table t purge;
  
  Table T dropped.
  
  Reply
2. Connor McDonald
  
  April 9, 2020 at 6:25 pm
  
  You create a column called “C2 (INVISIBLE)” 🙂
  
  Reply
  1. Mikhail L
    
    April 9, 2020 at 9:57 pm
    
    Yeah, I got you. That’s pretty sneaky. 🙂
    
    Reply