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.
|HIDDEN_COLUMN||VARCHAR2(3)||YES||Indicates whether the column is a hidden column (YES) or not (NO)|
|VIRTUAL_COLUMN||VARCHAR2(3)||YES||Indicates whether the column is a virtual column (YES) or not (NO)|
|SEGMENT_COLUMN_ID||NUMBER||YES||Sequence number of the column in the segment|
|INTERNAL_COLUMN_ID||NUMBER||NO||Internal sequence number of the column|
|QUALIFIED_COL_NAME||VARCHAR2(4000)||YES||Qualified column name|
|USER_GENERATED||VARCHAR2(3)||NO||Indicates whether the column is a user generated column (YES) or not (NO)|
|COLLATED_COLUMN_ID||NUMBER||YES||column 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: