View tenant and resource information|V4.3.0| docs|Distributed Database

View tenant and resource information

Last Updated：2024-04-19 08:42:49 Updated

This topic describes how to query views for information about tenants and resources.

OceanBase Database provides views related to tenants and resources. You can query these views for related information in the oceanbase database in the sys tenant.

DBA_OB_UNIT_CONFIGS: displays information about all unit configs, such as the ID, name, and configuration of each unit config.
DBA_OB_RESOURCE_POOLS: displays information about all resource pools, such as the ID, name, bound tenant, and zone list of each resource pool.
DBA_OB_TENANTS: displays information about all tenants, such as the ID, name, type, and resources of each tenant.
GV$OB_UNITS: displays information about resource units on each node, such as the node IP address, port number, unit config ID, tenant ID, and resource configuration.
GV$OB_SERVERS: displays the resource allocation information about nodes, such as the node IP address, port, target zone, and resource usage.

View information about all unit configs

You can query the DBA_OB_UNIT_CONFIGS view for information about all unit configs. Example:

In the second row of the query result, the name of the unit config is config_mysql001_zone1_S1_okz, the ID is 1001, the creation time is 2022-12-20 18:04:31, the maximum number of CPU cores is 1, the memory size is 6 GB, and the log disk size is 18 GB.

obclient [oceanbase]> SELECT * FROM DBA_OB_UNIT_CONFIGS;
+----------------+-------------------------------+----------------------------+----------------------------+---------+---------+-------------+---------------+----------+----------+-------------+
| UNIT_CONFIG_ID | NAME                          | CREATE_TIME                | MODIFY_TIME                | MAX_CPU | MIN_CPU | MEMORY_SIZE | LOG_DISK_SIZE | MAX_IOPS | MIN_IOPS | IOPS_WEIGHT |
+----------------+-------------------------------+----------------------------+----------------------------+---------+---------+-------------+---------------+----------+----------+-------------+
|              1 | sys_unit_config               | 2022-12-20 17:50:17.035504 | 2022-12-20 17:50:17.035504 |       1 |       1 | 14495514624 |   14495514624 |    10000 |    10000 |           1 |
|           1001 | config_mysql001_zone1_S1_okz  | 2022-12-20 18:04:31.547715 | 2022-12-20 18:04:31.547715 |     1.5 |     1.5 |  6442450944 |   19327352832 |    15000 |    15000 |           1 |
|           1002 | config_mysql001_zone2_S1_pme  | 2022-12-20 18:04:31.561335 | 2022-12-20 18:04:31.561335 |     1.5 |     1.5 |  6442450944 |   19327352832 |    15000 |    15000 |           1 |
|           1003 | config_mysql001_zone3_S1_jsu  | 2022-12-20 18:04:31.564510 | 2022-12-20 18:04:31.564510 |     1.5 |     1.5 |  6442450944 |   19327352832 |    15000 |    15000 |           1 |
|           1013 | config_oracle001_zone3_S1_exu | 2022-12-26 18:28:37.969047 | 2022-12-26 18:28:37.969047 |     1.5 |     1.5 |  6442450944 |   19327352832 |    15000 |    15000 |           1 |
|           1014 | config_oracle001_zone2_S1_hli | 2022-12-26 18:28:37.972194 | 2022-12-26 18:28:37.972194 |     1.5 |     1.5 |  6442450944 |   19327352832 |    15000 |    15000 |           1 |
|           1015 | config_oracle001_zone1_S1_owy | 2022-12-26 18:28:37.976446 | 2022-12-26 18:28:37.976446 |     1.5 |     1.5 |  6442450944 |   19327352832 |    15000 |    15000 |           1 |
+----------------+-------------------------------+----------------------------+----------------------------+---------+---------+-------------+---------------+----------+----------+-------------+
7 rows in set

For more information about the parameters in the DBA_OB_UNIT_CONFIGS view, see DBA_OB_UNIT_CONFIGS.

View information about all resource pools

