The CDB_TABLES view records all tables, including temporary and non-temporary tables, for all tenants. You can use this view to count the number of temporary tables.
In the sys tenant, run the following statement to count the number of temporary tables in all tenants:
obclient> SELECT count(*), CON_ID FROM oceanbase.CDB_TABLES WHERE TEMPORARY = 'Y' GROUP BY CON_ID;
In this statement, CON_ID indicates the tenant_id of the tenant.
In an Oracle-compatible user tenant, if you only need to query the number of temporary tables in the current tenant, you can use the following statements to query the DBA_TABLES, ALL_TABLES, or USER_TABLES view:
obclient> SELECT count(*) FROM SYS.DBA_TABLES WHERE TEMPORARY = 'Y';
obclient> SELECT count(*) FROM SYS.ALL_TABLES WHERE TEMPORARY = 'Y';
obclient> SELECT count(*) FROM SYS.USER_TABLES WHERE TEMPORARY = 'Y';