This new feature in 12C allows to make invisible columns for generic queries, operations. To make invisible column you need to use INVISIBLE clause.

Following operations don’t see invisible columns

• SELECT * FROM statements in SQL
• DESCRIBE commands in SQL*Plus
• %ROWTYPE attribute declarations in PL/SQL
• Describes in Oracle Call Interface (OCI)

Invisible can be declared during table creation

create table test_tbl
 (  id  number,
    id1 number INVISIBLE,
    id2 number,
    id3 number,
    id4 number
 );

or by alter table

alter table test_tbl modify(id2 INVISIBLE);

generic operations don’t see INVISIBLE columns

desc test_tbl;

Name Null Type   
---- ---- ------ 
ID        NUMBER 
ID3       NUMBER 
ID4       NUMBER

so backup using SELECT * FROM doesn’t copy INVISIBLE columns

create test_tbl_bkp
as
select * from test_tbl;

desc test_tbl_bkp

Name Null Type   
---- ---- ------ 
ID        NUMBER 
ID3       NUMBER 
ID4       NUMBER

but still you can access INVISIBLE column explicit

create table test_tbl_bkp1
as
select id, id1, id2, id3, id4 from test_tbl;

desc test_tbl_bkp1

Name Null Type   
---- ---- ------ 
ID        NUMBER 
ID1       NUMBER 
ID2       NUMBER 
ID3       NUMBER 
ID4       NUMBER

insert into test_tbl
(id, id1, id2, id3, id4)
values(1,1,1,1,1);

commit;

select id, id1, id2, id3, id4 from test_tbl;

        ID        ID1        ID2        ID3        ID4
---------- ---------- ---------- ---------- ----------
         1          1          1          1          1

INVISIBLE has got impact on ordering column_id USER|ALL|DBA_TAB_COLUMNS. For all INVISIBLE columns column_id is null.

select table_name, column_name, column_id
from user_tab_columns where table_name='TEST_TBL';

TABLE_NAME  COLUMN_NAME  COLUMN_ID
----------- ------------ ----------
TEST_TBL    ID4          3
TEST_TBL    ID3          2
TEST_TBL    ID2    
TEST_TBL    ID1    
TEST_TBL    ID           1

each modification from INVISIBLE to VISIBLE will put modified column as last for ordering.

alter table test_tbl modify(id2 VISIBLE);

select table_name, column_name, column_id
from user_tab_columns where table_name='TEST_TBL';

TABLE_NAME  COLUMN_NAME  COLUMN_ID
----------- ------------ ----------
TEST_TBL    ID4          3
TEST_TBL    ID3          2
TEST_TBL    ID2          4
TEST_TBL    ID1    
TEST_TBL    ID           1

Virtual INVISIBLE columns are allowed in 12C

create table test_tbl1
( id  number,
  id1 INVISIBLE generated always as (id+1) virtual);

desc test_tbl1

Name Null Type   
---- ---- ------ 
ID        NUMBER

Partitioning on INVISIBLE columns is supported as well

create table test_tbl2
( id  number,
  id1 INVISIBLE generated always as (id+1) virtual)
partition by range(id1)
(
  partition p1 values less than(10),
  partition p2 values less than(maxvalue)
);

desc test_tbl2

Name Null Type   
---- ---- ------ 
ID        NUMBER

SET COLINVISIBLE is new caluse in sqlplus to see invisible columns

set colinvisible on

desc test_tbl2

Name            Null Type   
--------------- ---- ------ 
ID                   NUMBER
ID1(INVISIBLE)       NUMBER

Have a fun 🙂

Tomasz