You can query the DBA_OB_RESOURCE_POOLS view for information about all resource pools. Here is an example:

In the second row of the query result, the name of the resource pool is pool_mysql001_zone3_jsu, the resource pool ID is 1001, the tenant ID is 1002, the unit config ID is 1003, the number of resource units is 1, and the zone list is zone3.

obclient [oceanbase]> SELECT * FROM DBA_OB_RESOURCE_POOLS;
+------------------+--------------------------+-----------+----------------------------+----------------------------+------------+----------------+-------------------+--------------+
| RESOURCE_POOL_ID | NAME                     | TENANT_ID | CREATE_TIME                | MODIFY_TIME                | UNIT_COUNT | UNIT_CONFIG_ID | ZONE_LIST         | REPLICA_TYPE |
+------------------+--------------------------+-----------+----------------------------+----------------------------+------------+----------------+-------------------+--------------+
|                1 | sys_pool                 |         1 | 2022-12-20 17:50:17.038641 | 2022-12-20 17:50:17.045453 |          1 |              1 | zone1;zone2;zone3 | FULL         |
|             1001 | pool_mysql001_zone3_jsu  |      1002 | 2022-12-20 18:04:31.607227 | 2022-12-20 18:04:31.692836 |          1 |           1003 | zone3             | FULL         |
|             1002 | pool_mysql001_zone1_okz  |      1002 | 2022-12-20 18:04:31.617087 | 2022-12-20 18:04:31.691827 |          1 |           1001 | zone1             | FULL         |
|             1003 | pool_mysql001_zone2_pme  |      1002 | 2022-12-20 18:04:31.621327 | 2022-12-20 18:04:31.692836 |          1 |           1002 | zone2             | FULL         |
|             1013 | pool_oracle001_zone3_exu |      1010 | 2022-12-26 18:28:37.979539 | 2022-12-26 18:28:38.059505 |          1 |           1013 | zone3             | FULL         |
|             1014 | pool_oracle001_zone1_owy |      1010 | 2022-12-26 18:28:37.988964 | 2022-12-26 18:28:38.058440 |          1 |           1015 | zone1             | FULL         |
|             1015 | pool_oracle001_zone2_hli |      1010 | 2022-12-26 18:28:37.994241 | 2022-12-26 18:28:38.059505 |          1 |           1014 | zone2             | FULL         |
+------------------+--------------------------+-----------+----------------------------+----------------------------+------------+----------------+-------------------+--------------+
7 rows in set

For more information about the parameters in the DBA_OB_RESOURCE_POOLSview, see DBA_OB_RESOURCE_POOLS.

View information of all tenants

You can query the DBA_OB_TENANTS view for information about all tenants. Here is an example:

In the third row of the query result, the tenant name is mysql001, the tenant ID is 1002, the tenant type is USER, the creation time is 2023-05-17 18:15:21, the primary zones are zone1 (with a higher priority) and zone2, the locality is zone1, zone2, and zone3, and the compatibility mode is MySQL.

