Query the tenant_id and table_id

In daily O&M, the tenant_id and table_id are the most commonly used information.

Query the tenant_id

If you know the tenant name and want to query the tenant_id of the tenant, you can query the DBA_OB_TENANTS view. Both the sys tenant and user tenant can query this view. The difference is that the sys tenant displays the information of all tenants (including the Meta tenant), while the user tenant displays only the information of the current tenant.

• Sys tenant

  In the sys tenant, you can execute the following statement to query the tenant_id.

  obclient> SELECT TENANT_ID, TENANT_NAME, TENANT_TYPE FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME='tanant_name';
  

• User tenant

  In a MySQL-compatible user tenant, you can execute the following statement to query the tenant_id.

  obclient> SELECT TENANT_ID, TENANT_NAME, TENANT_TYPE FROM oceanbase.DBA_OB_TENANTS;
  

  In an Oracle-compatible user tenant, you can execute the following statement to query the tenant_id.

  obclient> SELECT TENANT_ID, TENANT_NAME, TENANT_TYPE FROM SYS.DBA_OB_TENANTS;
  

In addition, the sys tenant and MySQL-compatible user tenant can call the effective_tenant_id() function to quickly obtain the tenant_id of the current tenant.

The statement is as follows:

obclient> SELECT effective_tenant_id();

Query the table_id

If you know the table name, you can query the table_id of the table from the CDB_OB_TABLE_LOCATIONS view (sys tenant) or the DBA_OB_TABLE_LOCATIONS view (user tenant).

• Sys tenant

  In the sys tenant, you can execute the following statement to query the table_id.

  obclient> SELECT DISTINCT TABLE_ID, DATABASE_NAME, TABLE_NAME, TABLE_TYPE FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TENANT_ID=tenant_id AND TABLE_NAME='table_name';
  

• User tenant

  In a MySQL-compatible user tenant, you can execute the following statement to query the table_id.

  obclient> SELECT DISTINCT TABLE_ID, DATABASE_NAME, TABLE_NAME, TABLE_TYPE FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE TABLE_NAME='table_name';
  

  In an Oracle-compatible user tenant, you can execute the following statement to query the table_id.

  obclient> SELECT DISTINCT TABLE_ID, DATABASE_NAME, TABLE_NAME, TABLE_TYPE FROM SYS.DBA_OB_TABLE_LOCATIONS WHERE TABLE_NAME='table_name';