DBA_TAB_COLUMNS

Columns of user's tables, views and clusters

Name Datatype Length Mandatory Comments
OWNER VARCHAR2 (30) Yes
TABLE_NAME VARCHAR2 (30) Yes Table, view or cluster name
COLUMN_NAME VARCHAR2 (30) Yes Column name
DATA_TYPE VARCHAR2 (106)
Datatype of the column
DATA_TYPE_MOD VARCHAR2 (3)
Datatype modifier of the column
DATA_TYPE_OWNER VARCHAR2 (30)
Owner of the datatype of the column
DATA_LENGTH NUMBER
Yes Length of the column in bytes
DATA_PRECISION NUMBER

Length: decimal digits (NUMBER) or binary digits (FLOAT)
DATA_SCALE NUMBER

Digits to right of decimal point in a number
NULLABLE VARCHAR2 (1)
Does column allow NULL values?
COLUMN_ID NUMBER

Sequence number of the column as created
DEFAULT_LENGTH NUMBER

Length of default value for the column
DATA_DEFAULT LONG (0)
Default value for the column
NUM_DISTINCT NUMBER

The number of distinct values in the column
LOW_VALUE RAW (32)
The low value in the column
HIGH_VALUE RAW (32)
The high value in the column
DENSITY NUMBER

The density of the column
NUM_NULLS NUMBER

The number of nulls in the column
NUM_BUCKETS NUMBER

The number of buckets in histogram for the column
LAST_ANALYZED DATE

The date of the most recent time this column was analyzed
SAMPLE_SIZE NUMBER

The sample size used in analyzing this column
CHARACTER_SET_NAME VARCHAR2 (44)
Character set name
CHAR_COL_DECL_LENGTH NUMBER

Declaration length of character type column
GLOBAL_STATS VARCHAR2 (3)
Are the statistics calculated without merging underlying partitions?
USER_STATS VARCHAR2 (3)
Were the statistics entered directly by the user?
AVG_COL_LEN NUMBER

The average length of the column in bytes
CHAR_LENGTH NUMBER

The maximum length of the column in characters
CHAR_USED VARCHAR2 (1)
C if the width was specified in characters, B if in bytes
V80_FMT_IMAGE VARCHAR2 (3)
Is column data in 8.0 image format?
DATA_UPGRADED VARCHAR2 (3)
Has column data been upgraded to the latest type version format?

Usage:


  cursor cur_columns(c_name dba_tab_columns.table_name%type
                   , c_owner dba_tab_columns.owner%type)
  is select col.column_name
     ,      col.data_type
     ,      col.data_length
     ,      nvl(to_char(col.data_precision), '
') data_precision , nvl(decode(col.nullable, 'N', 'Yes'), '
') nullable from dba_tab_columns col where col.table_name = c_name and col.owner = c_owner order by col.column_id;