Note
This view is available starting with V2.2.30.
Purpose
This view displays all columns of tables and views that are accessible to the current user.
Applicability
This view is available only in Oracle 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 name of the column. |
| DATA_TYPE | VARCHAR2(128) | NO | The type of the column. |
| 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 length of the column, in bytes. |
| DATA_PRECISION | NUMBER | NO | The decimal precision of a numeric data type; the binary precision of a floating-point data type; 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 in the column. |
| HIGH_VALUE | VARCHAR2(128) | NO | The maximum value in 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 date and 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 are collected or incrementally maintained, otherwise NO. |
| USER_STATS | VARCHAR2(3) | NO | Indicates whether the statistics are 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 for character data types. |
| CHAR_USED | VARCHAR2(1) | NO | Indicates whether the column is measured in bytes or characters. |
| 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. It is set to NULL by default. |
| COLLATION | VARCHAR2(100) | NO | This column is not supported. It is set to NULL by default. |
Sample query
Query all columns of the T_SUBPART table that is 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 the columns of the tables or views that are owned by the current user: USER_TAB_COLUMNS
Query the columns of all tables or views in the current tenant: DBA_TAB_COLUMNS