Note
This view is available starting with V2.2.30.
Purpose
This view displays all columns of all tables and views in the current tenant.
Applicability
This view is available only in Oracle-compatible mode of OceanBase Database.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| OWNER | VARCHAR2(128) | NO | The owner of the table or view. |
| TABLE_NAME | VARCHAR2(128) | NO | The name of the table or view. |
| COLUMN_NAME | VARCHAR2(128) | NO | The column name. |
| DATA_TYPE | VARCHAR2(128) | NO | The column type. |
| DATA_TYPE_MOD | VARCHAR2(3) | NO | This column is not supported. The default value is NULL. |
| DATA_TYPE_OWNER | VARCHAR2(128) | NO | This column is not supported. The default value is NULL. |
| DATA_LENGTH | NUMBER | NO | The length of the column, in bytes. |
| DATA_PRECISION | NUMBER | NO | The decimal precision of the numeric data type; the binary precision of the floating-point data type; NULL for all other data types. |
| DATA_SCALE | NUMBER | NO | The number of digits to the right of the decimal point. |
| NULLABLE | VARCHAR2(1) | NO | Indicates whether the column allows NULL values. |
| COLUMN_ID | NUMBER | NO | The column number. |
| DEFAULT_LENGTH | NUMBER | NO | The default length of the column. |
| DATA_DEFAULT | VARCHAR2(128) | NO | The default value of the column. |
| NUM_DISTINCT | NUMBER | NO | The number of distinct values in the column. |
| LOW_VALUE | VARCHAR2(128) | NO | The minimum value of the column. |
| HIGH_VALUE | VARCHAR2(128) | NO | The maximum value of the column. |
| DENSITY | NUMBER | NO | The density of the column. |
| NUM_NULLS | NUMBER | NO | The number of NULL values in the column. |
| NUM_BUCKETS | NUMBER | NO | The number of buckets in the column histogram. |
| LAST_ANALYZED | DATE | NO | The last time the column was analyzed. |
| SAMPLE_SIZE | NUMBER | NO | The sample size when the column was analyzed. |
| CHARACTER_SET_NAME | VARCHAR2(44) | NO | The character set name:
|
| CHAR_COL_DECL_LENGTH | NUMBER | NO | This column is not supported. The default value is NULL. |
| GLOBAL_STATS | VARCHAR2(3) | NO | YES if statistics were collected or incrementally maintained, otherwise NO. |
| USER_STATS | VARCHAR2(3) | NO | Indicates whether the statistics were directly input by the user ( YES) or not ( NO). |
| AVG_COL_LEN | NUMBER | NO | The average length of the column, in bytes. |
| CHAR_LENGTH | NUMBER | NO | The length of the column of the character type. |
| CHAR_USED | VARCHAR2(1) | NO | Indicates whether the column is based on byte length or character length. |
| V80_FMT_IMAGE | VARCHAR2(3) | NO | This column is not supported. The default value is NULL. |
| DATA_UPGRADED | VARCHAR2(3) | NO | This column is not supported. The default value is NULL. |
| HISTOGRAM | VARCHAR2(15) | NO | The histogram type. |
| DEFAULT_ON_NULL | VARCHAR2(3) | NO | This column is not supported. The default value is NULL |
| IDENTITY_COLUMN | VARCHAR2(3) | NO | This column is not supported. The default value is NULL |
| EVALUATION_EDITION | VARCHAR2(128) | NO | This column is not supported. The default value is NULL |
| UNUSABLE_BEFORE | VARCHAR2(128) | NO | This column is not supported. The default value is NULL |
| UNUSABLE_BEGINNING | VARCHAR2(128) | NO | This column is not supported and is set to NULL by default. |
| COLLATION | VARCHAR2(100) | NO | This column is not supported and is set to NULL by default. |
Sample query
Query all columns of the T_SUBPART table in the current tenant.
obclient [SYS]> SELECT * FROM SYS.DBA_TAB_COLUMNS WHERE TABLE_NAME ='T_SUBPART'\G
The query result is as follows:
*************************** 1. row ***************************
OWNER: SYS
TABLE_NAME: T_SUBPART
COLUMN_NAME: NO
DATA_TYPE: NUMBER
DATA_TYPE_MOD: NULL
DATA_TYPE_OWNER: NULL
DATA_LENGTH: 22
DATA_PRECISION: NULL
DATA_SCALE: NULL
NULLABLE: Y
COLUMN_ID: 16
DEFAULT_LENGTH: NULL
DATA_DEFAULT: NULL
NUM_DISTINCT: 1034
LOW_VALUE: 2
HIGH_VALUE: 1001
DENSITY: 0
NUM_NULLS: 0
NUM_BUCKETS: 0
LAST_ANALYZED: 25-MAR-25
SAMPLE_SIZE: 1000
CHARACTER_SET_NAME: NULL
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
HISTOGRAM: NULL
DEFAULT_ON_NULL: NULL
IDENTITY_COLUMN: NULL
EVALUATION_EDITION: NULL
UNUSABLE_BEFORE: NULL
UNUSABLE_BEGINNING: NULL
COLLATION: NULL
*************************** 2. row ***************************
OWNER: SYS
TABLE_NAME: T_SUBPART
COLUMN_NAME: ID
DATA_TYPE: NUMBER
DATA_TYPE_MOD: NULL
DATA_TYPE_OWNER: NULL
DATA_LENGTH: 22
DATA_PRECISION: NULL
DATA_SCALE: NULL
NULLABLE: Y
COLUMN_ID: 17
DEFAULT_LENGTH: NULL
DATA_DEFAULT: NULL
NUM_DISTINCT: 10
LOW_VALUE: 1
HIGH_VALUE: 999
DENSITY: 0
NUM_NULLS: 0
NUM_BUCKETS: 0
LAST_ANALYZED: 25-MAR-25
SAMPLE_SIZE: 1000
CHARACTER_SET_NAME: NULL
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
HISTOGRAM: NULL
DEFAULT_ON_NULL: NULL
IDENTITY_COLUMN: NULL
EVALUATION_EDITION: NULL
UNUSABLE_BEFORE: NULL
UNUSABLE_BEGINNING: NULL
COLLATION: NULL
*************************** 3. row ***************************
OWNER: SYS
TABLE_NAME: T_SUBPART
COLUMN_NAME: TEST_DATE
DATA_TYPE: DATE
DATA_TYPE_MOD: NULL
DATA_TYPE_OWNER: NULL
DATA_LENGTH: 7
DATA_PRECISION: NULL
DATA_SCALE: NULL
NULLABLE: Y
COLUMN_ID: 18
DEFAULT_LENGTH: NULL
DATA_DEFAULT: NULL
NUM_DISTINCT: 3
LOW_VALUE: '2022-12-15 00:00:00'
HIGH_VALUE: '2024-01-10 00:00:00'
DENSITY: 0
NUM_NULLS: 0
NUM_BUCKETS: 0
LAST_ANALYZED: 25-MAR-25
SAMPLE_SIZE: 1000
CHARACTER_SET_NAME: NULL
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
HISTOGRAM: NULL
DEFAULT_ON_NULL: NULL
IDENTITY_COLUMN: NULL
EVALUATION_EDITION: NULL
UNUSABLE_BEFORE: NULL
UNUSABLE_BEGINNING: NULL
COLLATION: NULL
*************************** 4. row ***************************
OWNER: SYS
TABLE_NAME: T_SUBPART
COLUMN_NAME: QUANTITY
DATA_TYPE: NUMBER
DATA_TYPE_MOD: NULL
DATA_TYPE_OWNER: NULL
DATA_LENGTH: 22
DATA_PRECISION: NULL
DATA_SCALE: NULL
NULLABLE: Y
COLUMN_ID: 19
DEFAULT_LENGTH: NULL
DATA_DEFAULT: NULL
NUM_DISTINCT: 2
LOW_VALUE: 1
HIGH_VALUE: 1000
DENSITY: 0
NUM_NULLS: 0
NUM_BUCKETS: 0
LAST_ANALYZED: 25-MAR-25
SAMPLE_SIZE: 1000
CHARACTER_SET_NAME: NULL
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
HISTOGRAM: NULL
DEFAULT_ON_NULL: NULL
IDENTITY_COLUMN: NULL
EVALUATION_EDITION: NULL
UNUSABLE_BEFORE: NULL
UNUSABLE_BEGINNING: NULL
COLLATION: NULL
4 rows in set
References
Query the columns of all tables and views that are accessible to the current user: ALL_TAB_COLUMNS
Query the columns of all tables and views that are owned by the current user: USER_TAB_COLUMNS