obclient [oceanbase]> SELECT * FROM DBA_OB_TENANTS;
+-----------+-------------+-------------+----------------------------+----------------------------+--------------+---------------------------------------------+-------------------+--------------------+--------+---------------+--------+-------------+-------------------+------------------+---------------------+---------------------+---------------------+---------------------+--------------+----------------------------+
| TENANT_ID | TENANT_NAME | TENANT_TYPE | CREATE_TIME                | MODIFY_TIME                | PRIMARY_ZONE | LOCALITY                                    | PREVIOUS_LOCALITY | COMPATIBILITY_MODE | STATUS | IN_RECYCLEBIN | LOCKED | TENANT_ROLE | SWITCHOVER_STATUS | SWITCHOVER_EPOCH | SYNC_SCN            | REPLAYABLE_SCN      | READABLE_SCN        | RECOVERY_UNTIL_SCN  | LOG_MODE     | ARBITRATION_SERVICE_STATUS |
+-----------+-------------+-------------+----------------------------+----------------------------+--------------+---------------------------------------------+-------------------+--------------------+--------+---------------+--------+-------------+-------------------+------------------+---------------------+---------------------+---------------------+---------------------+--------------+----------------------------+
|         1 | sys         | SYS         | 2023-05-17 18:10:19.940353 | 2023-05-17 18:10:19.940353 | RANDOM       | FULL{1}@zone1                               | NULL              | MYSQL              | NORMAL | NO            | NO     | PRIMARY     | NORMAL            |                0 |                NULL |                NULL |                NULL |                NULL | NOARCHIVELOG | DISABLED                   |
|      1001 | META$1002   | META        | 2023-05-17 18:15:21.455549 | 2023-05-17 18:15:36.639479 | zone1;zone2  | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | NULL              | MYSQL              | NORMAL | NO            | NO     | PRIMARY     | NORMAL            |                0 |                NULL |                NULL |                NULL |                NULL | NOARCHIVELOG | DISABLED                   |
|      1002 | mysql001    | USER        | 2023-05-17 18:15:21.461276 | 2023-05-17 18:15:36.669988 | zone1;zone2  | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | NULL              | MYSQL              | NORMAL | NO            | NO     | PRIMARY     | NORMAL            |                0 | 1684395321137516636 | 1684395321137516636 | 1684395321052204807 | 4611686018427387903 | NOARCHIVELOG | DISABLED                   |
|      1003 | META$1004   | META        | 2023-05-17 18:18:19.927859 | 2023-05-17 18:18:36.443233 | zone1        | FULL{1}@zone1                               | NULL              | MYSQL              | NORMAL | NO            | NO     | PRIMARY     | NORMAL            |                0 |                NULL |                NULL |                NULL |                NULL | NOARCHIVELOG | DISABLED                   |
|      1004 | oracle001   | USER        | 2023-05-17 18:18:19.928914 | 2023-05-17 18:18:36.471606 | zone1        | FULL{1}@zone1                               | NULL              | ORACLE             | NORMAL | NO            | NO     | PRIMARY     | NORMAL            |                0 | 1684395321137558760 | 1684395321137558760 | 1684395320951813345 | 4611686018427387903 | NOARCHIVELOG | DISABLED                   |
+-----------+-------------+-------------+----------------------------+----------------------------+--------------+---------------------------------------------+-------------------+--------------------+--------+---------------+--------+-------------+-------------------+------------------+---------------------+---------------------+---------------------+---------------------+--------------+----------------------------+
5 rows in set

For more information about the parameters in the DBA_OB_TENANTS view, see DBA_OB_TENANTS.

View the resource configurations of tenants

You can execute the following statement to query multiple views for resource configurations of tenants.

For the mysql001 tenant in the query result, the pool:conf column shows the names of the three resource pools and unit configs for the tenant. The unit_info column shows that each resource pool of the tenant has one resource unit, 1.5 CPU cores, and 6 GB of memory.

obclient [oceanbase]> SELECT c.TENANT_ID, e.TENANT_NAME, concat(c.NAME, ': ', d.NAME) `pool:conf`,concat(c.UNIT_COUNT, ' unit: ', d.min_cpu, 'C/', ROUND(d.MEMORY_SIZE/1024/1024/1024,0), "G") unit_info
                FROM DBA_OB_RESOURCE_POOLS c, DBA_OB_UNIT_CONFIGS d, DBA_OB_TENANTS e
                WHERE c.UNIT_CONFIG_ID=d.UNIT_CONFIG_ID AND c.TENANT_ID=e.TENANT_ID AND c.TENANT_ID>1000
                ORDER BY c.TENANT_ID;
