In May 2014, the following #oratidbit relating to invisible columns were tweeted.
- In #DB12c, you can make a column in table as INVISIBLE. When you make it invisible, the COL# column in COL$ dictionary is updated to 0, thus is not included in the “SELECT *” or “INSERT INTO VALUES”, unless specifically selected.
- When you make an INVISIBLE column in Oracle 12c database to VISIBLE, the COL# is assigned the highest available. Thus the column becomes the last column in the table (not storage, only display). So, if you accidentally make a column INVISIBLE and correct it by changing to VISIBLE, the column order changes. So, if the application uses “SELECT *” or “INSERT” without column names, they might break!
Why would you make a column invisible? There are not many reasons why suddenly you would make a column invisible, one situation comes to mind is you want to test the waters before dropping the column from table – to figure out if something breaks or someone yells. Oracle provides an option to mark a column as UNUSED before you DROP, and do ALTER TABLE … DROP UNUSED COLUMNS at a later time. Once you mark a column as UNUSED, there is no going back to undo the action. So marking it INVISIBLE before drop is a good idea. Another use could be that you have a running application used by many teams – before you collaborate with everyone on a table change, you could test the changes in the table by creating the new column as invisible, do your basic tests, then talk to the other teams and make the column visible to all.
Let me show you the behavior of INVISIBLE column and UNUSED column in the data dictionary. Create a table with the following characteristics. Columns 1, 4 & 7 are regular columns with no specialty. x2 is invisible, x3 is virtual, x5 is invisible and virtual, x6 is identity.
SQL> create table hidden_test ( x1 number, x2 number invisible, x3 number generated always as (x1/2) virtual, x4 number, x5 number invisible generated always as (x1/4) virtual, x6 number generated always as identity, x7 number ); Table created.
Query the column properties of the table just created.
SQL> col column_name format a5 SQL> select column_name, user_generated, virtual_column, hidden_column, identity_column, column_id, segment_column_id, internal_column_id from dba_tab_cols where table_name = 'HIDDEN_TEST'; COLUM USE VIR HID IDE COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID ----- --- --- --- --- ---------- ----------------- ------------------ X7 YES NO NO NO 5 5 7 X6 YES NO NO YES 4 4 6 X5 YES YES YES NO 5 X4 YES NO NO NO 3 3 4 X3 YES YES NO NO 2 3 X2 YES NO YES NO 2 2 X1 YES NO NO NO 1 1 1
Notice all columns have USER_GENERATED as YES, the invisible columns are marked as HIDDEN_COLUMN=YES. Now, mark x4 for drop and x2 visible.
SQL> alter table hidden_test set unused (x4); Table altered. SQL> alter table hidden_test modify x2 visible; Table altered.
Query the properties again…
SQL> select column_name, user_generated, virtual_column, hidden_column, identity_column, column_id, segment_column_id, internal_column_id from dba_tab_cols where table_name = 'HIDDEN_TEST'; COLUMN_NAM USE VIR HID IDE COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID ---------- --- --- --- --- ---------- ----------------- ------------------ X7 YES NO NO NO 4 5 7 X6 YES NO NO YES 3 4 6 X5 YES YES YES NO 5 SYS_C00004 NO NO YES NO 3 4 _14060322: 52:06$ X3 YES YES NO NO 2 3 X2 YES NO NO NO 5 2 2 X1 YES NO NO NO 1 1 1 7 rows selected.
The x4 column name marked for drop got renamed, and got a system generated name. The rename is to facilitate adding a column with same name to the table. The column x4 also got property changes – USER_GENERATED became NO and HIDDEN_COLUMN changed to YES. Also, the COLUMN_ID is released, thus the column will not be visible in “SELECT *” and “DESCRIBE”. The UNUSED column still maintains the same INTERNAL_COLUMN_ID.
When x2 column is made VISIBLE, it got a new COLUMN_ID assigned (the highest available, thus the column becomes the last column in “SELECT *” and “DESCRIBE”). Its hidden status changed to NO.
What happens to the ID columns, when the UNUSED column is dropped…?
SQL> alter table hidden_test drop unused columns; Table altered. SQL> select column_name, user_generated, virtual_column, hidden_column, identity_column, column_id, segment_column_id, internal_column_id from dba_tab_cols where table_name = 'HIDDEN_TEST'; COLUMN_NAM USE VIR HID IDE COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID ---------- --- --- --- --- ---------- ----------------- ------------------ X7 YES NO NO NO 4 4 6 X6 YES NO NO YES 3 3 5 X5 YES YES YES NO 4 X3 YES YES NO NO 2 3 X2 YES NO NO NO 5 2 2 X1 YES NO NO NO 1 1 1
The COLUMN_ID got reordered after the unused column is dropped (X4 was dropped, hence X6 and X7 got new column ids – X5 never had a column id assigned as it is INVISIBLE). The internal column id, which includes id for INVISIBLE columns also got adjusted. After the column is dropped, the INTERNAL_COLUMN_ID also got adjusted.
Tip: By default SQL*Plus DESCRIBE will not show the invisible columns in a table. If you want to see the invisible columns in DESCRIBE, use SET COLINVISIBLE ON.