Note
This view is available starting with V2.2.30.
Purpose
This view displays all columns of tables and views accessible to the current user.
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. It is NULL by default. |
| DATA_TYPE_OWNER | VARCHAR2(128) | NO | This column is not supported. It is NULL by default. |
| DATA_LENGTH | NUMBER | NO | The column length, in bytes. |
| DATA_PRECISION | NUMBER | NO | The decimal precision of numeric data types; the binary precision of floating-point data types; 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 in the column. |
| HIGH_VALUE | VARCHAR2(128) | NO | The maximum value in the column. |
| DENSITY | NUMBER | NO | The column density. |
| 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 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 NULL by default. |
| 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 a character-based column. |
| 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. It is NULL by default. |
| DATA_UPGRADED | VARCHAR2(3) | NO | This column is not supported. It is NULL by default. |
| HISTOGRAM | VARCHAR2(15) | NO | The histogram type. |
| DEFAULT_ON_NULL | VARCHAR2(3) | NO | This column is not supported. It is NULL by default. |
| IDENTITY_COLUMN | VARCHAR2(3) | NO | This column is not supported. It is NULL by default. |
| EVALUATION_EDITION | VARCHAR2(128) | NO | This column is not supported. It is NULL by default. |
| UNUSABLE_BEFORE | VARCHAR2(128) | NO | This column is not supported. It is NULL by default. |
| UNUSABLE_BEGINNING | VARCHAR2(128) | NO | This column is not supported and is NULL by default. |
| COLLATION | VARCHAR2(100) | NO | This column is not supported and is NULL by default. |
Sample query
Query all columns of the T_SUBPART table in the tables accessible to the current user.
obclient [SYS]> SELECT * FROM SYS.ALL_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 columns of tables or views owned by the current user: USER_TAB_COLUMNS
Query columns of tables or views in the current tenant: DBA_TAB_COLUMNS