Note
This view is introduced since OceanBase Database V4.0.0.
Purpose
This view displays the locations of tables or partitions, including system tables, user tables, and index tables.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| DATABASE_NAME | VARCHAR2(128) | NO | Database name. |
| TABLE_NAME | VARCHAR2(256) | NO | Table name. |
| TABLE_ID | NUMBER(38) | NO | Table ID. |
| TABLE_TYPE | VARCHAR2(13) | NO | Table type. Valid values include:
|
| PARTITION_NAME | VARCHAR2(64) | NO | Name of the primary partition. The value is NULL for a non-partitioned table. |
| SUBPARTITION_NAME | VARCHAR2(64) | NO | Name of the subpartition. The value is NULL if there is no subpartition. |
| INDEX_NAME | VARCHAR2(1024) | NO | If the table is an index table, the name of the index table is displayed. Otherwise, NULL is displayed. |
| DATA_TABLE_ID | NUMBER | NO | ID of the primary table if the table is an index table or a LOB auxiliary table. Otherwise, NULL is displayed. |
| TABLET_ID | NUMBER(38) | NO | TABLET ID. |
| LS_ID | NUMBER | NO | ID of the log stream where the object resides. |
| ZONE | VARCHAR2(128) | NO | Zone where the replica resides. |
| SVR_IP | VARCHAR2(46) | NO | IP address of the replica. |
| SVR_PORT | NUMBER | NO | Port of the replica. |
| ROLE | VARCHAR2(8) | NO | Role of the replica. |
| REPLICA_TYPE | VARCHAR2(18) | NO | Log stream replica type. Valid values include:
|
| DUPLICATE_SCOPE | VARCHAR2(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 | Read consistency constraint of the standby replica of a replicated table. Valid values include:
NoteThis field was introduced in V4.3.4. |
| OBJECT_ID | NUMBER(38) | NO | ID of the partition
NoteThis field was introduced in V4.2.0. |
| TABLEGROUP_NAME | VARCHAR2(128) | NO | Name of the table group to which the table belongs
NoteThis field was introduced in V4.2.0. |
| TABLEGROUP_ID | NUMBER(38) | NO | ID of the table group to which the table belongs
NoteThis field was introduced in V4.2.0. |
| SHARDING | VARCHAR2(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 following example queries the location of the TEST1_TBL1 table.
SELECT * FROM SYS.DBA_OB_TABLE_LOCATIONS WHERE TABLE_NAME='TEST1_TBL1'\G
The query result is as follows:
*************************** 1. row ***************************
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: xx.xx.xx.xx
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