Note
This view is available starting with V2.2.30.
Purpose
This view displays all columns of tables and views that the current user owns.
Applicability
This view is available only in Oracle-compatible mode of OceanBase Database.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| TABLE_NAME | VARCHAR2(128) | NO | The name of the table or view. |
| COLUMN_NAME | VARCHAR2(128) | NO | The name of the column. |
| DATA_TYPE | VARCHAR2(128) | NO | The data type of the column. |
| DATA_TYPE_MOD | VARCHAR2(3) | NO | This column is not supported. It is set to NULL by default. |
| DATA_TYPE_OWNER | VARCHAR2(128) | NO | This column is not supported. It is set to NULL by default. |
| DATA_LENGTH | NUMBER | NO | The length of the column, in bytes. |
| DATA_PRECISION | NUMBER | NO | The decimal precision of numeric data types; the binary precision of floating-point data types; and 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 time when the column was last analyzed. |
| SAMPLE_SIZE | NUMBER | NO | The sample size used 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. It is set to NULL by default. |
| GLOBAL_STATS | VARCHAR2(3) | NO | YES if statistics are collected or incrementally maintained, otherwise NO. |
| USER_STATS | VARCHAR2(3) | NO | Indicates whether the statistics are directly entered 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 for character data types. |
| CHAR_USED | VARCHAR2(1) | NO | Indicates whether the column is measured by byte length or character length. |
| V80_FMT_IMAGE | VARCHAR2(3) | NO | This column is not supported. It is set to NULL by default. |
| DATA_UPGRADED | VARCHAR2(3) | NO | This column is not supported. It is set to NULL by default. |
| HISTOGRAM | VARCHAR2(15) | NO | The histogram type. |
| DEFAULT_ON_NULL | VARCHAR2(3) | NO | This column is not supported. It is set to NULL by default. |
| IDENTITY_COLUMN | VARCHAR2(3) | NO | This column is not supported. It is set to NULL by default. |
| EVALUATION_EDITION | VARCHAR2(128) | NO | This column is not supported. It is set to NULL by default. |
| UNUSABLE_BEFORE | VARCHAR2(128) | NO | This column is not supported. It is set to NULL by default. |
| UNUSABLE_BEGINNING | VARCHAR2(128) | NO | This column is not supported. It is set to NULL by default. |
| COLLATION | VARCHAR2(100) | NO | This column is not supported. The default value is NULL. |
Sample query
Query all columns of the T_SUBPART table in the tables owned by the current user.
obclient [SYS]> SELECT * FROM SYS.USER_TAB_COLUMNS WHERE TABLE_NAME ='T_SUBPART'\G
The query result is as follows:
*************************** 1. row ***************************
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 ***************************
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 ***************************
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 ***************************
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 all columns of all tables or views accessible to the current user: ALL_TAB_COLUMNS
Query all columns of all tables or views in the current tenant: DBA_TAB_COLUMNS