{"id":1619,"date":"2015-10-14T23:37:12","date_gmt":"2015-10-15T04:37:12","guid":{"rendered":"https:\/\/bijoos.com\/oraclenotes\/?p=1619"},"modified":"2026-02-07T15:34:43","modified_gmt":"2026-02-07T21:34:43","slug":"invisible-columns-in-oracle-database-12c","status":"publish","type":"post","link":"https:\/\/bijoos.com\/oraclenotes\/2015\/1619\/","title":{"rendered":"Invisible Columns in Oracle Database 12c"},"content":{"rendered":"<p>In May 2014, the following #oratidbit relating to invisible columns were tweeted.<\/p>\n<ul>\n<li><em>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 &#8220;SELECT *&#8221; or &#8220;INSERT INTO VALUES&#8221;, unless specifically selected.<\/em><\/li>\n<li><em>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 &#8220;SELECT *&#8221; or &#8220;INSERT&#8221; without column names, they might break!<\/em><\/li>\n<\/ul>\n<p>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 \u2013 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 \u2026 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 \u2013 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.<\/p>\n<p>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 &amp; 7 are regular columns with no specialty. x2 is invisible, x3 is virtual, x5 is invisible and virtual, x6 is identity.<\/p>\n<pre class=\"lang:default highlight:0 decode:true \">SQL&gt; create table hidden_test (\n\u00a0\u00a0\u00a0\u00a0 x1 number,\n\u00a0\u00a0\u00a0\u00a0 x2 number invisible,\n\u00a0\u00a0\u00a0\u00a0 x3 number generated always as (x1\/2) virtual,\n\u00a0\u00a0\u00a0\u00a0 x4 number,\n\u00a0\u00a0\u00a0\u00a0 x5 number invisible generated always as (x1\/4) virtual,\n\u00a0\u00a0\u00a0\u00a0 x6 number generated always as identity,\n \u00a0\u00a0\u00a0 x7 number );\n\nTable created.<\/pre>\n<p>Query the column properties of the table just created.<\/p>\n<pre class=\"lang:default highlight:0 decode:true \">SQL&gt; col column_name format a5\nSQL&gt; select column_name, user_generated, virtual_column, hidden_column,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 identity_column, column_id, segment_column_id, internal_column_id\n\u00a0\u00a0\u00a0\u00a0 from dba_tab_cols\n\u00a0\u00a0\u00a0\u00a0 where table_name = 'HIDDEN_TEST';\n\nCOLUM USE VIR HID IDE\u00a0 COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID\n----- --- --- --- --- ---------- ----------------- ------------------\nX7\u00a0\u00a0\u00a0 YES NO\u00a0 NO\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7\nX6\u00a0\u00a0\u00a0 YES NO\u00a0 NO\u00a0 YES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6\nX5\u00a0\u00a0\u00a0 YES YES YES NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\nX4\u00a0\u00a0\u00a0 YES NO\u00a0 NO\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\nX3\u00a0\u00a0\u00a0 YES YES NO\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\nX2\u00a0\u00a0\u00a0 YES NO\u00a0 YES NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\nX1\u00a0\u00a0\u00a0 YES NO\u00a0 NO\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a01\n<\/pre>\n<p>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.<\/p>\n<pre class=\"lang:default highlight:0 decode:true \">SQL&gt; alter table hidden_test set unused (x4);\n\nTable altered.\n\nSQL&gt; alter table hidden_test modify x2 visible;\n\nTable altered.\n<\/pre>\n<p>Query the properties again\u2026<\/p>\n<pre class=\"lang:default highlight:0 decode:true\">SQL&gt; select column_name, user_generated, virtual_column, hidden_column,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 identity_column, column_id, segment_column_id, internal_column_id\n\u00a0\u00a0\u00a0\u00a0 from dba_tab_cols\n\u00a0\u00a0\u00a0\u00a0 where table_name = 'HIDDEN_TEST';\n\nCOLUMN_NAM USE VIR HID IDE\u00a0 COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID\n---------- --- --- --- --- ---------- ----------------- ------------------\nX7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 YES NO\u00a0 NO\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7\nX6\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 YES NO\u00a0 NO\u00a0 YES\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6\nX5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 YES YES YES NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\nSYS_C00004 NO\u00a0 NO\u00a0 YES NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\n_14060322:\n52:06$\nX3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 YES YES NO\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03\nX2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 YES NO\u00a0 NO\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\nX1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 YES NO\u00a0 NO\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\n\n7 rows selected.<\/pre>\n<p>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 \u2013 USER_GENERATED became NO and HIDDEN_COLUMN changed to YES. Also, the COLUMN_ID is released, thus the column will not be visible in \u201cSELECT *\u201d and \u201cDESCRIBE\u201d. The UNUSED column still maintains the same INTERNAL_COLUMN_ID.<\/p>\n<p>When x2 column is made VISIBLE, it got a new COLUMN_ID assigned (the highest available, thus the column becomes the last column in \u201cSELECT *\u201d and \u201cDESCRIBE\u201d). Its hidden status changed to NO.<\/p>\n<p>What happens to the ID columns, when the UNUSED column is dropped\u2026?<\/p>\n<pre class=\"lang:default highlight:0 decode:true \">SQL&gt; alter table hidden_test drop unused columns;\n\nTable altered.\n\nSQL&gt; select column_name, user_generated, virtual_column, hidden_column,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 identity_column, column_id, segment_column_id, internal_column_id\n\u00a0\u00a0\u00a0\u00a0 from dba_tab_cols\n\u00a0\u00a0\u00a0\u00a0 where table_name = 'HIDDEN_TEST';\n\nCOLUMN_NAM USE VIR HID IDE\u00a0 COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID\n---------- --- --- --- --- ---------- ----------------- ------------------\nX7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 YES NO\u00a0 NO\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6\nX6\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 YES NO\u00a0 NO\u00a0 YES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\nX5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 YES YES YES NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\nX3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 YES YES NO\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\nX2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 YES NO\u00a0 NO\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\nX1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 YES NO\u00a0 NO\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\n<\/pre>\n<p>The COLUMN_ID got reordered after the unused column is dropped (X4 was dropped, hence X6 and X7 got new column ids \u2013 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.<\/p>\n<p>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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 &#8220;SELECT *&#8221; or &#8220;INSERT INTO VALUES&#8221;, unless specifically selected. When you &#8230; <a title=\"Invisible Columns in Oracle Database 12c\" class=\"read-more\" href=\"https:\/\/bijoos.com\/oraclenotes\/2015\/1619\/\" aria-label=\"Read more about Invisible Columns in Oracle Database 12c\">Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[133],"tags":[],"class_list":["post-1619","post","type-post","status-publish","format-standard","hentry","category-oracledb"],"acf":[],"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/1619","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/comments?post=1619"}],"version-history":[{"count":1,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/1619\/revisions"}],"predecessor-version":[{"id":3015,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/1619\/revisions\/3015"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=1619"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=1619"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=1619"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}