{"id":421,"date":"2014-06-18T21:58:09","date_gmt":"2014-06-19T02:58:09","guid":{"rendered":"https:\/\/bijoos.com\/oraclenotes\/?p=421"},"modified":"2026-02-07T15:34:44","modified_gmt":"2026-02-07T21:34:44","slug":"dba_tab_columns-vs-dba_tab_cols","status":"publish","type":"post","link":"https:\/\/bijoos.com\/oraclenotes\/2014\/421\/","title":{"rendered":"DBA_TAB_COLUMNS vs DBA_TAB_COLS"},"content":{"rendered":"

Not sure how many DBAs have noticed the difference between DBA_TAB_COLUMNS<\/strong> view and DBA_TAB_COLS<\/strong> view. Both views look pretty identical, if you are not paying too much attention. According to Oracle documentation, DBA_TAB_COLS describes the columns of all tables, views, and clusters in the database. This view differs from “DBA_TAB_COLUMNS” in that system-generated hidden columns and invisible columns, which are user-generated hidden columns, are not filtered out<\/em>.<\/p>\n

Let me dig a little deep to understand the differences better. First, how the views are built…<\/p>\n

Until 11gR2, DBA_TAB_COLS view is a complex SQL joining multiple internal dictionary tables. The FROM clause is shown here…<\/p>\n

The FROM clause of DBA_TAB_COLS view in 10gR2 is…<\/p>\n

--10gR2 \nSQL> set long 32000 \nSQL> select text from dba_views \n     where view_name = 'DBA_TAB_COLS'; \n \n     from sys.col$ c, sys.obj$ o, sys.hist_head$ h, sys.user$ u, \n          sys.coltype$ ac, sys.obj$ ot, sys.user$ ut \n<\/pre>\n
The FROM clause of DBA_TAB_COLS view in 11gR2 is…<\/p>\n
--11gR2\nSQL> select text from dba_views \n     where view_name = 'DBA_TAB_COLS';\n\n     from sys.col$ c, sys.\"_CURRENT_EDITION_OBJ\" o, sys.hist_head$ h, \n     sys.user$ u, sys.coltype$ ac, sys.obj$ ot, \n     sys.\"_BASE_USER\" ut, sys.tab$ t\n<\/pre>\n
Because of the increased complexity by introducing new features like invisible columns, I think Oracle decided to create an intermediate view named DBA_TAB_COLS_V$<\/strong> to make the SQL for DBA_TAB_COLS simpler in 12c. The view SQL for SYS.DBA_TAB_COLS_V$ is similar to the SQL used in DBA_TAB_COLS in 11gR2, but has more lines.<\/p>\n
So, in 12c, the DBA_TAB_COLS view is a straight SELECT of columns from DBA_TAB_COLS_V$ without any WHERE clause.<\/p>\n
In all versions the DBA_TAB_COLUMNS is a query on selected columns from DBA_TAB_COLS with a WHERE clause to filter the hidden columns. In 10g and 11g, the WHERE clause is where HIDDEN_COLUMN = ‘NO’<\/em><\/strong> and in 12c the WHERE clause changed to where USER_GENERATED = ‘YES’<\/em><\/strong>.<\/p>\n
Full SQL used for the DBA_TAB_COLUMNS view in 12c is below.<\/p>\n
--12cR1\nSQL> set long 32000\nSQL> select text from dba_views where view_name = 'DBA_TAB_COLUMNS';\n\nTEXT\n----------------------------------------------------------------------\nselect OWNER, TABLE_NAME,\nCOLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,\nDATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,\nDEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,\nDENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,\nCHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,\nGLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,\nV80_FMT_IMAGE, DATA_UPGRADED, HISTOGRAM, DEFAULT_ON_NULL,\nIDENTITY_COLUMN, SENSITIVE_COLUMN,\nEVALUATION_EDITION, UNUSABLE_BEFORE, UNUSABLE_BEGINNING\nfrom DBA_TAB_COLS\nwhere USER_GENERATED = 'YES'<\/pre>\n
Which columns are available in DBA_TAB_COLS that are not in DBA_TAB_COLUMNS? Following query shows the columns that are additional in DBA_TAB_COLS in 12cR1.<\/p>\n
SQL> select column_name from dba_tab_cols \n     where table_name = 'DBA_TAB_COLS'\n     minus\n     select column_name from dba_tab_cols \n     where table_name = 'DBA_TAB_COLUMNS';\n\nCOLUMN_NAME\n-----------------------------\nHIDDEN_COLUMN\nINTERNAL_COLUMN_ID\nQUALIFIED_COL_NAME\nSEGMENT_COLUMN_ID\nUSER_GENERATED\nVIRTUAL_COLUMN<\/pre>\n
USER_GENERATED column is new in 12c, all other columns are same in 11gR2\/10gR2 as well.<\/p>\n
When you mark a column in table as UNUSED (that is a candidate for drop), it becomes hidden in 11gR2. Is Hidden Column different from Invisible Column in 12c DBA_TAB_COLS?<\/p>\n
Let me create a table with the following characteristics. Columns 1, 4 & 7 are regular columns with no speciality. x2 is invisible, x3 is virtual, x5 is invisible and virtual, x6 is identity.<\/p>\n
SQL> create table hidden_test (\n     x1 number,\n     x2 number invisible,\n     x3 number generated always as (x1\/2) virtual,\n     x4 number,\n     x5 number invisible generated always as (x1\/4) virtual,\n     x6 number generated always as identity,\n     x7 number );\n\nTable created.<\/pre>\n
Query the column properties of the table just created.<\/p>\n
SQL> col column_name format a5\nSQL> select column_name, user_generated, virtual_column, hidden_column, \n            identity_column, column_id, segment_column_id, internal_column_id\n     from dba_tab_cols\n     where table_name = 'HIDDEN_TEST';\n\nCOLUM USE VIR HID IDE  COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID\n----- --- --- --- --- ---------- ----------------- ------------------\nX7    YES NO  NO  NO           5                 5                  7\nX6    YES NO  NO  YES          4                 4                  6\nX5    YES YES YES NO                                                5\nX4    YES NO  NO  NO           3                 3                  4\nX3    YES YES NO  NO           2                                    3\nX2    YES NO  YES NO                             2                  2\nX1    YES NO  NO  NO           1                 1                  1\n\n7 rows selected.<\/pre>\n
Notice all columns have USER_GENERATED as YES, which means all of these columns will be visible in DBA_TAB_COLUMNS. Remeber, this is the filter condition for DBA_TAB_COLUMNS, so for all subsequent queries on DBA_TAB_COLS, if USER_GENERATED is NO, those columns will not be visible in DBA_TAB_COLUMNS. [[I would have liked to see\u00a0VIRTUAL_COLUMN (HIDDEN_COLUMN too) as\u00a0part of DBA_TAB_COLUMNS, they would have added some useful information to DBA_TAB_COLUMNS.]]<\/em> Virtual column information is included in DBA_TAB_COLUMNS, but does not identify\u00a0if the column is VIRTUAL or not. The invisible columns are marked as HIDDEN_COLUMN=YES. Now, mark x4 for drop and x2 visible.<\/p>\n
SQL> alter table hidden_test set unused (x4);\n\nTable altered.\n\nSQL> alter table hidden_test modify x2 visible;\n\nTable altered.\n\nSQL><\/pre>\n
Query the properties again…<\/p>\n
SQL> select column_name, user_generated, virtual_column, hidden_column, \n            identity_column, column_id, segment_column_id, internal_column_id\n     from dba_tab_cols\n     where table_name = 'HIDDEN_TEST';\n\nCOLUMN_NAM USE VIR HID IDE  COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID\n---------- --- --- --- --- ---------- ----------------- ------------------\nX7         YES NO  NO  NO           4                 5                  7\nX6         YES NO  NO  YES          3                 4                  6\nX5         YES YES YES NO                                                5\nSYS_C00004 NO  NO  YES NO                             3                  4\n_14060322:\n52:06$\n\nX3         YES YES NO  NO           2                                    3\nX2         YES NO  NO  NO           5                 2                  2\nX1         YES NO  NO  NO           1                 1                  1\n\n7 rows selected.<\/pre>\n
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 visilbe in “SELECT *” and “DESCRIBE”. The UNUSED column still maintains the same INTERNAL_COLUMN_ID.<\/p>\n
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.<\/p>\n
What happens to the ID columns, when the UNUSED column is dropped.<\/p>\n
SQL> alter table hidden_test drop unused columns;\n\nTable altered.\n\nSQL> select column_name, user_generated, virtual_column, hidden_column, \n            identity_column, column_id, segment_column_id, internal_column_id\n     from dba_tab_cols\n     where table_name = 'HIDDEN_TEST';\n\nCOLUMN_NAM USE VIR HID IDE  COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID\n---------- --- --- --- --- ---------- ----------------- ------------------\nX7         YES NO  NO  NO           4                 4                  6\nX6         YES NO  NO  YES          3                 3                  5\nX5         YES YES YES NO                                                4\nX3         YES YES NO  NO           2                                    3\nX2         YES NO  NO  NO           5                 2                  2\nX1         YES NO  NO  NO           1                 1                  1\n<\/pre>\n
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.<\/p>\n
Well, I started with just difference between DBA_TAB_COLS and DBA_TAB_COLUMNS, but got into investigating column ids… \ud83d\ude42<\/p>\n
[amazon text=Amazon&template=carousel&asin=111864395X]<\/p>\n","protected":false},"excerpt":{"rendered":"
Not sure how many DBAs have noticed the difference between DBA_TAB_COLUMNS view and DBA_TAB_COLS view. Both views look pretty identical, if you are not paying too much attention. According to Oracle documentation, DBA_TAB_COLS describes the columns of all tables, views, and clusters in the database. This view differs from “DBA_TAB_COLUMNS” in that system-generated hidden columns … 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-421","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\/421","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=421"}],"version-history":[{"count":1,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/421\/revisions"}],"predecessor-version":[{"id":3021,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/421\/revisions\/3021"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=421"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=421"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=421"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}