Note
This view is introduced since OceanBase Database V4.0.0.
Purpose
The CDB_OB_TABLE_LOCATIONS view displays the locations of tables or partitions, including system tables, user tables, and index tables.
Columns
| Column | Type | Nullable? | 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. Valid values include:
|
| PARTITION_NAME | varchar(64) | NO | Name of the primary partition. For a non-partitioned table, this value is NULL. |
| SUBPARTITION_NAME | varchar(64) | NO | Name of the subpartition. If there is no subpartition, this value is NULL. |
| INDEX_NAME | varchar(256) | NO | If the table is an index table, the name of the index table is displayed. Otherwise, NULL is displayed. |
| DATA_TABLE_ID | bigint(20) | NO | If the table is an index table or a LOB auxiliary table, the ID of the corresponding primary table is displayed. Otherwise, NULL is displayed. |
| TABLET_ID | bigint(20) | NO | Tablet ID |
| LS_ID | bigint(20) | NO | Log stream ID of the object |
| ZONE | varchar(128) | NO | Zone where the replica is located |
| SVR_IP | varchar(46) | NO | IP address of the replica |
| SVR_PORT | bigint(20) | NO | Port of the replica |
| ROLE | varchar(8) | NO | Role of the replica |
| REPLICA_TYPE | varchar(18) | NO | Log stream replica type. Valid values include:
|
| DUPLICATE_SCOPE | varchar(7) | NO | Indicates whether the table is a replicated table. Valid values include:
NoteThis field was introduced in V4.2.0. |
| DUPLICATE_READ_CONSISTENCY | varchar(6) | NO | Specifies the read consistency constraint for the standby replica of a replicated table. Valid values include:
NoteThis field was introduced in V4.3.4. |
| OBJECT_ID | bigint(20) | NO | ID of the partition
NoteThis field was introduced in V4.2.0. |
| TABLEGROUP_NAME | varchar(128) | NO | Name of the table group to which the table belongs
NoteThis field was introduced in V4.2.0. |
| TABLEGROUP_ID | bigint(20) | NO | ID of the table group to which the table belongs
NoteThis field was introduced in V4.2.0. |
| SHARDING | varchar(10) | NO | Sharding attribute (equilibrium mode) of the table group to which the table belongs
NoteThis field was introduced in V4.2.0. |
Sample query
The system tenant queries the location of the test1_tbl1 table in the tenant with the ID of 1004.
SELECT * FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TENANT_ID = 1004 AND TABLE_NAME='test1_tbl1'\G
The query result is as follows:
*************************** 1. row ***************************
TENANT_ID: 1004
DATABASE_NAME: SYS
TABLE_NAME: TEST1_TBL1
TABLE_ID: 500002
TABLE_TYPE: USER TABLE
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
DATA_TABLE_ID: NULL
TABLET_ID: 200001
LS_ID: 1001
ZONE: zone1
SVR_IP: xxx.xx.xxx.xxx
SVR_PORT: 2882
ROLE: LEADER
REPLICA_TYPE: FULL
DUPLICATE_SCOPE: NONE
DUPLICATE_READ_CONSISTENCY: NONE
OBJECT_ID: 500002
TABLEGROUP_NAME: NULL
TABLEGROUP_ID: NULL
SHARDING: NULL
1 row in set