Columns of and differences between DBA_TAB_COLUMNS and DBA_TAB_COLS
dba_tab_columns is related to dba_tab_cols, but shows only columns that are not system generated, i.e the definition of the view dba_tab_columns excludes user generated columns:
create view DBA_TAB_COLUMNS as
select
…
from
DBA_TAB_COLS
where
USER_GENERATED = 'YES';
with
c1 as (select * /* column_name, data_type, data_length, data_precision */ from dba_tab_cols where owner = 'SYS' and table_name = 'DBA_TAB_COLS' ),
c2 as (select * /* column_name, data_type, data_length, data_precision */ from dba_tab_columns where owner = 'SYS' and table_name = 'DBA_TAB_COLUMNS')
select
nvl(c1.column_name, c2.column_name) column_name,
nvl(case when c1.column_name is null and c2.column_name is not null then 'only in dba_tab_columns' end,
case when c2.column_name is null and c1.column_name is not null then 'only in dba_tab_cols' end
) where_
-- c1.data_type , c2.data_type,
-- c1.column_id , c2.column_id
from
c1 full outer join
c2 on c1.column_name = c2.column_name
order by
c1.column_id,
c2.column_id;
Number of digits to the right of the decimal point.
NULLABLE
COLUMN_ID
DEFAULT_LENGTH
DATA_DEFAULT
NUM_DISTINCT
LOW_VALUE, HIGH_VALUE
The lowest/highest value in the column found when the column statistics were last gathered. Use on of the dbms_stats.convert_raw_to_<datatype> functions to convert the raw value stored in these column to more readable values.
DENSITY
NUM_NULLS
NUM_BUCKETS
LAST_ANALYZED
SAMPLE_SIZE
CHARACTER_SET_NAME
The name of the character set: either CHAR_CS or NCHAR_CS
CHAR_COL_DECL_LENGTH
GLOBAL_STATS
YES indicates that optimizer statistics are gathered or incrementally maintained. If this is not the case, the value is NO.
USER_STATS
YES indicates that optimizer statistics were provided by a user. NO indicates they were gathered by Oracle.
AVG_COL_LEN
The column's avarage amount of bytes used per record
CHAR_LENGTH
For datatypes char, nchar, varchar2 and nvarchar2, the value of this colum corresponds to the (maximal) length of the values. See also CHAR_USED
CHAR_USED
For datatypes char, nchar, varchar2 and nvarchar2, the value C indicates char length semantics, B byte length semantcis. See also CHAR_LENGTH
If and what type of histogram statistics the optimizer stores for the column. Possible values are: NONE, FREQUENCY, TOP-FREQUENCY, HEIGHT BALANCED and HYBRID.