DBA_TAB_COLUMNS vs DBA_TAB_COLS

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 and invisible columns, which are user-generated hidden columns, are not filtered out.

Let me dig a little deep to understand the differences better. First, how the views are built…

Until 11gR2, DBA_TAB_COLS view is a complex SQL joining multiple internal dictionary tables. The FROM clause is shown here…

The FROM clause of DBA_TAB_COLS view in 10gR2 is…

The FROM clause of DBA_TAB_COLS view in 11gR2 is…

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$ 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.

So, in 12c, the DBA_TAB_COLS view is a straight SELECT of columns from DBA_TAB_COLS_V$ without any WHERE clause.

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’ and in 12c the WHERE clause changed to where USER_GENERATED = ‘YES’.

Full SQL used for the DBA_TAB_COLUMNS view in 12c is below.

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.

USER_GENERATED column is new in 12c, all other columns are same in 11gR2/10gR2 as well.

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?

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.

Query the column properties of the table just created.

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 VIRTUAL_COLUMN (HIDDEN_COLUMN too) as part of DBA_TAB_COLUMNS, they would have added some useful information to DBA_TAB_COLUMNS.]] Virtual column information is included in DBA_TAB_COLUMNS, but does not identify if the column is VIRTUAL or not. The invisible columns are marked as HIDDEN_COLUMN=YES. Now, mark x4 for drop and x2 visible.

Query the properties again…

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.

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.

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.

Well, I started with just difference between DBA_TAB_COLS and DBA_TAB_COLUMNS, but got into investigating column ids… 🙂

2 Responses to DBA_TAB_COLUMNS vs DBA_TAB_COLS

  1. Mohamed Seliem

    This query is wrong. you are minus dba_tab_cols from dba_tab_cols!!

    SQL> select column_name from dba_tab_cols
    where table_name = ‘DBA_TAB_COLS’
    minus
    select column_name from dba_tab_cols
    where table_name = ‘DBA_TAB_COLUMNS’;

    • Mohamed, that query looks just fine to me. How else would you write it?

      Biju, Thank you for the article! Very well explained.

Leave a Reply

%d bloggers like this: