DIFFERENCE BETWEEN ALL_TAB_COLS AND ALL_TAB_COLUMNS IN ORACLE

ALL_TAB_COLUMNS and ALL_TAB_COLS are the views in Oracle that describes the columns of the tables, views, and clusters accessible to the current user. The only difference between two views is, ALL_TAB_COLS won’t filter hidden columns where as ALL_TAB_COLUMNS filters the hidden columns.

Please note that invisible and hidden columns both are different. ALL_TAB_COLUMNS and ALL_TAB_COLS both shows invisible columns. I will show this by creating a test table with both invisible and hidden columns.

Even if you do ‘DESC ALL_TAB_COLUMNS’ and ‘DESC ALL_TAB_COLS’, you can observer there are extra 7 columns are present ALL_TAB_COLS oracle view.

Below are the details of extra columns that we have in ALL_TAB_COLS  view.

ColumnDatatypeNULLABLE?Description
HIDDEN_COLUMNVARCHAR2(3)YESIndicates whether the column is a hidden column (YES) or not (NO)
VIRTUAL_COLUMNVARCHAR2(3)YESIndicates whether the column is a virtual column (YES) or not (NO)
SEGMENT_COLUMN_IDNUMBERYESSequence number of the column in the segment
INTERNAL_COLUMN_IDNUMBERNOInternal sequence number of the column
QUALIFIED_COL_NAMEVARCHAR2(4000)YESQualified column name
USER_GENERATEDVARCHAR2(3)NOIndicates whether the column is a user generated column (YES) or not (NO)
COLLATED_COLUMN_IDNUMBERYEScolumn id of that column

Below is the ‘ALL_TAB_TEST’ table which has three columns and one index. ‘BAL’ is the invisible column here. Oracle internally created a hidden column for ‘FN_INDX_TEST’ index, but we can’t see this column unless you use ALL_TAB_COLS.

CREATE TABLE ALL_TAB_TEST
( 
TYPE VARCHAR2(10) NOT NULL ENABLE, 
ACC_NO NUMBER(10) NOT NULL ENABLE,
BAL NUMBER INVISIBLE 
);

CREATE INDEX FN_INDX_TEST on ALL_TAB_TEST(UPPER(TYPE));

After creating above table, now we will select the records from system views for the able table.

SELECT table_name,column_name,data_type,column_id,hidden_column,virtual_column,user_generated FROM all_tab_cols WHERE table_name=’ALL_TAB_TEST’;

Actually we have only 3 columns in the table, but here we are seeing one more column extra. As shown above, it is a hidden column and user won’t be aware of this at all.

SELECT table_name, column_name, data_type, column_id FROM all_tab_columns WHERE table_name=”ALL_TAB_TEST’;

This is not showing hidden column but showing all other three column including invisible columns.

You can watch video which shows above table execution in SQL Developer:

Comments

comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: