How can I see my invisible columns

Posted by

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 comments

  1. 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)

    1. 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.

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.