Note
This view is available starting with V4.0.0.
Purpose
This view displays the column information of tables and views for all tenants. It is consistent with the CDB_TAB_COLS view, but includes an additional NOTES column, which is not currently used.
Columns
Column |
Type |
Nullable |
Description |
||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| CON_ID | bigint(20) | NO | The tenant ID | ||||||||||
| OWNER | varchar(128) | NO | The owner of the table, view, or cluster | ||||||||||
| TABLE_NAME | varchar(128) | NO | The name of the table, view, or cluster | ||||||||||
| COLUMN_NAME | varchar(128) | NO | The column name | ||||||||||
| DATA_TYPE | varchar(128) | NO | The data type of the column | ||||||||||
| DATA_TYPE_MOD | varchar(3) | NO | This column is NULL by default | ||||||||||
| DATA_TYPE_OWNER | varchar(128) | NO | This column is NULL by default | ||||||||||
| DATA_LENGTH | bigint(21) | NO | The length of the column, in bytes | ||||||||||
| DATA_PRECISION | bigint(20) | NO |
|
||||||||||
| DATA_SCALE | bigint(20) | NO | The number of digits to the right of the decimal point | ||||||||||
| NULLABLE | varchar(1) | NO | Indicates whether the column allows NULL values. The value indicates whether the column has a NOT NULL constraint or whether the column is part of the PRIMARY KEY |
||||||||||
| COLUMN_ID | bigint(20) | NO | The sequence number of the created column | ||||||||||
| DEFAULT_LENGTH | bigint(20) | NO | The length of the default value of the column | ||||||||||
| DATA_DEFAULT | mediumtext | NO | The default value of the column | ||||||||||
| NUM_DISTINCT | bigint(20) | NO | The number of distinct values in the column | ||||||||||
| LOW_VALUE | varchar(128) | NO | The minimum value of the column | ||||||||||
| HIGH_VALUE | varchar(128) | NO | The maximum value of the column | ||||||||||
| DENSITY | bigint(21) | NO | The density of the column | ||||||||||
| NUM_NULLS | bigint(20) | NO | The number of NULL values in the column | ||||||||||
| NUM_BUCKETS | bigint(20) | NO | The number of buckets in the column histogram | ||||||||||
| LAST_ANALYZED | date | NO | The date when the column was last analyzed | ||||||||||
| SAMPLE_SIZE | bigint(20) | NO | The sample size used for analyzing the column | ||||||||||
| CHARACTER_SET_NAME | varchar(44) | NO | The character set name: CHAR_CSNCHAR_CS |
||||||||||
| CHAR_COL_DECL_LENGTH | bigint(0) | NO | This column is NULL by default | ||||||||||
| GLOBAL_STATS | varchar(3) | NO | YES if statistics are collected or incrementally maintained, otherwise NO |
||||||||||
| USER_STATS | varchar(3) | NO | Indicates whether the statistics are directly input by the user ( YES) or not ( NO) |
||||||||||
| NOTES | varchar(80) | NO | This column is NULL by default | ||||||||||
| AVG_COL_LEN | bigint(21) | NO | The average length of the column, in bytes | ||||||||||
| CHAR_LENGTH | bigint(20) | NO | Displays the column length as a character. This value applies only to the following data types: CHARVARCHAR2NCHARNVARCHAR2 |
||||||||||
| CHAR_USED | varchar(1) | NO | Indicates whether this column is defined with the BYTE length semantics (B) or the CHAR length semantics (C). If the data type is not one of the following, this value is NULL: CHARVARCHAR2NCHARNVARCHAR2 |
||||||||||
| V80_FMT_IMAGE | varchar(3) | NO | This column defaults to NULL. | ||||||||||
| DATA_UPGRADED | varchar(3) | NO | This field is set to NULL by default. | ||||||||||
| HIDDEN_COLUMN | varchar(3) | NO | Indicates whether the column is hidden (YES) or not (NO). |
||||||||||
| VIRTUAL_COLUMN | varchar(3) | NO | YES indicates that the column is a virtual column, and NO indicates that it is not a virtual column. | ||||||||||
| SEGMENT_COLUMN_ID | bigint(0) | NO | This field is NULL by default. | ||||||||||
| INTERNAL_COLUMN_ID | bigint(0) | NO | This field is NULL by default. | ||||||||||
| HISTOGRAM | varchar(15) | NO | Indicates the histogram type. Valid values: NONEFREQUENCYTOP-FREQUENCYHEIGHT BALANCEDHYBRID |
||||||||||
| QUALIFIED_COL_NAME | text | NO | Qualified column name | ||||||||||
| USER_GENERATED | varchar(3) | NO | Indicates whether this column was generated by the user ( YES) or by the system ( NO). |
||||||||||
| DEFAULT_ON_NULL | varchar(3) | NO | This field defaults to NULL | ||||||||||
| Column | Type | Nullable | Description | --------------------- | ---------------- | ------------ | ---------------------------- | IDENTITY_COLUMN | varchar(3) | NO | This field defaults to NULL. | ||
| EVALUATION_EDITION | varchar(128) | NO | This field defaults to NULL. | ||||||||||
| UNUSABLE_BEFORE | varchar(128) | NO | This column defaults to NULL. | ||||||||||
| UNUSABLE_BEGINNING | varchar(128) | NO | This column is NULL by default. | ||||||||||
| Column | Type | Nullable | Description | --- | --- | --- | --- | COLLATION | varchar(100) | NO | This field is by default NULL. | ||
| COLLATED_COLUMN_ID | bigint(0) | NO | This column defaults to NULL. |
Sample query
In the sys tenant, query the column information of the t1 table in the tenant with ID 1002.
obclient [oceanbase]> SELECT * FROM oceanbase.CDB_TAB_COLS WHERE CON_ID=1002 AND TABLE_NAME='t1'\G
The query result is as follows:
*************************** 1. row ***************************
CON_ID: 1002
OWNER: infotest
TABLE_NAME: t1
COLUMN_NAME: id
DATA_TYPE: NUMBER
DATA_TYPE_MOD: NULL
DATA_TYPE_OWNER: NULL
DATA_LENGTH: 0
DATA_PRECISION: 20
DATA_SCALE: 0
NULLABLE: N
COLUMN_ID: 16
DEFAULT_LENGTH: NULL
DATA_DEFAULT: NULL
NUM_DISTINCT: 3
LOW_VALUE: 1
HIGH_VALUE: 3
DENSITY: 0
NUM_NULLS: 0
NUM_BUCKETS: 0
LAST_ANALYZED: 2025-02-20
SAMPLE_SIZE: 3
CHARACTER_SET_NAME:
CHAR_COL_DECL_LENGTH: NULL
GLOBAL_STATS: NO
USER_STATS: NO
NOTES: NULL
AVG_COL_LEN: 20
CHAR_LENGTH: 0
CHAR_USED: NULL
V80_FMT_IMAGE: NULL
DATA_UPGRADED: NULL
HIDDEN_COLUMN: NO
VIRTUAL_COLUMN: NO
SEGMENT_COLUMN_ID: NULL
INTERNAL_COLUMN_ID: NULL
HISTOGRAM: NULL
QUALIFIED_COL_NAME: id
USER_GENERATED: YES
DEFAULT_ON_NULL: NULL
IDENTITY_COLUMN: NULL
EVALUATION_EDITION: NULL
UNUSABLE_BEFORE: NULL
UNUSABLE_BEGINNING: NULL
COLLATION: NULL
COLLATED_COLUMN_ID: NULL
*************************** 2. row ***************************
CON_ID: 1002
OWNER: infotest
TABLE_NAME: t1
COLUMN_NAME: name
DATA_TYPE: VARCHAR2
DATA_TYPE_MOD: NULL
DATA_TYPE_OWNER: NULL
DATA_LENGTH: 50
DATA_PRECISION: NULL
DATA_SCALE: NULL
NULLABLE: Y
COLUMN_ID: 17
DEFAULT_LENGTH: NULL
DATA_DEFAULT: NULL
NUM_DISTINCT: 3
LOW_VALUE: 'A'
HIGH_VALUE: 'C'
DENSITY: 0
NUM_NULLS: 0
NUM_BUCKETS: 0
LAST_ANALYZED: 2025-02-20
SAMPLE_SIZE: 3
CHARACTER_SET_NAME: CHAR_CS
CHAR_COL_DECL_LENGTH: NULL
GLOBAL_STATS: NO
USER_STATS: NO
NOTES: NULL
AVG_COL_LEN: 13
CHAR_LENGTH: 50
CHAR_USED: B
V80_FMT_IMAGE: NULL
DATA_UPGRADED: NULL
HIDDEN_COLUMN: NO
VIRTUAL_COLUMN: NO
SEGMENT_COLUMN_ID: NULL
INTERNAL_COLUMN_ID: NULL
HISTOGRAM: NULL
QUALIFIED_COL_NAME: name
USER_GENERATED: YES
DEFAULT_ON_NULL: NULL
IDENTITY_COLUMN: NULL
EVALUATION_EDITION: NULL
UNUSABLE_BEFORE: NULL
UNUSABLE_BEGINNING: NULL
COLLATION: NULL
COLLATED_COLUMN_ID: NULL
*************************** 3. row ***************************
CON_ID: 1002
OWNER: infotest
TABLE_NAME: t1
COLUMN_NAME: gmt_create
DATA_TYPE: TIMESTAMP
DATA_TYPE_MOD: NULL
DATA_TYPE_OWNER: NULL
DATA_LENGTH: 0
DATA_PRECISION: NULL
DATA_SCALE: 0
NULLABLE: N
COLUMN_ID: 18
DEFAULT_LENGTH: 17
DATA_DEFAULT: CURRENT_TIMESTAMP
NUM_DISTINCT: 1
LOW_VALUE: '2025-02-20 14:37:07'
HIGH_VALUE: '2025-02-20 14:37:07'
DENSITY: 0
NUM_NULLS: 0
NUM_BUCKETS: 0
LAST_ANALYZED: 2025-02-20
SAMPLE_SIZE: 3
CHARACTER_SET_NAME:
CHAR_COL_DECL_LENGTH: NULL
GLOBAL_STATS: NO
USER_STATS: NO
NOTES: NULL
AVG_COL_LEN: 20
CHAR_LENGTH: 0
CHAR_USED: NULL
V80_FMT_IMAGE: NULL
DATA_UPGRADED: NULL
HIDDEN_COLUMN: NO
VIRTUAL_COLUMN: NO
SEGMENT_COLUMN_ID: NULL
INTERNAL_COLUMN_ID: NULL
HISTOGRAM: NULL
QUALIFIED_COL_NAME: gmt_create
USER_GENERATED: YES
DEFAULT_ON_NULL: NULL
IDENTITY_COLUMN: NULL
EVALUATION_EDITION: NULL
UNUSABLE_BEFORE: NULL
UNUSABLE_BEGINNING: NULL
COLLATION: NULL
COLLATED_COLUMN_ID: NULL
3 rows in set