Query tenant_id and table_id

In daily operations, tenant_id and table_id are the most commonly used pieces of information.

Query tenant_id

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

• Sys tenant

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

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

• User tenant

  In a MySQL-compatible user tenant, you can use 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 use the following statement to query the tenant_id.

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

In addition to the above methods, the sys tenant and MySQL-compatible user tenants can also use 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 table_id

If you know the table name, you can query the table_id of the table using 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 use 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 use 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 use 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';