Note
This view is introduced since OceanBase Database V4.0.0.
Purpose
The oceanbase.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 | The ID of the tenant. |
| 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: a user table.SYSTEM TABLE: a system table, which is an index table created by the system.LOB AUX TABLE: 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 server where the replica resides. |
| SVR_PORT | bigint(20) | NO | The port number of the server where the replica resides. |
| ROLE | varchar(8) | NO | The role of the replica. Valid values: |
| REPLICA_TYPE | varchar(18) | NO | The type of the log stream. Currently, only FULL is supported, which indicates full-featured replicas. |
Sample query
obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TENANT_ID = 1004;
The query result is as follows:
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+--------+--------------+-----------------+-----------+-----------------+---------------+----------+
| TENANT_ID | DATABASE_NAME | TABLE_NAME | TABLE_ID | TABLE_TYPE | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | DATA_TABLE_ID | TABLET_ID | LS_ID | ZONE | SVR_IP | SVR_PORT | ROLE | REPLICA_TYPE | DUPLICATE_SCOPE | OBJECT_ID | TABLEGROUP_NAME | TABLEGROUP_ID | SHARDING |
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+--------+--------------+-----------------+-----------+-----------------+---------------+----------+
| 1004 | SYS | T2_F_RL | 500061 | USER TABLE | P0 | SP0 | NULL | NULL | 200043 | 1001 | zone1 | xx.xx.xx.xx | 2882 | LEADER | FULL | NONE | 500064 | NULL | NULL | NULL |
| 1004 | SYS | T2_F_RL | 500061 | USER TABLE | P0 | SP1 | NULL | NULL | 200044 | 1002 | zone1 | xx.xx.xx.xx | 2882 | LEADER | FULL | NONE | 500065 | NULL | NULL | NULL |
| 1004 | SYS | T2_F_RL | 500061 | USER TABLE | P1 | SP2 | NULL | NULL | 200045 | 1002 | zone1 | xx.xx.xx.xx | 2882 | LEADER | FULL | NONE | 500066 | NULL | NULL | NULL |
| 1004 | SYS | T2_F_RL | 500061 | USER TABLE | P1 | SP3 | NULL | NULL | 200046 | 1001 | zone1 | xx.xx.xx.xx | 2882 | LEADER | FULL | NONE | 500067 | NULL | NULL | NULL |
| 1004 | SYS | T2_F_RL | 500061 | USER TABLE | P1 | SP4 | NULL | NULL | 200047 | 1001 | zone1 | xx.xx.xx.xx | 2882 | LEADER | FULL | NONE | 500068 | NULL | NULL | NULL |
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+--------+--------------+-----------------+-----------+-----------------+---------------+----------+
5 rows in set
