DBA_TAB_COLUMNS

DBA_TAB_COLUMNS describes the columns of all tables, views, and clusters in the database.

Its columns (except for SENSITIVE_COLUMN) are the same as those in ALL_TAB_COLUMNS.

To gather statistics for this view, use the DBMS_STATS package.

This view filters out system-generated hidden columns. The DBA_TAB_COLS view does not filter out system-generated hidden columns.

Columns marked with an asterisk (*) in the table below remain for backward compatibility with Oracle7. This information is now in the [TAB|PART]_COL_STATISTICS views.

Column Datatype NULL Description
OWNER VARCHAR2(128) NOT NULL Owner of the table, view, or cluster
TABLE_NAME VARCHAR2(128) NOT NULL Name of the table, view, or cluster
COLUMN_NAME VARCHAR2(128) NOT NULL Column name
DATA_TYPE VARCHAR2(128)   Data type of the column
DATA_TYPE_MOD VARCHAR2(3)   Data type modifier of the column
DATA_TYPE_OWNER VARCHAR2(128)   Owner of the data type of the column
DATA_LENGTH NUMBER NOT NULL Length of the column (in bytes)
DATA_PRECISION NUMBER   Decimal precision for NUMBER data type; binary precision for FLOAT data type; NULL for all other data types
DATA_SCALE NUMBER   Digits to the right of the decimal point in a number
NULLABLE VARCHAR2(1)   Indicates whether a column allows NULLs. The value is N if there is a NOT NULL constraint on the column or if the column is part of a PRIMARY KEY. The constraint should be in an ENABLE VALIDATE state.
COLUMN_ID NUMBER   Sequence number of the column as created
DEFAULT_LENGTH NUMBER   Length of the default value for the column
DATA_DEFAULT LONG   Default value for the column
DATA_DEFAULT_VC VARCHAR2(4000)  

Default value for the column

This column may truncate the default value.
NUM_DISTINCT* NUMBER   Number of distinct values in the column
LOW_VALUE* RAW(1000)   Low value in the column
HIGH_VALUE* RAW(1000)   High value in the column
DENSITY* NUMBER  

If a histogram is available on COLUMN_NAME, then this column displays the selectivity of a value that spans fewer than 2 endpoints in the histogram. It does not represent the selectivity of values that span 2 or more endpoints.

If a histogram is not available on COLUMN_NAME, then the value of this column is 1/NUM_DISTINCT.

NUM_NULLS NUMBER   Number of NULLs in the column
NUM_BUCKETS NUMBER  

Number of buckets in the histogram for the column

Note: The number of buckets in a histogram is specified in the SIZE parameter of the ANALYZE SQL statement. However, Oracle AI Database does not create a histogram with more buckets than the number of rows in the sample. Also, if the sample contains any values that are very repetitious, Oracle AI Database creates the specified number of buckets, but the value indicated by this column may be smaller because of an internal compression algorithm.

LAST_ANALYZED DATE   Date on which this column was most recently analyzed
SAMPLE_SIZE NUMBER   Sample size used in analyzing this column
CHARACTER_SET_NAME VARCHAR2(44)  

Name of the character set:

  • CHAR_CS

  • NCHAR_CS
CHAR_COL_DECL_LENGTH NUMBER   Declaration length of the character type column
GLOBAL_STATS VARCHAR2(3)   GLOBAL_STATS will be YES if statistics are gathered or incrementally maintained, otherwise it will be NO
USER_STATS VARCHAR2(3)   Indicates whether statistics were entered directly by the user (YES) or not (NO)
AVG_COL_LEN NUMBER   Average length of the column (in bytes)
CHAR_LENGTH NUMBER  

Displays the length of the column in characters. This value only applies to the following data types:

  • CHAR

  • VARCHAR2

  • NCHAR

  • NVARCHAR2
CHAR_USED VARCHAR2(1)  

Indicates that the column uses BYTE length semantics (B) or CHAR length semantics (C), or whether the data type is not any of the following (NULL):

  • CHAR

  • VARCHAR2

  • NCHAR

  • NVARCHAR2
V80_FMT_IMAGE VARCHAR2(3)   Indicates whether the column data is in release 8.0 image format (YES) or not (NO)
DATA_UPGRADED VARCHAR2(3)   Indicates whether the column data has been upgraded to the latest type version format (YES) or not (NO)
HISTOGRAM VARCHAR2(15)  

Indicates existence/type of histogram:

  • NONE

  • FREQUENCY

  • TOP-FREQUENCY

  • HEIGHT BALANCED

  • HYBRID
DEFAULT_ON_NULL VARCHAR2(3)   Indicates whether the column has DEFAULT ON NULL for INSERT semantics (YES) or not (NO)
DEFAULT_ON_NULL_UPD VARCHAR2(3)   Indicates whether the column has DEFAULT ON NULL for UPDATE semantics (YES) or not (NO)
RESERVABLE_COLUMN VARCHAR2(3)   Indicates whether the column is a reservable column (YES) or not (NO)
IDENTITY_COLUMN VARCHAR2(3)   Indicates whether this is an identity column (YES) or not (NO)
SENSITIVE_COLUMN VARCHAR2(3)   Indicates whether this is a sensitive column (YES) or not (NO)
EVALUATION_EDITION VARCHAR2(128)   Name of the edition in which editioned objects referenced in an expression column are resolved
UNUSABLE_BEFORE VARCHAR2(128)   Name of the oldest edition in which the index may be used as part of a query plan
UNUSABLE_BEGINNING VARCHAR2(128)   Name of the edition for which the index may not be used as part of a query plan in this edition or any of its descendants
COLLATION VARCHAR2(100)   Collation for the column. Only applies to columns with character data types.
DOMAIN_OWNER VARCHAR2(128)   If a data use case domain is specified for the column, the owner of the data use case domain
DOMAIN_NAME VARCHAR2(128)   If a data use case domain is specified for the column, the name of the data use case domain
DOMAIN_COLUMN_NAME VARCHAR2(128)   If a data use case domain is specified for the column, the name of the data use case domain column
DOMAIN_ASSOCIATION_ID NUMBER   If a data use case domain is specified for the column, the INTERNAL_COLUMN_ID of the first column associated with the domain
JSON_MODIFIER VARCHAR2(448)  

JSON-type modifier for the column

A value is displayed only for columns of data type JSON.

If one or more JSON-type modifiers are specified for the column, then the following is displayed:

JSON( modifier [, modifier]... )

Possible values for modifier:

  • ARRAY - JSON array

  • OBJECT - JSON object

  • SCALAR - JSON scalar

  • data_type - JSON scalar of type data_type

If a JSON-type modifier is not specified for the column, then the following is displayed:

JSON( )
VECTOR_INFO VARCHAR2(56)  

Indicates the number of vectors and their data type in an ONNX model

Format: VECTOR(dimension,element_type)

Example: VECTOR(768,float32)

See Also: