Note
This view is available starting with V4.0.0.
Overview
The CDB_OB_TABLE_LOCATIONS view displays the locations of tables or partitions, including system tables, user tables, index tables, and so on.
Columns
Field Name |
Type |
Whether can be NULL |
Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | Tenant ID |
| DATABASE_NAME | varchar(128) | NO | Database Name |
| TABLE_NAME | varchar(256) | NO | Table name |
| TABLE_ID | bigint(20) | NO | Table ID |
| TABLE_TYPE | varchar(13) | NO | Table type:
|
| PARTITION_NAME | varchar(64) | NO | The name of the partition. For a non-partitioned table, this parameter is NULL by default. |
| SUBPARTITION_NAME | varchar(64) | NO | Subpartition name. NULL if no subpartitions exist. |
| INDEX_NAME | varchar(256) | NO | Displays the index table name if it is an index table; otherwise, displays NULL. |
| DATA_TABLE_ID | bigint(20) | NO | Displays the ID of the primary table to which an index or LOB auxiliary table belongs. If no primary table is associated, NULL is displayed. |
| TABLET_ID | bigint(20) | NO | TABLET ID |
| LS_ID | bigint(20) | NO | Log stream where the object is located |
| ZONE | varchar(128) | NO | ZONE of the Replica |
| SVR_IP | varchar(46) | NO | IP address of the replica location |
| SVR_PORT | bigint(20) | NO | Port information of the replica location |
| ROLE | varchar(8) | NO | Replica Role |
| REPLICA_TYPE | varchar(18) | NO | Log stream replica type:
|
| DUPLICATE_SCOPE | varchar(7) | NO | Indicates whether this table is a replicated table. Valid values:
NoteThis field is available starting with V4.2.0. |
| DUPLICATE_READ_CONSISTENCY | varchar(6) | NO | Displays the read-consistency constraints of the standby replica of the replicated table. Valid values:
NoteThis field is available starting with V4.3.4. |
| OBJECT_ID | bigint(20) | NO | Object ID Corresponding to Partition
NoteThis field is available starting with V4.2.0. |
| TABLEGROUP_NAME | varchar(128) | NO | The name of the table group to which the table belongs.
NoteThis field is available starting with V4.2.0. |
| TABLEGROUP_ID | bigint(20) | NO | Table corresponding to the table group ID
NoteThis field is available starting with V4.2.0. |
| SHARDING | varchar(20) | NO | Sharding attribute (balanced mode) of the table corresponding to the table group.
NoteThis field is available starting with V4.2.0. |
| SCOPE | varchar(20) | NO | Table groupSCOPEAttribute. Valid values:
NoteFor V4.4.2, this field is available starting with V4.4.2 BP1. |
| INDEX_TYPE | varchar(6) | NO | Displays indexes of the index table.GLOBAL/LOCALThe attribute information can have the following three values:
Note
|
Sample query
The system tenant queries the location of the table test_tbl3 belonging to the tenant with ID 1002.
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