+-----------+-------------+---------------------------------------------------------+-----------------+
| TENANT_ID | TENANT_NAME | pool:conf                                               | unit_info       |
+-----------+-------------+---------------------------------------------------------+-----------------+
|      1002 | mysql001    | pool_mysql001_zone1_okz: config_mysql001_zone1_S1_okz   | 1 unit: 1.5C/6G |
|      1002 | mysql001    | pool_mysql001_zone2_pme: config_mysql001_zone2_S1_pme   | 1 unit: 1.5C/6G |
|      1002 | mysql001    | pool_mysql001_zone3_jsu: config_mysql001_zone3_S1_jsu   | 1 unit: 1.5C/6G |
|      1010 | oracle001   | pool_oracle001_zone3_exu: config_oracle001_zone3_S1_exu | 1 unit: 1.5C/6G |
|      1010 | oracle001   | pool_oracle001_zone2_hli: config_oracle001_zone2_S1_hli | 1 unit: 1.5C/6G |
|      1010 | oracle001   | pool_oracle001_zone1_owy: config_oracle001_zone1_S1_owy | 1 unit: 1.5C/6G |
+-----------+-------------+---------------------------------------------------------+-----------------+
6 rows in set

View the deployment locations of the resource units of tenants

Execute the following statement to view the IP addresses of the servers where the resource units of tenants are located.

The query result shows the ID of each tenant and the IP addresses of the nodes where its resources are located.

obclient [oceanbase]> SELECT a.TENANT_NAME,a.TENANT_ID,b.SVR_IP FROM DBA_OB_TENANTS a,GV$OB_UNITS b WHERE a.TENANT_ID=b.TENANT_ID;
+-------------+-----------+----------------+
| TENANT_NAME | TENANT_ID | SVR_IP         |
+-------------+-----------+----------------+
| sys         |         1 | xx.xx.xx.237   |
| META$1002   |      1001 | xx.xx.xx.237   |
| mysql001    |      1002 | xx.xx.xx.237   |
| META$1010   |      1009 | xx.xx.xx.237   |
| oracle001   |      1010 | xx.xx.xx.237   |
| sys         |         1 | xx.xx.xx.238   |
| META$1002   |      1001 | xx.xx.xx.238   |
| mysql001    |      1002 | xx.xx.xx.238   |
| META$1010   |      1009 | xx.xx.xx.238   |
| oracle001   |      1010 | xx.xx.xx.238   |
| sys         |         1 | xx.xx.xx.218   |
| META$1002   |      1001 | xx.xx.xx.218   |
| mysql001    |      1002 | xx.xx.xx.218   |
| META$1010   |      1009 | xx.xx.xx.218   |
| oracle001   |      1010 | xx.xx.xx.218   |
+-------------+-----------+----------------+
15 rows in set

View information about resource units on nodes

You can query the GV$OB_UNITS view for the information about resource units on all nodes. Here is an example:

In the third row of the query result, the node IP address is xx.xx.xx.218, the server port is 2882, the resource unit ID is 1001, the tenant ID is 1002, the zone name is zone3, the maximum number of CPU cores is 1.5, the memory size is 5 GB, the log disk size is 16 GB, 1.07 GB of log disk space is used, 572 MB of data disk space is used, and the status of the resource unit is normal.
The value of the SVR_IP column is xx.xx.xx.218 in five rows. This indicates that this node has five resource units that respectively belong to tenants with the IDs 1, 1001, 1002, 1009, and 1010.
The value of the TENANT_ID column is 1002 in three rows. This indicates that this tenant has three resource units with the IDs 1001, 1002, and 1003 that are respectively located on three nodes.

