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.
| Column | Datatype | NULLABLE? | Description |
|---|---|---|---|
| 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:
