Purpose
Lists all tables to which the current user has access. The ALL_TABLES view displays all tables accessible to the current user.
Applicability
This view applies only to 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. |
| TABLE_NAME | VARCHAR2(128) | NO | The name of the table. |
| TABLESPACE_NAME | VARCHAR2(30) | NO | The name of the tablespace that contains the table. |
| CLUSTER_NAME | VARCHAR2(128) | NO | At present, this column is not supported and its value is NULL by default. |
| IOT_NAME | VARCHAR2(128) | NO | At present, this column is not supported and its value is NULL by default. |
| STATUS | VARCHAR2(8) | NO | The status of the table. Valid values: |
| PCT_FREE | NUMBER | NO | The minimum percentage of free space in a block. |
| PCT_USED | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| INI_TRANS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| MAX_TRANS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| INITIAL_EXTENT | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| NEXT_EXTENT | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| MIN_EXTENTS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| MAX_EXTENTS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| PCT_INCREASE | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| FREELISTS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| FREELIST_GROUPS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| LOGGING | VARCHAR2(3) | NO | At present, this column is not supported and its value is NULL by default. |
| BACKED_UP | VARCHAR2(1) | NO | At present, this column is not supported and its value is NULL by default. |
| NUM_ROWS | NUMBER | NO | The number rows in the table. |
| BLOCKS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| EMPTY_BLOCKS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| AVG_SPACE | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| CHAIN_CNT | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| AVG_ROW_LEN | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| AVG_SPACE_FREELIST_BLOCKS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| NUM_FREELIST_BLOCKS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| DEGREE | VARCHAR2(10) | NO | At present, this column is not supported and its value is NULL by default. |
| INSTANCES | VARCHAR2(10) | NO | At present, this column is not supported and its value is NULL by default. |
| CACHE | VARCHAR2(5) | NO | At present, this column is not supported and its value is NULL by default. |
| TABLE_LOCK | VARCHAR2(8) | 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. |
| LAST_ANALYZED | DATE | NO | At present, this column is not supported and its value is NULL by default. |
| PARTITIONED | VARCHAR2(3) | NO | Indicates whether the table is partitioned. Valid values: |
| IOT_TYPE | VARCHAR2(12) | NO | At present, this column is not supported and its value is NULL by default. |
| TEMPORARY | VARCHAR2(1) | NO | Indicates whether the table is a temporary table. |
| SECONDARY | VARCHAR2(1) | NO | At present, this column is not supported and its value is NULL by default. |
| NESTED | VARCHAR2(3) | NO | Indicates whether the table is a nested table. Valid values: |
| BUFFER_POOL | VARCHAR2(7) | NO | At present, this column is not supported and its value is NULL by default. |
| FLASH_CACHE | VARCHAR2(7) | NO | At present, this column is not supported and its value is NULL by default. |
| CELL_FLASH_CACHE | VARCHAR2(7) | NO | At present, this column is not supported and its value is NULL by default. |
| ROW_MOVEMENT | VARCHAR2(8) | NO | At present, this column is not supported and its value is NULL by default.
NoteThe ROW_MOVEMENT column applies to OceanBase Database V4.2.5 and later, and it was introduced in V4.2.5 BP2. |
| 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. |
| DURATION | VARCHAR2(15) | NO | The duration of the table if it is a temporary table. |
| SKIP_CORRUPT | VARCHAR2(8) | NO | At present, this column is not supported and its value is NULL by default. |
| MONITORING | VARCHAR2(3) | NO | At present, this column is not supported and its value is NULL by default. |
| CLUSTER_OWNER | VARCHAR2(128) | NO | At present, this column is not supported and its value is NULL by default. |
| DEPENDENCIES | VARCHAR2(8) | NO | At present, this column is not supported and its value is NULL by default. |
| COMPRESSION | VARCHAR2(8) | NO | At present, this column is not supported and its value is NULL by default. |
| COMPRESS_FOR | VARCHAR2(30) | NO | At present, this column is not supported and its value is NULL by default. |
| DROPPED | VARCHAR2(3) | NO | Indicates whether the table has been dropped and moved to the recycle bin. |
| READ_ONLY | VARCHAR2(3) | NO | At present, this column is not supported and its value is NULL by default. |
| SEGMENT_CREATED | VARCHAR2(3) | NO | At present, this column is not supported and its value is NULL by default. |
| RESULT_CACHE | VARCHAR2(7) | NO | At present, this column is not supported and its value is NULL by default. |
| CLUSTERING | VARCHAR2(3) | NO | At present, the value of this column is NULL by default. |
| ACTIVITY_TRACKING | VARCHAR2(23) | NO | At present, the value of this column is NULL by default. |
| DML_TIMESTAMP | VARCHAR2(25) | NO | At present, the value of this column is NULL by default. |
| HAS_IDENTITY | VARCHAR2(3) | NO | At present, the value of this column is NULL by default. |
| CONTAINER_DATA | VARCHAR2(3) | NO | At present, the value of this column is NULL by default. |
| INMEMORY | VARCHAR2(8) | NO | At present, the value of this column is NULL by default. |
| INMEMORY_PRIORITY | VARCHAR2(8) | NO | At present, the value of this column is NULL by default. |
| INMEMORY_DISTRIBUTE | VARCHAR2(15) | NO | This column is used for compatibility with Oracle. The default value of this column is NULL, which has no actual meaning. |
| INMEMORY_COMPRESSION | VARCHAR2(17) | NO | At present, the value of this column is NULL by default. |
| INMEMORY_DUPLICATE | VARCHAR2(13) | NO | At present, the value of this column is NULL by default. |
| DEFAULT_COLLATION | VARCHAR2(100) | NO | At present, the value of this column is NULL by default. |
| DUPLICATED | VARCHAR2(1) | NO | At present, the value of this column is NULL by default. |
| SHARDED | VARCHAR2(1) | NO | At present, the value of this column is NULL by default. |
| EXTERNALLY_SHARDED | VARCHAR2(1) | NO | At present, the value of this column is NULL by default. |
| EXTERNALLY_DUPLICATED | VARCHAR2(1) | NO | At present, the value of this column is NULL by default. |
| EXTERNAL | VARCHAR2(3) | NO | Indicates whether the table is an external table. Valid values:
|
| HYBRID | VARCHAR2(3) | NO | At present, the value of this column is NULL by default. |
| CELLMEMORY | VARCHAR2(24) | NO | This column is used for compatibility with Oracle. The default value of this column is NULL, which has no actual meaning. |
| CONTAINERS_DEFAULT | VARCHAR2(3) | NO | At present, the value of this column is NULL by default. |
| CONTAINER_MAP | VARCHAR2(3) | NO | At present, the value of this column is NULL by default. |
| EXTENDED_DATA_LINK | VARCHAR2(3) | NO | At present, the value of this column is NULL by default. |
| EXTENDED_DATA_LINK_MAP | VARCHAR2(3) | NO | At present, the value of this column is NULL by default. |
| INMEMORY_SERVICE | VARCHAR2(12) | NO | At present, the value of this column is NULL by default. |
| INMEMORY_SERVICE_NAME | VARCHAR2(1000) | NO | At present, the value of this column is NULL by default. |
| CONTAINER_MAP_OBJECT | VARCHAR2(3) | NO | At present, the value of this column is NULL by default. |
| MEMOPTIMIZE_READ | VARCHAR2(8) | NO | At present, the value of this column is NULL by default. |
| MEMOPTIMIZE_WRITE | VARCHAR2(8) | NO | At present, the value of this column is NULL by default. |
| HAS_SENSITIVE_COLUMN | VARCHAR2(3) | NO | At present, the value of this column is NULL by default. |
| ADMIT_NULL | VARCHAR2(3) | NO | At present, the value of this column is NULL by default. |
| DATA_LINK_DML_ENABLED | VARCHAR2(3) | NO | At present, the value of this column is NULL by default. |
| LOGICAL_REPLICATION | VARCHAR2(8) | NO | At present, the value of this column is NULL by default. |
Sample query
Query the information about the TBL1 table that is accessible to the current user.
obclient [SYS]> SELECT * FROM SYS.ALL_TABLES WHERE TABLE_NAME = 'TBL1'\G
The query result is as follows:
*************************** 1. row ***************************
OWNER: SYS
TABLE_NAME: TBL1
TABLESPACE_NAME: NULL
CLUSTER_NAME: NULL
IOT_NAME: NULL
STATUS: VALID
PCT_FREE: 0
PCT_USED: NULL
INI_TRANS: NULL
MAX_TRANS: NULL
INITIAL_EXTENT: NULL
NEXT_EXTENT: NULL
MIN_EXTENTS: NULL
MAX_EXTENTS: NULL
PCT_INCREASE: NULL
FREELISTS: NULL
FREELIST_GROUPS: NULL
LOGGING: NULL
BACKED_UP: NULL
NUM_ROWS: 1
BLOCKS: NULL
EMPTY_BLOCKS: NULL
AVG_SPACE: NULL
CHAIN_CNT: NULL
AVG_ROW_LEN: NULL
AVG_SPACE_FREELIST_BLOCKS: NULL
NUM_FREELIST_BLOCKS: NULL
DEGREE: NULL
INSTANCES: NULL
CACHE: NULL
TABLE_LOCK: NULL
SAMPLE_SIZE: NULL
LAST_ANALYZED: NULL
PARTITIONED: NO
IOT_TYPE: NULL
TEMPORARY: N
SECONDARY: NULL
NESTED: NO
BUFFER_POOL: NULL
FLASH_CACHE: NULL
CELL_FLASH_CACHE: NULL
ROW_MOVEMENT: NO
GLOBAL_STATS: NULL
USER_STATS: NULL
DURATION: NULL
SKIP_CORRUPT: NULL
MONITORING: NULL
CLUSTER_OWNER: NULL
DEPENDENCIES: NULL
COMPRESSION: NULL
COMPRESS_FOR: NULL
DROPPED: NO
READ_ONLY: NULL
SEGMENT_CREATED: NULL
RESULT_CACHE: NULL
CLUSTERING: NULL
ACTIVITY_TRACKING: NULL
DML_TIMESTAMP: NULL
HAS_IDENTITY: NULL
CONTAINER_DATA: NULL
INMEMORY: NULL
INMEMORY_PRIORITY: NULL
INMEMORY_DISTRIBUTE: NULL
INMEMORY_COMPRESSION: NULL
INMEMORY_DUPLICATE: NULL
DEFAULT_COLLATION: NULL
DUPLICATED: NULL
SHARDED: NULL
EXTERNALLY_SHARDED: NULL
EXTERNALLY_DUPLICATED: NULL
EXTERNAL: NO
HYBRID: NULL
CELLMEMORY: NULL
CONTAINERS_DEFAULT: NULL
CONTAINER_MAP: NULL
EXTENDED_DATA_LINK: NULL
EXTENDED_DATA_LINK_MAP: NULL
INMEMORY_SERVICE: NULL
INMEMORY_SERVICE_NAME: NULL
CONTAINER_MAP_OBJECT: NULL
MEMOPTIMIZE_READ: NULL
MEMOPTIMIZE_WRITE: NULL
HAS_SENSITIVE_COLUMN: NULL
ADMIT_NULL: NULL
DATA_LINK_DML_ENABLED: NULL
LOGICAL_REPLICATION: NULL
1 row in set