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…
--10gR2
SQL> set long 32000
SQL> select text from dba_views
where view_name = 'DBA_TAB_COLS';
from sys.col$ c, sys.obj$ o, sys.hist_head$ h, sys.user$ u,
sys.coltype$ ac, sys.obj$ ot, sys.user$ ut
The FROM clause of DBA_TAB_COLS view in 11gR2 is…
--11gR2
SQL> select text from dba_views
where view_name = 'DBA_TAB_COLS';
from sys.col$ c, sys."_CURRENT_EDITION_OBJ" o, sys.hist_head$ h,
sys.user$ u, sys.coltype$ ac, sys.obj$ ot,
sys."_BASE_USER" ut, sys.tab$ t
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.
--12cR1 SQL> set long 32000 SQL> select text from dba_views where view_name = 'DBA_TAB_COLUMNS'; TEXT ---------------------------------------------------------------------- select OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID, DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE, CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH, GLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED, V80_FMT_IMAGE, DATA_UPGRADED, HISTOGRAM, DEFAULT_ON_NULL, IDENTITY_COLUMN, SENSITIVE_COLUMN, EVALUATION_EDITION, UNUSABLE_BEFORE, UNUSABLE_BEGINNING from DBA_TAB_COLS where USER_GENERATED = 'YES'
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.
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';
COLUMN_NAME
-----------------------------
HIDDEN_COLUMN
INTERNAL_COLUMN_ID
QUALIFIED_COL_NAME
SEGMENT_COLUMN_ID
USER_GENERATED
VIRTUAL_COLUMN
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.
SQL> create table hidden_test (
x1 number,
x2 number invisible,
x3 number generated always as (x1/2) virtual,
x4 number,
x5 number invisible generated always as (x1/4) virtual,
x6 number generated always as identity,
x7 number );
Table created.
Query the column properties of the table just created.
SQL> col column_name format a5
SQL> select column_name, user_generated, virtual_column, hidden_column,
identity_column, column_id, segment_column_id, internal_column_id
from dba_tab_cols
where table_name = 'HIDDEN_TEST';
COLUM USE VIR HID IDE COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
----- --- --- --- --- ---------- ----------------- ------------------
X7 YES NO NO NO 5 5 7
X6 YES NO NO YES 4 4 6
X5 YES YES YES NO 5
X4 YES NO NO NO 3 3 4
X3 YES YES NO NO 2 3
X2 YES NO YES NO 2 2
X1 YES NO NO NO 1 1 1
7 rows selected.
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.
SQL> alter table hidden_test set unused (x4); Table altered. SQL> alter table hidden_test modify x2 visible; Table altered. SQL>
Query the properties again…
SQL> select column_name, user_generated, virtual_column, hidden_column,
identity_column, column_id, segment_column_id, internal_column_id
from dba_tab_cols
where table_name = 'HIDDEN_TEST';
COLUMN_NAM USE VIR HID IDE COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
---------- --- --- --- --- ---------- ----------------- ------------------
X7 YES NO NO NO 4 5 7
X6 YES NO NO YES 3 4 6
X5 YES YES YES NO 5
SYS_C00004 NO NO YES NO 3 4
_14060322:
52:06$
X3 YES YES NO NO 2 3
X2 YES NO NO NO 5 2 2
X1 YES NO NO NO 1 1 1
7 rows selected.
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.
SQL> alter table hidden_test drop unused columns;
Table altered.
SQL> select column_name, user_generated, virtual_column, hidden_column,
identity_column, column_id, segment_column_id, internal_column_id
from dba_tab_cols
where table_name = 'HIDDEN_TEST';
COLUMN_NAM USE VIR HID IDE COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
---------- --- --- --- --- ---------- ----------------- ------------------
X7 YES NO NO NO 4 4 6
X6 YES NO NO YES 3 3 5
X5 YES YES YES NO 4
X3 YES YES NO NO 2 3
X2 YES NO NO NO 5 2 2
X1 YES NO NO NO 1 1 1
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… 🙂
[amazon text=Amazon&template=carousel&asin=111864395X]