Whenever there was a requirement to hide a column (or
columns) from the users, DBAs used to create views on the table with the
required columns and then allow users to access this view. Now, starting with
Oracle Database 12.1, there is an inbuilt functionality and new feature called
Invisible Columns to achieve this functionality. This means, a DBA can make
changes to a table without interrupting applications that use the table and
then make the column visible at a later stage, when the change implementation
is completed
Creating and Describing a table
As an example, let's create a table CUSTOMERS with the
column CUSTOMER_TYPE being invisible
rajesh@PDB1>
rajesh@PDB1>
create table customer
2 (
customer_id number,
3
customer_name varchar2(30),
4
customer_type varchar2(5) INVISIBLE);
Table
created.
rajesh@PDB1>
A Describe of this table does not display the column
CUSTOMER_TYPE
rajesh@PDB1>
desc customer
Name Null? Type
------------------------- --------
---------------
CUSTOMER_ID NUMBER
CUSTOMER_NAME VARCHAR2(30)
rajesh@PDB1>
The following operations will not display the INVISIBLE
columns.
·
SELECT * FROM statements in SQL
·
DESCRIBE commands in SQL*Plus
·
%ROWTYPE attribute declarations in PL/SQL
·
Describes in Oracle Call Interface (OCI)
However, the INVISIBLE column can be accessed by explicitly
mentioning the column name.
Inserting, querying and updating records
In order to insert data to the invisible column, the column
name should be explicitly mentioned in the INSERT statement:
rajesh@PDB1>
insert into customer values(1,'Tom','Gold');
insert
into customer values(1,'Tom','Gold')
*
ERROR at
line 1:
ORA-00913:
too many values
rajesh@PDB1>
insert into customer values(1,'Tom');
1 row
created.
rajesh@PDB1>
insert into customer(customer_id,customer_name,customer_type)
2
values(2,'Sofia','Bronz');
1 row
created.
rajesh@PDB1>
commit;
Commit
complete.
rajesh@PDB1>
A SELECT * statement will not display the contents of the
INVISIBLE column. But if you mention the column name explicitly, the data will
be displayed
rajesh@PDB1>
select * from customer ;
CUSTOMER_ID
CUSTOMER_NAME
-----------
------------------------------
1 Tom
2 Sofia
2 rows
selected.
rajesh@PDB1>
select customer_id,customer_name,customer_type
2 from
customer ;
CUSTOMER_ID
CUSTOMER_NAME CUSTO
-----------
------------------------------ -----
1 Tom
2 Sofia Bronz
2 rows
selected.
rajesh@PDB1>
The INVISIBLE columns can be a virtual column, or can be a
part of functional indexes and can even be used as a partitioning key also. In
our example, we will update the column CUSTOMER_TYPE.
rajesh@PDB1>
update customer
2 set
customer_type ='Gold'
3 where
customer_id = 2 ;
1 row
updated.
rajesh@PDB1>
select customer_id,customer_name,customer_type
2 from
customer ;
CUSTOMER_ID
CUSTOMER_NAME CUSTO
-----------
------------------------------ -----
1 Tom
2 Sofia Gold
2 rows
selected.
rajesh@PDB1>
Relevant Data Dictionary information
Since we now understand the functionality of the INVISIBLE
COLUMNS feature, it might be interesting to also understand how the information
is stored internally in the Data Dictionary tables.
When a column is marked as INVISIBLE, the COL# value for
that column to 0 in the Data Dictionary COL$ table (and the PROPERTY value is
also updated to mark it as invisible). Hence, this is not included in the
column order as SELECT * FROM uses this Column Ordering.
rajesh@PDB1>
rajesh@PDB1>
select col#,segcol#,name
2 from
sys.col$
3
where obj# = (select objecT_id
4 from
user_objects where object_name ='CUSTOMER');
COL#
SEGCOL# NAME
----------
---------- --------------------
1 1 CUSTOMER_ID
2 2 CUSTOMER_NAME
0 3 CUSTOMER_TYPE
3 rows
selected.
rajesh@PDB1>
rajesh@PDB1>
select column_name,column_id,hidden_column,user_generated
2 from
user_tab_cols
3
where table_name ='CUSTOMER' ;
COLUMN_NAME COLUMN_ID HID USE
--------------------
---------- --- ---
CUSTOMER_ID 1 NO YES
CUSTOMER_NAME 2 NO YES
CUSTOMER_TYPE
YES YES
3 rows
selected.
rajesh@PDB1>
Note: SEGCOL# remains the same irrespective of the changes. Invisible columns are User Generated HIDDEN COLUMNS, hence,
they can be changed to make them VISIBLE (System generated Hidden Columns
cannot be altered). However, note, that this will then change the column id.
rajesh@PDB1>
rajesh@PDB1>
alter table customer modify customer_type visible;
Table
altered.
rajesh@PDB1>
rajesh@PDB1>
select col#,segcol#,name
2 from
sys.col$
3
where obj# = (select objecT_id
4 from
user_objects where object_name ='CUSTOMER');
COL#
SEGCOL# NAME
----------
---------- --------------------
1 1 CUSTOMER_ID
2 2 CUSTOMER_NAME
3 3 CUSTOMER_TYPE
3 rows
selected.
rajesh@PDB1>
select column_name,column_id,hidden_column,user_generated
2 from
user_tab_cols
3
where table_name ='CUSTOMER' ;
COLUMN_NAME COLUMN_ID HID USE
--------------------
---------- --- ---
CUSTOMER_TYPE
3 NO YES
CUSTOMER_NAME 2 NO YES
CUSTOMER_ID 1 NO YES
3 rows
selected.
rajesh@PDB1>
Implications when changing visibility of columns in a table
Whenever the existing columns in a table are made
INVISIBLE/VISIBLE, the Column Ordering of the table can change (depending on
the position of the column being altered). Hence a DBA needs to be cautious
changing the columns to VISIBLE and INVISIBLE. From an application perspective,
it's always recommended to have fully qualified column names in the SELECT
query.
rajesh@PDB1>
select * from customer ;
CUSTOMER_ID
CUSTOMER_NAME CUSTO
-----------
------------------------------ -----
1 Tom
2 Sofia Gold
2 rows
selected.
rajesh@PDB1>
alter table customer modify customer_name invisible;
Table
altered.
rajesh@PDB1>
select column_name,column_id,hidden_column,user_generated
2 from
user_tab_cols
3
where table_name ='CUSTOMER';
COLUMN_NAME COLUMN_ID HID USE
--------------------
---------- --- ---
CUSTOMER_ID 1 NO YES
CUSTOMER_NAME
YES YES
CUSTOMER_TYPE 2 NO YES
3 rows
selected.
rajesh@PDB1>
alter table customer modify customer_name visible;
Table
altered.
rajesh@PDB1>
select column_name,column_id,hidden_column,user_generated
2 from
user_tab_cols
3
where table_name ='CUSTOMER';
COLUMN_NAME COLUMN_ID HID USE
--------------------
---------- --- ---
CUSTOMER_TYPE 2 NO YES
CUSTOMER_NAME
3 NO YES
CUSTOMER_ID 1 NO YES
3 rows
selected.
rajesh@PDB1>
rajesh@PDB1>
select * from customer;
CUSTOMER_ID
CUSTO CUSTOMER_NAME
-----------
----- ------------------------------
1 Tom
2 Gold Sofia
2 rows
selected.
rajesh@PDB1>
