Note
This view is available starting with V4.0.0.
Purpose
This view displays column information for tables and views of all tenants. It is consistent with the CDB_TAB_COLS_V$ view, but without the 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 | The decimal precision of the NUMBER data type; the binary precision of the FLOAT data type; NULL for all other data types |
| 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 a 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 time when the column was last analyzed |
| SAMPLE_SIZE | bigint(20) | NO | The sampling size when the column was analyzed |
| 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 a user ( YES) or not ( NO) |
| AVG_COL_LEN | bigint(21) | NO | The average length of the column, in bytes |
| CHAR_LENGTH | bigint(20) | NO | The length of the column, displayed in characters. This value applies only to the following data types: CHARVARCHAR2NCHARNVARCHAR2 |
| CHAR_USED | varchar(1) | NO | Indicates whether the column uses the BYTE length semantics (B) or the CHAR length semantics (C). NULL indicates that the column is not of the following data types: CHARVARCHAR2NCHARNVARCHAR2 |
| V80_FMT_IMAGE | varchar(3) | NO | This column defaults to NULL. |
| DATA_UPGRADED | varchar(3) | NO | This field is NULL by default. |
| HIDDEN_COLUMN | varchar(3) | NO | Indicates whether the column is hidden ( YES) or not ( NO) |
| VIRTUAL_COLUMN | varchar(3) | NO | Indicates whether the column is a virtual column (YES) or not (NO). |
| SEGMENT_COLUMN_ID | bigint(0) | NO | This column defaults to NULL. |
| INTERNAL_COLUMN_ID | This column is NULL by default. | ||
| HISTOGRAM | varchar(15) | NO | Indicates the type of the histogram: NONEFREQUENCYTOP-FREQUENCYHEIGHT BALANCEDHYBRID |
| QUALIFIED_COL_NAME | text | NO | The qualified name of a column. |
| USER_GENERATED | varchar(3) | NO | Indicates whether the column is user-generated (YES) or system-generated (NO). |
| DEFAULT_ON_NULL | varchar(3) | NO | This column defaults to NULL. |
| IDENTITY_COLUMN | varchar(3) | NO | The value is null by default. |
| EVALUATION_EDITION | varchar(128) | NO | The value is NULL by default. |
| UNUSABLE_BEFORE | varchar(128) | NO | This field is default NULL. |
| UNUSABLE_BEGINNING | varchar(128) | NO | This field defaults to NULL. |
| COLLATION | varchar(100) | NO | The default value is NULL. |
| COLLATED_COLUMN_ID | bigint(0) | NO | NULL by default |
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
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
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
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