Note
This view is available starting with V4.0.0.
Purpose
The CDB_OB_TABLE_LOCATIONS view displays the locations of tables and partitions, including system tables, user tables, index tables, and so on.
Columns
Column |
Type |
Nullable |
Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | The tenant ID. |
| DATABASE_NAME | varchar(128) | NO | The database name. |
| TABLE_NAME | varchar(256) | NO | The table name. |
| TABLE_ID | bigint(20) | NO | The table ID. |
| TABLE_TYPE | varchar(13) | NO | The table type. Valid values:
|
| PARTITION_NAME | varchar(64) | NO | The name of the partition. If the table is not partitioned, this value is NULL. |
| SUBPARTITION_NAME | varchar(64) | NO | The name of the subpartition. If the table has no subpartition, this value is NULL. |
| INDEX_NAME | varchar(256) | NO | The name of the index table. If the table is not an index table, this value is NULL. |
| DATA_TABLE_ID | bigint(20) | NO | The ID of the primary table to which the index table or LOB auxiliary table belongs. If the table is not an index table or LOB auxiliary table, this value is NULL. |
| TABLET_ID | bigint(20) | NO | The tablet ID. |
| LS_ID | bigint(20) | NO | The log stream to which the object belongs. |
| ZONE | varchar(128) | NO | The zone where the replica resides. |
| SVR_IP | varchar(46) | NO | The IP address of the replica. |
| SVR_PORT | bigint(20) | NO | The port number of the replica. |
| 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 the replicated table. Valid values:
NoteThis column is available starting with V4.3.4. |
| OBJECT_ID | bigint(20) | NO | The object 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 (balanced mode) of the table group to which the table belongs
NoteThis column is available starting with V4.2.0. |
| SCOPE | varchar(20) | NO | The SCOPE attribute of the table group. Valid values:
NoteFor OceanBase Database V5.x, this column is available starting with V5.0.1. |
| 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 test_tbl3 table in the tenant whose ID is 1002 in the sys tenant.
obclient> SELECT * FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TENANT_ID = 1002 AND TABLE_NAME='test_tbl3'\G
The return result is as follows:
*************************** 1. row ***************************
TENANT_ID: 1002
DATABASE_NAME: test_db
TABLE_NAME: test_tbl3
TABLE_ID: 500011
TABLE_TYPE: USER TABLE
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
DATA_TABLE_ID: NULL
TABLET_ID: 200004
LS_ID: 1001
ZONE: zone1
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
ROLE: LEADER
REPLICA_TYPE: FULL
DUPLICATE_SCOPE: NONE
DUPLICATE_READ_CONSISTENCY: NONE
OBJECT_ID: 500011
TABLEGROUP_NAME: tblgroup2
TABLEGROUP_ID: 500010
SHARDING: NONE
SCOPE: SERVER
INDEX_TYPE: NULL
1 row in set