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 applies only to OceanBase Database in Oracle mode.
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 data type of the column. |
| DATA_TYPE_MOD | VARCHAR2(3) | NO | Not supported. The default value is NULL. |
| DATA_TYPE_OWNER | VARCHAR2(128) | NO | 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 column for numeric data types, and the binary precision for floating-point data types. All other data types are NULL. |
| DATA_SCALE | NUMBER | NO | The number of digits to the right of the decimal point. |
| NULLABLE | VARCHAR2(1) | NO | Whether the column allows NULL values. |
| COLUMN_ID | NUMBER | NO | The column sequence 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 histogram of the column. |
| 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_CSNCHAR_CS |
| CHAR_COL_DECL_LENGTH | NUMBER | NO | Not supported. The default value is NULL. |
| GLOBAL_STATS | VARCHAR2(3) | NO | YES if statistics are collected or incrementally maintained, otherwise NO. |
| USER_STATS | VARCHAR2(3) | NO | YES if statistics are directly entered by users, otherwise NO. |
| AVG_COL_LEN | NUMBER | NO | The average column length in bytes. |
| CHAR_LENGTH | NUMBER | NO | The length of a character column. |
| CHAR_USED | VARCHAR2(1) | NO | Indicates whether the column length is measured in bytes or characters. |
| V80_FMT_IMAGE | VARCHAR2(3) | NO | Not supported. The default value is NULL. |
| DATA_UPGRADED | VARCHAR2(3) | NO | Not supported. The default value is NULL. |
| HISTOGRAM | VARCHAR2(15) | NO | The histogram type. |
| DEFAULT_ON_NULL | VARCHAR2(3) | NO | Not supported. The default value is NULL |
| IDENTITY_COLUMN | VARCHAR2(3) | NO | Not supported. The default value is NULL |
| EVALUATION_EDITION | VARCHAR2(128) | NO | Not supported. The default value is NULL |
| UNUSABLE_BEFORE | VARCHAR2(128) | NO | Not supported. The default value is NULL |
| UNUSABLE_BEGINNING | VARCHAR2(128) | NO | Not supported. The default value is NULL |
| COLLATION | VARCHAR2(100) | NO | Not supported. The default value is NULL |
Sample query
Query all columns of the T_SUBPART table that the current user can access.
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 the current user owns: USER_TAB_COLUMNS
Query the columns of all tables or views in the current tenant: DBA_TAB_COLUMNS