obclient [oceanbase]> SELECT * FROM GV$OB_UNITS;
+----------------+----------+---------+-----------+-------+-----------+----------------+---------+---------+-------------+----------+----------+-------------+---------------+-----------------+------------------+--------+----------------------------+
| SVR_IP         | SVR_PORT | UNIT_ID | TENANT_ID | ZONE  | ZONE_TYPE | REGION         | MAX_CPU | MIN_CPU | MEMORY_SIZE | MAX_IOPS | MIN_IOPS | IOPS_WEIGHT | LOG_DISK_SIZE | LOG_DISK_IN_USE | DATA_DISK_IN_USE | STATUS | CREATE_TIME                |
+----------------+----------+---------+-----------+-------+-----------+----------------+---------+---------+-------------+----------+----------+-------------+---------------+-----------------+------------------+--------+----------------------------+
| xx.xx.xx.218   |     2882 |       3 |         1 | zone3 | ReadWrite | default_region |       1 |       1 | 14495514624 |    10000 |    10000 |           1 |   14495514624 |     11566462821 |       6069157888 | NORMAL | 2022-12-20 17:49:25.705363 |
| xx.xx.xx.218   |     2882 |    1001 |      1001 | zone3 | ReadWrite | default_region |   NULL  |    NULL |  1073741824 |     NULL |     NULL |        NULL |    1932735283 |      1520602567 |       7325351936 | NORMAL | 2022-12-20 18:04:31.693819 |
| xx.xx.xx.218   |     2882 |    1001 |      1002 | zone3 | ReadWrite | default_region |     1.5 |     1.5 |  5368709120 |    15000 |    15000 |           1 |   17394617549 |      1154755905 |        599785472 | NORMAL | 2022-12-20 18:04:31.693819 |
| xx.xx.xx.218   |     2882 |    1013 |      1009 | zone3 | ReadWrite | default_region |    NULL |    NULL |  1073741824 |     NULL |     NULL |        NULL |    1932735283 |      1495227415 |       5368709120 | NORMAL | 2022-12-26 18:28:38.060661 |
| xx.xx.xx.218   |     2882 |    1013 |      1010 | zone3 | ReadWrite | default_region |     1.5 |     1.5 |  5368709120 |    15000 |    15000 |           1 |   17394617549 |      3532346784 |        681574400 | NORMAL | 2022-12-26 18:28:38.060661 |
| xx.xx.xx.237   |     2882 |       1 |         1 | zone1 | ReadWrite | default_region |       1 |       1 | 14495514624 |    10000 |    10000 |           1 |   14495514624 |     11566525317 |       6071255040 | NORMAL | 2022-12-20 17:49:25.705315 |
| xx.xx.xx.237   |     2882 |    1002 |      1001 | zone1 | ReadWrite | default_region |    NULL |    NULL |  1073741824 |     NULL |     NULL |        NULL |    1932735283 |      1520680069 |       7325351936 | NORMAL | 2022-12-20 18:04:31.692599 |
| xx.xx.xx.237   |     2882 |    1002 |      1002 | zone1 | ReadWrite | default_region |     1.5 |     1.5 |  5368709120 |    15000 |    15000 |           1 |   17394617549 |      4845753295 |        595591168 | NORMAL | 2022-12-20 18:04:31.692599 |
| xx.xx.xx.237   |     2882 |    1014 |      1009 | zone1 | ReadWrite | default_region |    NULL |    NULL |  1073741824 |     NULL |     NULL |        NULL |    1932735283 |      1495304058 |       5391777792 | NORMAL | 2022-12-26 18:28:38.059317 |
| xx.xx.xx.237   |     2882 |    1014 |      1010 | zone1 | ReadWrite | default_region |     1.5 |     1.5 |  5368709120 |    15000 |    15000 |           1 |   17394617549 |      3532357328 |        681574400 | NORMAL | 2022-12-26 18:28:38.059317 |
| xx.xx.xx.238   |     2882 |       2 |         1 | zone2 | ReadWrite | default_region |       1 |       1 | 14495514624 |    10000 |    10000 |           1 |   14495514624 |     11566567057 |       5947523072 | NORMAL | 2022-12-20 17:49:25.705354 |
| xx.xx.xx.238   |     2882 |    1003 |      1001 | zone2 | ReadWrite | default_region |    NULL |    NULL |  1073741824 |     NULL |     NULL |        NULL |    1932735283 |      1520720157 |       7337934848 | NORMAL | 2022-12-20 18:04:31.693277 |
| xx.xx.xx.238   |     2882 |    1003 |      1002 | zone2 | ReadWrite | default_region |    1.5  |     1.5 |  5368709120 |    15000 |    15000 |           1 |   17394617549 |      4845759221 |        591396864 | NORMAL | 2022-12-20 18:04:31.693277 |
| xx.xx.xx.238   |     2882 |    1015 |      1009 | zone2 | ReadWrite | default_region |    NULL |    NULL |  1073741824 |     NULL |     NULL |        NULL |    1932735283 |      1495346552 |       5364514816 | NORMAL | 2022-12-26 18:28:38.060042 |
| xx.xx.xx.238   |     2882 |    1015 |      1010 | zone2 | ReadWrite | default_region |     1.5 |     1.5 |  5368709120 |    15000 |    15000 |           1 |   17394617549 |      3532362696 |        681574400 | NORMAL | 2022-12-26 18:28:38.060042 |
+----------------+----------+---------+-----------+-------+-----------+----------------+---------+---------+-------------+----------+----------+-------------+---------------+-----------------+------------------+--------+----------------------------+
15 rows in set

