Information about the table

Query the associated objects of the table

Unlike V2.x and V3.x, OceanBase Database V4.x uses the object_id to globally encode database objects. The object_id is unique within a tenant.

In OceanBase Database V4.x, the object_id of a table is the same as its table_id. Therefore, you can query the information of a table by using its table_id. The information includes the table name, the database to which the table belongs, and the sub-objects of the table (such as the partition name).

• Sys tenant

  The CDB_OBJECTS view records the information of all tables in all tenants. You can use this view to query the associated information of any table. The statement is as follows:

  obclient> SELECT * FROM oceanbase.CDB_OBJECTS WHERE CON_ID=tenant_id AND OBJECT_ID=table_id AND OBJECT_TYPE='TABLE';
  

  For example, if the table_id of a table in the tenant with tenant_id of 1002 is 500007, you can query the information of the table in the system tenant by using the following statement:

  obclient> SELECT * FROM oceanbase.CDB_OBJECTS WHERE CON_ID=1002 AND OBJECT_ID=500007 AND OBJECT_TYPE='TABLE';
  

• User tenant

  In a MySQL-compatible user tenant, you can use the following statement to query the associated information of a table:

  obclient> SELECT * FROM oceanbase.DBA_OBJECTS WHERE OBJECT_ID=table_id AND OBJECT_TYPE='TABLE';
  

  In an Oracle-compatible user tenant, you can use the following statement to query the associated information of a table:

  obclient> SELECT * FROM SYS.DBA_OBJECTS WHERE OBJECT_ID=table_id AND OBJECT_TYPE='TABLE';
  

Query the location of the table or partition

When you know the table_id of a table, you can use it to query the node information of the partition replica distribution for that table.

• Sys tenant

  In the sys tenant, you can use the following statement to query:

  obclient> SELECT * FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TENANT_ID=tenant_id AND TABLE_ID=table_id AND TABLE_TYPE='USER TABLE';
  

• User tenant

  In a MySQL-compatible user tenant, you can use the following statement to query:

  obclient> SELECT * FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE TABLE_ID=table_id AND TABLE_TYPE='USER TABLE';
  

  In an Oracle-compatible user tenant, you can use the following statement to query:

  obclient> SELECT * FROM SYS.DBA_OB_TABLE_LOCATIONS WHERE TABLE_ID=table_id AND TABLE_TYPE='USER TABLE';