Note
This view is available starting with V4.0.0.
Purpose
This view displays the locations of tables and partitions, including system tables, user tables, and index tables.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| DATABASE_NAME | varchar(128) | NO | The name of the database. |
| TABLE_NAME | varchar(256) | NO | The name of the table. |
| TABLE_ID | bigint(20) | NO | The ID of the table. |
| TABLE_TYPE | varchar(13) | NO | The type of the table. Valid values:
|
| PARTITION_NAME | varchar(64) | NO | The name of the partition. For non-partitioned tables, this column is NULL. |
| SUBPARTITION_NAME | varchar(64) | NO | The name of the subpartition. If no subpartition exists, this column is NULL. |
| INDEX_NAME | varchar(256) | NO | The name of the index table. If the table is not an index table, this column is NULL. |
| DATA_TABLE_ID | bigint(20) | NO | The ID of the main table to which the index table or LOB auxiliary table belongs. If the table is not an index table or LOB auxiliary table, this column is NULL. |
| TABLET_ID | bigint(20) | NO | The ID of the tablet. |
| LS_ID | bigint(20) | NO | The ID of the log stream to which the object belongs. |
| ZONE | varchar(128) | NO | The zone where the replica is located. |
| SVR_IP | varchar(46) | NO | The IP address of the server where the replica is located. |
| SVR_PORT | bigint(20) | NO | The port number of the server where the replica is located. |
| ROLE | varchar(8) | NO | The role of the replica. |
| REPLICA_TYPE | varchar(18) | NO | The type of the log stream replica. Valid values:
|
| DUPLICATE_SCOPE | varchar(7) | NO | The value indicates whether the table is a replicated table. Valid values:
NoteThis column is available starting with V4.2.0. |
| DUPLICATE_READ_CONSISTENCY | varchar(6) | NO | The value indicates the read consistency constraint of the standby replica of a replicated table. Valid values:
NoteThis column is available starting with V4.3.4. |
| OBJECT_ID | bigint(20) | NO | The ID of the partition.
NoteThis column is available starting with V4.2.0. |
| TABLEGROUP_NAME | varchar(128) | NO | The name of the table group to which the table belongs.
NoteThis column is available starting with V4.2.0. |
| TABLEGROUP_ID | bigint(20) | NO | The ID of the table group to which the table belongs.
NoteThis column is available starting with V4.2.0. |
| SHARDING | varchar(20) | NO | The sharding attribute (balance mode) of the table group to which the table belongs.
NoteThis column is available starting with V4.2.0. |
| INDEX_TYPE | varchar(6) | NO | The value indicates the GLOBAL/LOCAL attribute of the index of an index table. Valid values:
Note
|
Sample query
Query the location of the t1 table.
obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE TABLE_NAME='t1'\G
The query result is as follows:
*************************** 1. row ***************************
DATABASE_NAME: test
TABLE_NAME: t1
TABLE_ID: 500010
TABLE_TYPE: USER TABLE
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
DATA_TABLE_ID: NULL
TABLET_ID: 200004
LS_ID: 1
ZONE: zone1
SVR_IP: 172.xx.xx.xx
SVR_PORT: 2882
ROLE: LEADER
REPLICA_TYPE: FULL
DUPLICATE_SCOPE: NONE
DUPLICATE_READ_CONSISTENCY: NONE
OBJECT_ID: 500010
TABLEGROUP_NAME: NULL
TABLEGROUP_ID: NULL
SHARDING: NULL
INDEX_TYPE: NULL
1 row in set