Invisible Columns in Oracle Database 12c

Invisible Columns in Oracle Database 12c Release 1 (12.1)

Invisible columns can be useful during application migrations. Making new columns invisible means they won't be seen by any existing applications, but can still be referenced by any new applications, making the online migration of applications much simpler.

• Invisible Columns
• Invisible Columns and Column Ordering
• Mandatory Invisible Columns

Invisible Columns

Making a column invisible means it is no longer seen by SELECT * FROM, SQL*Plus or OCI describes and %ROWTYPE attributes.

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id          NUMBER,
  description VARCHAR2(50) INVISIBLE
);

DESC tab1;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID						    NUMBER

SQL>

INSERT INTO tab1 VALUES (1);
COMMIT;

SELECT * FROM tab1;

	ID
----------
	 1

SQL>

Invisible columns are still available for all actions, provided they are named explicitly.

INSERT INTO tab1 (id, description) VALUES (2, 'TWO');
COMMIT;

SELECT id, description
FROM   tab1;

	ID DESCRIPTION
---------- --------------------------------------------------
	 1
	 2 TWO

SQL>

Some miscellaneous facts about invisible columns include the following.

• Virtual columns can be made invisible.
• A table can be partitioned by an invisible column, either during or after table creation.
• External, cluster and temporary tables can not have invisible columns.
• User-defined types can not contain invisible attributes.
• You can not make system generated hidden columns visible.

We can display invisible columns using the DESCRIBE command by setting the COLINVISIBLE option.

SQL> DESC tab1
 Name						       Null?	Type
 ----------------------------------------------------- -------- ------------------------------------
 ID								NUMBER

SQL> SET COLINVISIBLE ON
SQL> DESC tab1
 Name						       Null?	Type
 ----------------------------------------------------- -------- ------------------------------------
 ID								NUMBER
 DESCRIPTION (INVISIBLE)					VARCHAR2(50)

SQL>

Invisible Columns and Column Ordering

Invisible columns are not assigned a column order, so if an invisible column is made visible it is listed as the last column of the table.

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  a NUMBER,
  b NUMBER,
  c NUMBER INVISIBLE
);

COLUMN column_name FORMAT A15

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

 COLUMN_ID COLUMN_NAME	   HID
---------- --------------- ---
	 1 A		   NO
	 2 B		   NO
	   C		   YES

SQL> 

ALTER TABLE tab1 MODIFY b INVISIBLE;
ALTER TABLE tab1 MODIFY c VISIBLE;

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

 COLUMN_ID COLUMN_NAME	   HID
---------- --------------- ---
	 1 A		   NO
	 2 C		   NO
	   B		   YES

SQL>

ALTER TABLE tab1 MODIFY b VISIBLE;

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

 COLUMN_ID COLUMN_NAME	   HID
---------- --------------- ---
	 1 A		   NO
	 2 C		   NO
	 3 B		   NO

SQL>

Mandatory Invisible Columns

Making a column invisible does not affect its mandatory/optional status, as shown in the example below.

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id          NUMBER NOT NULL,
  description VARCHAR2(50) NOT NULL,
  created_date DATE INVISIBLE NOT NULL
);

COLUMN column_name FORMAT A20

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

 COLUMN_ID COLUMN_NAME		N HID
---------- -------------------- - ---
	 1 ID			N NO
	 2 DESCRIPTION		N NO
	   CREATED_DATE 	N YES

SQL>

INSERT INTO tab1 VALUES (1, 'ONE');
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."TAB1"."CREATED_DATE")

SQL>

ALTER TABLE tab1 MODIFY created_date NULL;
INSERT INTO tab1 VALUES (1, 'ONE');

1 row created.

SQL>

Invisible Column으로 설정하는 경우 컬럼 순서가 변경된다.

컬럼 순서 변경하고자 할때 테이블 다시 생성하지 않고 변경할 수 있다.

출처

https://oracle-base.com/articles/12c/invisible-columns-12cr1