Note
This view was introduced in OceanBase Database V2.2.77.
Purpose
The ALL_TAB_COLS view displays all columns of the tables and views accessible to the current user.
This view differs from the ALL_TAB_COLUMNS view in that this view displays quickly deleted columns but the ALL_TAB_COLUMNS view does not.
Applicability
This view is available only in OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL 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 | At present, this column is not supported and its value is NULL by default. |
| DATA_TYPE_OWNER | VARCHAR2(128) | NO | At present, this column is not supported and its value is NULL by default. |
| DATA_LENGTH | NUMBER | NO | The length of the column, in bytes. |
| DATA_PRECISION | NUMBER | NO | The decimal precision for the NUMBER data type and the binary precision for the FLOAT data type. The value of this column is NULL for all other data types. |
| DATA_SCALE | NUMBER | NO | The number of digits on the right of the decimal point in a number. |
| NULLABLE | VARCHAR2(1) | NO | Indicates whether the column can be null. |
| COLUMN_ID | NUMBER | NO | The sequence number of the column. |
| 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 | At present, this column is not supported and its value is NULL by default. |
| LOW_VALUE | VARCHAR2(128) | NO | At present, this column is not supported and its value is NULL by default. |
| HIGH_VALUE | VARCHAR2(128) | NO | At present, this column is not supported and is its value NULL by default. |
| DENSITY | NUMBER | NO | At present, this column is not supported and is its value NULL by default. |
| NUM_NULLS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| NUM_BUCKETS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| LAST_ANALYZED | DATE | NO | At present, this column is not supported and its value is NULL by default. |
| SAMPLE_SIZE | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| CHARACTER_SET_NAME | VARCHAR2(44) | NO | At present, this column is not supported and its value is NULL by default. |
| CHAR_COL_DECL_LENGTH | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| GLOBAL_STATS | VARCHAR2(3) | NO | At present, this column is not supported and its value is NULL by default. |
| USER_STATS | VARCHAR2(3) | NO | At present, this column is not supported and its value is NULL by default. |
| AVG_COL_LEN | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| CHAR_LENGTH | NUMBER | NO | The character length of the column. |
| CHAR_USED | VARCHAR2(1) | NO | Indicates whether the column uses the byte length or the character length. |
| V80_FMT_IMAGE | VARCHAR2(3) | NO | At present, this column is not supported and its value is NULL by default. |
| DATA_UPGRADED | VARCHAR2(3) | NO | At present, this column is not supported and its value is NULL by default. |
| HIDDEN_COLUMN | VARCHAR2(3) | NO | Indicates whether the column is a hidden column. Valid values: YESNO |
| VIRTUAL_COLUMN | VARCHAR2(3) | NO | Indicates whether the column is a virtual column. Valid values: YESNO |
| SEGMENT_COLUMN_ID | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| INTERNAL_COLUMN_ID | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| HISTOGRAM | VARCHAR2(15) | NO | At present, this column is not supported and its value is NULL by default. |
| QUALIFIED_COL_NAME | VARCHAR2(4000) | NO | The qualified column name. |
| USER_GENERATED | VARCHAR2(3) | NO | At present, this column is not supported and its value is YES by default. |
| DEFAULT_ON_NULL | VARCHAR2(3) | NO | At present, this column is not supported and its value is NULL by default. |
| IDENTITY_COLUMN | VARCHAR2(3) | NO | At present, this column is not supported and its value is NULL by default. |
| EVALUATION_EDITION | VARCHAR2(128) | NO | At present, this column is not supported and its value is NULL by default. |
| UNUSABLE_BEFORE | VARCHAR2(128) | NO | At present, this column is not supported and its value is NULL by default. |
| UNUSABLE_BEGINNING | VARCHAR2(128) | NO | At present, this column is not supported and its value is NULL by default. |
| COLLATION | VARCHAR2(100) | NO | At present, this column is not supported and its value is NULL by default. |
| COLLATED_COLUMN_ID | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
Sample query
Query all columns of the tables and views accessible to the current user.
obclient [SYS]> SELECT * FROM SYS.ALL_TAB_COLS WHERE ROWNUM =1\G
The query result is as follows:
*************************** 1. row ***************************
OWNER: oceanbase
TABLE_NAME: __all_core_table
COLUMN_NAME: gmt_create
DATA_TYPE: TIMESTAMP
DATA_TYPE_MOD: NULL
DATA_TYPE_OWNER: NULL
DATA_LENGTH: 11
DATA_PRECISION: NULL
DATA_SCALE: 6
NULLABLE: Y
COLUMN_ID: 16
DEFAULT_LENGTH: 17
DATA_DEFAULT: CURRENT_TIMESTAMP
NUM_DISTINCT: NULL
LOW_VALUE: NULL
HIGH_VALUE: NULL
DENSITY: NULL
NUM_NULLS: NULL
NUM_BUCKETS: NULL
LAST_ANALYZED: NULL
SAMPLE_SIZE: NULL
CHARACTER_SET_NAME: NULL
CHAR_COL_DECL_LENGTH: NULL
GLOBAL_STATS: NULL
USER_STATS: NULL
AVG_COL_LEN: NULL
CHAR_LENGTH: 0
CHAR_USED: NULL
V80_FMT_IMAGE: NULL
DATA_UPGRADED: NULL
HIDDEN_COLUMN: NO
VIRTUAL_COLUMN: NO
SEGMENT_COLUMN_ID: NULL
INTERNAL_COLUMN_ID: NULL
HISTOGRAM: NULL
QUALIFIED_COL_NAME: gmt_create
USER_GENERATED: YES
DEFAULT_ON_NULL: NULL
IDENTITY_COLUMN: NULL
EVALUATION_EDITION: NULL
UNUSABLE_BEFORE: NULL
UNUSABLE_BEGINNING: NULL
COLLATION: NULL
COLLATED_COLUMN_ID: NULL
1 row in set (0.114 sec)