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;