Note
This view was introduced in OceanBase Database V4.0.0.
Purpose
The DBA_OB_TABLE_LOCATIONS view displays the locations of tables or 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: USER TABLE: indicates a user table.SYSTEM TABLE: indicates a system table, which is an index table created by the system.LOB AUX TABLE: indicates a LOB auxiliary table, which is a table created by the system to implement LOB columns. |
| PARTITION_NAME | varchar(64) | NO | The name of the partition. The default value is NULL for non-partitioned tables. |
| SUBPARTITION_NAME | varchar(64) | NO | The name of the subpartition. The value is NULL for non-subpartitioned tables. |
| INDEX_NAME | varchar(256) | NO | The name of the index table. For tables other than index tables, the value is NULL. |
| DATA_TABLE_ID | bigint(20) | NO | The ID of the primary table. If the table is not an index table or LOB table, the value is NULL. |
| TABLET_ID | bigint(20) | NO | The ID of the tablet. |
| LS_ID | bigint(20) | NO | The log stream where the object resides. |
| ZONE | varchar(128) | NO | The zone where the replica resides. |
| SVR_IP | varchar(46) | NO | The IP address of the OBServer node where the replica resides. |
| SVR_PORT | bigint(20) | NO | The port number of the OBServer node where the replica resides. |
| ROLE | varchar(8) | NO | The role of the replica. |
| REPLICA_TYPE | varchar(18) | NO | The replica type of the log stream. Valid values:
|
| DUPLICATE_SCOPE | varchar(7) | NO | Indicates whether the table is a replicated table. Valid values:
NoteThis column is available in OceanBase Database V4.2.0 and later. |
| OBJECT_ID | bigint(20) | NO | The object ID corresponding to the partition.
NoteThis column is available in OceanBase Database V4.2.0 and later. |
| TABLEGROUP_NAME | varchar(128) | NO | The name of the table group to which the table belongs.
NoteThis column is available in OceanBase Database V4.2.0 and later. |
| TABLEGROUP_ID | bigint(20) | NO | The ID of the table group to which the table belongs.
NoteThis column is available in OceanBase Database V4.2.0 and later. |
| SHARDING | varchar(10) | NO | The sharding attribute (in load balancing mode) of the table group to which the table belongs.
NoteThis column is available in OceanBase Database V4.2.0 and later. |
Sample query
Query the location of the t1 table.
obclient [test]> 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
OBJECT_ID: 500010
TABLEGROUP_NAME: NULL
TABLEGROUP_ID: NULL
SHARDING: NULL
1 row in set