For more information about the parameters in the GV$OB_UNITS view, see GV$OB_UNITS.

View information about resource allocation on nodes

You can query the GV$OB_SERVERS view for information about resource allocation on OBServer nodes. Example:

In the first row of the query result, the node IP address is xx.xx.xx.238, the server port is 2882, the zone name is zone2, the SQL port is 2881, the number of CPU cores for the observer process is 64, the memory available for the observer process is 54 GB, 27.5 GB of memory is allocated, the total log disk space is 167.25 GB, 55 GB of the log disk space is allocated, 22 GB of the log disk space is used, the total data disk space is 167.25 GB, and 18.89 GB of the data disk space is used.

obclient [oceanbase]> SELECT * FROM GV$OB_SERVERS;
+----------------+----------+-------+----------+--------------+------------------+--------------+------------------+--------------+--------------+-------------------+-------------------+-----------------+--------------------+------------------+-------------------------+--------------+-------------------------+-----------------------+
| SVR_IP         | SVR_PORT | ZONE  | SQL_PORT | CPU_CAPACITY | CPU_CAPACITY_MAX | CPU_ASSIGNED | CPU_ASSIGNED_MAX | MEM_CAPACITY | MEM_ASSIGNED | LOG_DISK_CAPACITY | LOG_DISK_ASSIGNED | LOG_DISK_IN_USE | DATA_DISK_CAPACITY | DATA_DISK_IN_USE | DATA_DISK_HEALTH_STATUS | MEMORY_LIMIT | DATA_DISK_ABNORMAL_TIME | SSL_CERT_EXPIRED_TIME |
+----------------+----------+-------+----------+--------------+------------------+--------------+------------------+--------------+--------------+-------------------+-------------------+-----------------+--------------------+------------------+-------------------------+--------------+-------------------------+-----------------------+
| xx.xx.xx.238   |     2882 | zone2 |     2881 |           64 |               64 |            5 |                5 |  57982058496 |  29527900160 |      179583320064 |       59592671232 |     23622320128 |       179593805824 |      20283654144 | NORMAL                  |  68719476736 | NULL                    | NULL                  |
| xx.xx.xx.237   |     2882 | zone1 |     2881 |           64 |               64 |            5 |                5 |  32212254720 |  29527900160 |      179583320064 |       59592671232 |     23622320128 |       179593805824 |      20443037696 | NORMAL                  |  85899345920 | NULL                    | NULL                  |
| xx.xx.xx.218   |     2882 | zone3 |     2881 |           64 |               64 |            4 |                4 |  57982058496 |  27380416512 |      179583320064 |       53150220288 |     19528679424 |       179593805824 |      20044578816 | NORMAL                  |  68719476736 | NULL                    | NULL                  |
+----------------+----------+-------+----------+--------------+------------------+--------------+------------------+--------------+--------------+-------------------+-------------------+-----------------+--------------------+------------------+-------------------------+--------------+-------------------------+-----------------------+
3 rows in set

For more information about parameters in the GV$OB_SERVERS view, see GV$OB_SERVERS.