17认证网在进行数据库集群性能诊断时，可以用已创建好的白屏监控工具 OCP_Express 或 Prometheus 等对数据库进行性能监控，也可以通过黑屏操作命令来查看当前集群的性能数据，对集群的性能进行诊断。
正如大家所知，诊断集群性能的几个主要指标为 CPU、内存、IO、磁盘等。可以通过这几个指标的使用情况来诊断集群的性能。当这些指标较高时，会影响到集群的性能，需要针对不同的情况进行处理。
-
当 CPU 占用较高时，一般可以排查数据库正在执行的 SQL，通过对 SQL 限流或者进行 SQL 优化等方法来降低 CPU 的占用。 -
当内存占用较高时，可以如下方法降低内存的占用。 -
扩大租户内存。 -
调高转储线程数，加快转储，让内存尽快释放。 -
当集群已经无法扩容租户总内存时，可调高租户 memstore 的比例，临时扩大可写入内存，并且调低转储阈值，让转储更快发生。 -
开启写入限速，内存写入达到一定阈值 OB 会主动限制客户端导入速度。 -
应用侧降级批处理任务。
-
-
当 IO 占用比较高时，可以通过如下可行方法降低 IO 的占用。 -
暂停进行中的合并。 -
暂停执行中的备份任务。 -
暂停执行中的数据传输或导入/导出任务。 -
调低转储线程数。 -
调低迁移复制的并发数。 -
调低后台任务的网络带宽。 -
对高负载的 SQL 执行限流或 SQL优化。 -
取消进行中的索引创建等。
-
-
当业务数据占用磁盘比较高时，可以通过扩容等手段来降低业务数据占用磁盘的比例。 -
当日志数据占用磁盘比较高时，可以通过过期日志清理等手段来降低日志数据占用磁盘的比例。
连接集群
obclient -h 127.0.0.1 -uroot@sys -p'******' -Doceanbase -A
查看资源分配和使用情况
获取 OBServer CPU、内存，磁盘资源使用情况
通过如下 SQL，可以查看当前集群中 observer 主机的资源使用情况。
select
svr_ip as ip,
CONCAT(cpu_capacity_max, 'C') as cpu_total,
CONCAT(cpu_assigned_max, 'C') as cpu_assigned,
CONCAT(TRUNCATE(mem_capacity / 1024 / 1024 / 1024, 2), 'G') as mem_total,
CONCAT(TRUNCATE(mem_assigned / 1024 / 1024 / 1024, 2), 'G') as mem_assigned,
CONCAT(TRUNCATE(data_disk_capacity / 1024 / 1024 / 1024, 2),'G') as total_data_size,
CONCAT(TRUNCATE(log_disk_capacity / 1024 / 1024 / 1024, 2), 'G') as total_log_size,
CONCAT(TRUNCATE((cpu_assigned_max / cpu_capacity_max) * 100, 2),'%') as cpu_assigned_percent,
CONCAT(TRUNCATE((mem_assigned / mem_capacity) * 100, 2),'%') as mem_assigned_percent,
CONCAT(TRUNCATE((data_disk_in_use / data_disk_capacity) * 100, 2),'%') as used_data_percent,
CONCAT(TRUNCATE((log_disk_in_use / log_disk_capacity) * 100, 2),'%') as used_log_percent
from
oceanbase.GV$OB_SERVERS;
从结果可以看出（具体数据以实际返回结果为准），当前主机的 CPU 已分配 100%，内存已分配 100%，数据盘已使用 0.06%，日志盘已使用 2.96%。
obclient(root@sys)[oceanbase]> select
-> svr_ip as ip,
-> CONCAT(cpu_capacity_max, 'C') as cpu_total,
-> CONCAT(cpu_assigned_max, 'C') as cpu_assigned,
-> CONCAT(TRUNCATE(mem_capacity / 1024 / 1024 / 1024, 2), 'G') as mem_total,
-> CONCAT(TRUNCATE(mem_assigned / 1024 / 1024 / 1024, 2), 'G') as mem_assigned,
-> CONCAT(TRUNCATE(data_disk_capacity / 1024 / 1024 / 1024, 2),'G') as total_data_size,
-> CONCAT(TRUNCATE(log_disk_capacity / 1024 / 1024 / 1024, 2), 'G') as total_log_size,
-> CONCAT(TRUNCATE((cpu_assigned_max / cpu_capacity_max) * 100, 2),'%') as cpu_assigned_percent,
-> CONCAT(TRUNCATE((mem_assigned / mem_capacity) * 100, 2),'%') as mem_assigned_percent,
-> CONCAT(TRUNCATE((data_disk_in_use / data_disk_capacity) * 100, 2),'%') as used_data_percent,
-> CONCAT(TRUNCATE((log_disk_in_use / log_disk_capacity) * 100, 2),'%') as used_log_percent
-> from
-> oceanbase.GV$OB_SERVERS;
+-----------+-----------+--------------+-----------+--------------+-----------------+----------------+----------------------+----------------------+-------------------+------------------+
| ip | cpu_total | cpu_assigned | mem_total | mem_assigned | total_data_size | total_log_size | cpu_assigned_percent | mem_assigned_percent | used_data_percent | used_log_percent |
+-----------+-----------+--------------+-----------+--------------+-----------------+----------------+----------------------+----------------------+-------------------+------------------+
| 127.0.0.1 | 8C | 8C | 6.00G | 6.00G | 139.00G | 19.00G | 100% | 100.00% | 0.06% | 2.96% |
+-----------+-----------+--------------+-----------+--------------+-----------------+----------------+----------------------+----------------------+-------------------+------------------+
1 row in set (0.008 sec)
obclient(root@sys)[oceanbase]>
查询集群业务数据磁盘使用情况
通过如下 SQL，可以查看当前集群业务数据磁盘的使用情况。
select
CONCAT(TRUNCATE(data_disk_capacity/1024/1024/1024,2),'G') as total_data_size,
CONCAT(TRUNCATE(data_disk_in_use/1024/1024/1024,2),'G') as used_data_size,
CONCAT(TRUNCATE((data_disk_in_use/data_disk_capacity) * 100, 2),'%') as used_data_percent,
CONCAT(TRUNCATE((data_disk_capacity - data_disk_in_use)/1024/1024/1024,2),'G') as free_data_size,
CONCAT(TRUNCATE(((data_disk_capacity - data_disk_in_use)/data_disk_capacity)*100,2),'%') as free_data_percent
from
oceanbase.GV$OB_SERVERS;
从结果可以看出（具体数据以实际返回结果为准），当前集群总的数据盘 139.00G，已使用的数据盘为 0.08G，已使用的数据盘占总量的 0.06%，剩余的数据盘为 138.91G，剩余的数据盘占总量的 99.93%。
obclient(root@sys)[oceanbase]> select
-> CONCAT(TRUNCATE(data_disk_capacity/1024/1024/1024,2),'G') as total_data_size,
-> CONCAT(TRUNCATE(data_disk_in_use/1024/1024/1024,2),'G') as used_data_size,
-> CONCAT(TRUNCATE((data_disk_in_use/data_disk_capacity) * 100, 2),'%') as used_data_percent,
-> CONCAT(TRUNCATE((data_disk_capacity - data_disk_in_use)/1024/1024/1024,2),'G') as free_data_size,
-> CONCAT(TRUNCATE(((data_disk_capacity - data_disk_in_use)/data_disk_capacity)*100,2),'%') as free_data_percent
-> from
-> oceanbase.GV$OB_SERVERS;
+-----------------+----------------+-------------------+----------------+-------------------+
| total_data_size | used_data_size | used_data_percent | free_data_size | free_data_percent |
+-----------------+----------------+-------------------+----------------+-------------------+
| 139.00G | 0.08G | 0.06% | 138.91G | 99.93% |
+-----------------+----------------+-------------------+----------------+-------------------+
1 row in set (0.003 sec)
obclient(root@sys)[oceanbase]>
查询集群日志磁盘使用情况
通过如下 SQL，可以查看当前集群日志数据磁盘的使用情况。
select
CONCAT(TRUNCATE(log_disk_capacity/1024/1024/1024,2),'G') as total_log_size,
CONCAT(TRUNCATE(log_disk_in_use/1024/1024/1024,2),'G') as used_log_size,
CONCAT(TRUNCATE(log_disk_assigned/1024/1024/1024,2),'G') as assigned_log_size,
CONCAT(TRUNCATE((log_disk_in_use/log_disk_capacity) * 100, 2),'%') as used_log_percent,
CONCAT(TRUNCATE((log_disk_capacity - log_disk_in_use)/1024/1024/1024,2),'G') as free_log_size,
CONCAT(TRUNCATE(((log_disk_capacity - log_disk_in_use)/log_disk_capacity)*100,2),'%') as free_log_percent
from
oceanbase.GV$OB_SERVERS;
从结果可以看出（具体数据以实际返回结果为准），当前集群总的日志盘 19.00G，已使用的日志盘为 0.56G，已分配的日志盘为 19.00G，剩余日志盘为 18.43G，剩余日志盘占总量的 97.03%。
obclient(root@sys)[oceanbase]> select
-> CONCAT(TRUNCATE(log_disk_capacity/1024/1024/1024,2),'G') as total_log_size,
-> CONCAT(TRUNCATE(log_disk_in_use/1024/1024/1024,2),'G') as used_log_size,
-> CONCAT(TRUNCATE(log_disk_assigned/1024/1024/1024,2),'G') as assigned_log_size,
-> CONCAT(TRUNCATE((log_disk_in_use/log_disk_capacity) * 100, 2),'%') as used_log_percent,
-> CONCAT(TRUNCATE((log_disk_capacity - log_disk_in_use)/1024/1024/1024,2),'G') as free_log_size,
-> CONCAT(TRUNCATE(((log_disk_capacity - log_disk_in_use)/log_disk_capacity)*100,2),'%') as free_log_percent
-> from
-> oceanbase.GV$OB_SERVERS;
+----------------+---------------+-------------------+------------------+---------------+------------------+
| total_log_size | used_log_size | assigned_log_size | used_log_percent | free_log_size | free_log_percent |
+----------------+---------------+-------------------+------------------+---------------+------------------+
| 19.00G | 0.56G | 19.00G | 2.96% | 18.43G | 97.03% |
+----------------+---------------+-------------------+------------------+---------------+------------------+
1 row in set (0.003 sec)
obclient(root@sys)[oceanbase]>
查看占 CPU 较高的 OBServer 和租户信息
若查到 CPU 使用比较高，也可以通过如下 SQL 定位使用超过 10 个 CPU 的主机和租户。
select * from gv$sysstat where con_id>1000 and name = 'cpu usage' and value > 1000;
根据结果可以看出（具体数据以实际返回结果为准），租户 id 为 1101 的租户，占用了比较高的 CPU。这里，cpu usage 中每 100 代表 1 个 CPU，本示例中，cpu usage 的 VALUE 为 1020 ，故相当于使用了 10.2 个 CPU，负载很大时可能会超过分配核数。
+--------+----------------+----------+------------+-----------+-------+-------+------------+---------+
| CON_ID | SVR_IP | SVR_PORT | STATISTIC# | NAME | CLASS | VALUE | VALUE_TYPE | STAT_ID |
+--------+----------------+----------+------------+-----------+-------+-------+------------+---------+
| 1101 | 10.xxx.xxx.xxx | 2882 | 370 | cpu usage | 64 | 1020 | SET_VALUE | 140006 |
+--------+----------------+----------+------------+-----------+-------+-------+------------+---------+
1 rows in set
获取各租户业务数据磁盘占用情况
当查到业务数据盘占用比较高时，可以用如下 SQL，查看不同的租户的业务数据的磁盘占用情况。
select
TENANT_ID,
CONCAT(TRUNCATE(LOG_DISK_SIZE/1024/1024/1024,2),'G') as total_data_size,
CONCAT(TRUNCATE(LOG_DISK_IN_USE/1024/1024/1024,2),'G') as used_data_size,
CONCAT(TRUNCATE((LOG_DISK_IN_USE/LOG_DISK_SIZE) * 100, 2),'%') as used_data_percent,
CONCAT(TRUNCATE((LOG_DISK_SIZE - LOG_DISK_IN_USE)/1024/1024/1024,2),'G') as free_data_size,
CONCAT(TRUNCATE(((LOG_DISK_SIZE - LOG_DISK_IN_USE)/LOG_DISK_SIZE)*100,2),'%') as free_data_percent
from
oceanbase.GV$OB_UNITS;
从结果可以看出（具体数据以实际返回结果为准），不同的租户使用业务数据盘的情况。以 TENANT_ID 为 1002 的租户为例，从查询结果可以看出，其分配的业务数据盘为 15.30G，已使用 0.17G，剩余 15.12G，剩余占比 98.86%。
obclient(root@sys)[oceanbase]> select
-> TENANT_ID,
-> CONCAT(TRUNCATE(LOG_DISK_SIZE/1024/1024/1024,2),'G') as total_data_size,
-> CONCAT(TRUNCATE(LOG_DISK_IN_USE/1024/1024/1024,2),'G') as used_data_size,
-> CONCAT(TRUNCATE((LOG_DISK_IN_USE/LOG_DISK_SIZE) * 100, 2),'%') as used_data_percent,
-> CONCAT(TRUNCATE((LOG_DISK_SIZE - LOG_DISK_IN_USE)/1024/1024/1024,2),'G') as free_data_size,
-> CONCAT(TRUNCATE(((LOG_DISK_SIZE - LOG_DISK_IN_USE)/LOG_DISK_SIZE)*100,2),'%') as free_data_percent
-> from
-> oceanbase.GV$OB_UNITS;
+-----------+-----------------+----------------+-------------------+----------------+-------------------+
| TENANT_ID | total_data_size | used_data_size | used_data_percent | free_data_size | free_data_percent |
+-----------+-----------------+----------------+-------------------+----------------+-------------------+
| 1 | 2.00G | 0.15G | 7.96% | 1.84G | 92.03% |
| 1001 | 1.69G | 0.10G | 6.40% | 1.59G | 93.59% |
| 1002 | 15.30G | 0.17G | 1.13% | 15.12G | 98.86% |
+-----------+-----------------+----------------+-------------------+----------------+-------------------+
3 rows in set (0.003 sec)
obclient(root@sys)[oceanbase]>
查询各租户的 CPU、内存、数据盘和日志盘使用情况
可以使用如下 SQL，查询具体的 CPU、内存、数据盘和日志盘使用情况。
SELECT
COALESCE(t1.TENANT_ID, -1) as TENANT_ID,
TENANT_NAME,
SUM(MAX_CPU) AS MAX_CPU,
SUM(MIN_CPU) AS MIN_CPU,
SUM(MAX_MEMORY) AS MAX_MEMORY,
SUM(MIN_MEMORY) AS MIN_MEMORY,
SUM(MAX_IOPS) AS MAX_IOPS,
SUM(MIN_IOPS) AS MIN_IOPS,
SUM(DATA_DISK_IN_USE) AS DATA_DISK_IN_USE,
SUM(LOG_DISK_IN_USE) AS LOG_DISK_IN_USE
FROM
(
SELECT
T1.UNIT_ID,
T1.SVR_IP,
T1.SVR_PORT,
T1.ZONE,
T2.TENANT_ID,
T1.MIN_CPU,
T1.MAX_CPU,
T1.MIN_MEMORY,
T1.MAX_MEMORY,
T1.MIN_IOPS,
T1.MAX_IOPS,
T1.IOPS_WEIGHT,
T1.DATA_DISK_IN_USE,
T1.LOG_DISK_IN_USE
FROM
(
SELECT
UNIT_ID,
SVR_IP,
SVR_PORT,
ZONE,
SUM(MIN_CPU) AS MIN_CPU,
SUM(MAX_CPU) AS MAX_CPU,
SUM(MEMORY_SIZE) AS MIN_MEMORY,
SUM(MEMORY_SIZE) AS MAX_MEMORY,
SUM(MIN_IOPS) AS MIN_IOPS,
SUM(MAX_IOPS) AS MAX_IOPS,
SUM(IOPS_WEIGHT) AS IOPS_WEIGHT,
SUM(DATA_DISK_IN_USE) AS DATA_DISK_IN_USE,
SUM(LOG_DISK_IN_USE) AS LOG_DISK_IN_USE
FROM
oceanbase.GV$OB_UNITS
GROUPBY
UNIT_ID
) T1
JOIN oceanbase.DBA_OB_UNITS T2 ON T1.UNIT_ID = T2.UNIT_ID
) t1
JOIN oceanbase.DBA_OB_TENANTS t2 ON t1.TENANT_ID = t2.TENANT_ID
WHERE
TENANT_TYPE IN ('SYS', 'USER')
GROUPBY
TENANT_ID;
从结果可以看出（具体数据以实际返回结果为准），mysql_tenant 租户详细的资源使用情况。
+-----------+--------------+---------+---------+------------+------------+---------------------+---------------------+------------------+-----------------+
| TENANT_ID | TENANT_NAME | MAX_CPU | MIN_CPU | MAX_MEMORY | MIN_MEMORY | MAX_IOPS | MIN_IOPS | DATA_DISK_IN_USE | LOG_DISK_IN_USE |
+-----------+--------------+---------+---------+------------+------------+---------------------+---------------------+------------------+-----------------+
| 1 | sys | 2 | 2 | 1073741824 | 1073741824 | 9223372036854775807 | 9223372036854775807 | 0 | 157121192 |
| 1002 | mysql_tenant | 6 | 6 | 5368709120 | 5368709120 | 9223372036854775807 | 9223372036854775807 | 0 | 257806690 |
+-----------+--------------+---------+---------+------------+------------+---------------------+---------------------+------------------+-----------------+
2 rows in set (0.040 sec)
obclient(root@sys)[oceanbase]>
查看 OBServer 运行情况
可以使用如下 SQL，查询 OBServer 运行情况。
SELECT
zone,
svr_ip,
svr_port,
sql_port AS inner_port,
with_rootserver,
UPPER(`status`) AS`status`,
build_version,
stop_time * 1000000AS stop_time,
start_service_time * 1000000AS start_service_time,
last_offline_time * 1000000AS last_offline_time,
block_migrate_in_time * 1000000AS block_migrate_in_time
FROM
oceanbase.DBA_OB_SERVERS;
从结果可以看出，该 OBServer 属于 zone1，IP 为 127.0.0.1，服务端口为 2882，inner 端口 为 2881，当前状态是 active，版本号为 V4.2.5，开启服务的时间是 2025 年 7 月 11 号。
obclient(root@sys)[oceanbase]> SELECT
-> zone,
-> svr_ip,
-> svr_port,
-> sql_port AS inner_port,
-> with_rootserver,
-> UPPER(`status`) AS`status`,
-> build_version,
-> stop_time * 1000000AS stop_time,
-> start_service_time * 1000000AS start_service_time,
-> last_offline_time * 1000000AS last_offline_time,
-> block_migrate_in_time * 1000000AS block_migrate_in_time
-> FROM
-> oceanbase.DBA_OB_SERVERS;
+-------+-----------+----------+------------+-----------------+--------+-------------------------------------------------------------------------------------------+-----------+-----------------------------+-------------------+-----------------------+
| zone | svr_ip | svr_port | inner_port | with_rootserver | status | build_version | stop_time | start_service_time | last_offline_time | block_migrate_in_time |
+-------+-----------+----------+------------+-----------------+--------+-------------------------------------------------------------------------------------------+-----------+-----------------------------+-------------------+-----------------------+
| zone1 | 127.0.0.1 | 2882 | 2881 | YES | ACTIVE | 4.2.5.4_104040052025071121-969b0573b5acaa8702a55910412894a7a33b7382(Jul 11 2025 21:46:10) | NULL | 20260525135547983776.000000 | NULL | NULL |
+-------+-----------+----------+------------+-----------------+--------+-------------------------------------------------------------------------------------------+-----------+-----------------------------+-------------------+-----------------------+
1 row in set (0.003 sec)
obclient(root@sys)[oceanbase]>
查询 UNIT 列表
可以使用如下 SQL，查询 UNIT 的详细情况。
SELECT
/*+ QUERY_TIMEOUT(60000000) */
t1.MODIFY_TIME AS UPDATE_TIME,
t1.RESOURCE_POOL_ID,
t1.NAME,
t1.UNIT_COUNT,
t1.UNIT_CONFIG_ID,
t1.ZONE_LIST,
t1.TENANT_ID,
t1.REPLICA_TYPE,
t2.NAME AS UNIT_CONFIG_NAME,
t2.MAX_CPU,
t2.MIN_CPU,
CONCAT(TRUNCATE(t2.MEMORY_SIZE/1024/1024/1024,2),'G'),
t2.MAX_IOPS,
t2.MIN_IOPS,
CONCAT(TRUNCATE(t2.LOG_DISK_SIZE/1024/1024/1024,2),'G')
FROM
oceanbase.DBA_OB_RESOURCE_POOLS AS t1
JOIN oceanbase.DBA_OB_UNIT_CONFIGS AS t2 ON t1.UNIT_CONFIG_ID = t2.UNIT_CONFIG_ID;
从结果可以看出各租户的资源分配详情（具体数据以实际返回结果为准）。这里以 mysql_tenant_pool 为例，该资源池的更新时间为 2026-04-30 12:02:14.921058，资源池 ID 为 1001，只有 1 个名为 mysql_tenant_unit 的 unit，该资源池位于 zone1，该资源池被分配给租户 ID 为 1002 的租户，分配的内存为 5.00G，分配的 IOPS 为 9223372036854775807，分配的日志盘为 17.00G。
+----------------------------+------------------+-------------------+------------+----------------+-----------+-----------+--------------+-------------------+---------+---------+-------------------------------------------------------+---------------------+---------------------+---------------------------------------------------------+
| UPDATE_TIME | RESOURCE_POOL_ID | NAME | UNIT_COUNT | UNIT_CONFIG_ID | ZONE_LIST | TENANT_ID | REPLICA_TYPE | UNIT_CONFIG_NAME | MAX_CPU | MIN_CPU | CONCAT(TRUNCATE(t2.MEMORY_SIZE/1024/1024/1024,2),'G') | MAX_IOPS | MIN_IOPS | CONCAT(TRUNCATE(t2.LOG_DISK_SIZE/1024/1024/1024,2),'G') |
+----------------------------+------------------+-------------------+------------+----------------+-----------+-----------+--------------+-------------------+---------+---------+-------------------------------------------------------+---------------------+---------------------+---------------------------------------------------------+
| 2026-04-30 11:47:31.764731 | 1 | sys_pool | 1 | 1 | zone1 | 1 | FULL | sys_unit_config | 2 | 2 | 1.00G | 9223372036854775807 | 9223372036854775807 | 2.00G |
| 2026-04-30 12:02:14.921058 | 1001 | mysql_tenant_pool | 1 | 1001 | zone1 | 1002 | FULL | mysql_tenant_unit | 6 | 6 | 5.00G | 9223372036854775807 | 9223372036854775807 | 17.00G |
+----------------------------+------------------+-------------------+------------+----------------+-----------+-----------+--------------+-------------------+---------+---------+-------------------------------------------------------+---------------------+---------------------+---------------------------------------------------------+
2 rows in set (0.004 sec)
统计未使用的 unit
可以使用如下 SQL，查询已创建但是未使用的 UNIT 的详细情况。
SELECT
/*+ QUERY_TIMEOUT(5000000) */
t1.UNIT_ID,
t1.RESOURCE_POOL_ID,
t1.ZONE,
t1.SVR_IP,
t1.SVR_PORT,
t1.STATUS,
t2.REPLICA_TYPE,
NULLAS TENANT_ID,
NULLAS TENANT_NAME,
t2.NAME AS RESOURCE_POOL_NAME,
t2.MODIFY_TIME AS RESOURCE_POOL_UPDATE_TIME,
t1.MIGRATE_FROM_SVR_IP,
t1.MIGRATE_FROM_SVR_PORT,
t1.MANUAL_MIGRATE
FROM
oceanbase.DBA_OB_UNITS AS t1
JOIN oceanbase.DBA_OB_RESOURCE_POOLS AS t2 ON t1.RESOURCE_POOL_ID = t2.RESOURCE_POOL_ID
WHERE
t2.TENANT_ID ISNULL;
从结果可以看出，当前集群所有的 unit 都被使用了。
obclient(root@sys)[oceanbase]> SELECT
-> /*+ QUERY_TIMEOUT(5000000) */
-> t1.UNIT_ID,
-> t1.RESOURCE_POOL_ID,
-> t1.ZONE,
-> t1.SVR_IP,
-> t1.SVR_PORT,
-> t1.STATUS,
-> t2.REPLICA_TYPE,
-> NULLAS TENANT_ID,
-> NULLAS TENANT_NAME,
-> t2.NAME AS RESOURCE_POOL_NAME,
-> t2.MODIFY_TIME AS RESOURCE_POOL_UPDATE_TIME,
-> t1.MIGRATE_FROM_SVR_IP,
-> t1.MIGRATE_FROM_SVR_PORT,
-> t1.MANUAL_MIGRATE
-> FROM
-> oceanbase.DBA_OB_UNITS AS t1
-> JOIN oceanbase.DBA_OB_RESOURCE_POOLS AS t2 ON t1.RESOURCE_POOL_ID = t2.RESOURCE_POOL_ID
-> WHERE
-> t2.TENANT_ID ISNULL;
Empty set (0.011 sec)
obclient(root@sys)[oceanbase]>
查看内存各模块使用相关信息
查看 MEMStore 的使用情况
使用如下 SQL，可以查看 MEMStore 的使用情况。
select
/*+ READ_CONSISTENCY(WEAK),query_timeout(100000000) */
TENANT_ID,
SVR_IP,
round(ACTIVE_SPAN / 1024 / 1024 / 1024, 2) ACTIVE_GB,
round(MEMSTORE_USED / 1024 / 1024 / 1024, 2) TOTAL_GB,
round(FREEZE_TRIGGER / 1024 / 1024 / 1024, 2) FREEZE_TRIGGER_GB,
round(MEMSTORE_USED / FREEZE_TRIGGER * 100, 2) percent_trigger,
round(MEMSTORE_LIMIT / 1024 / 1024 / 1024, 2) MEM_LIMIT_GB
from
V$OB_MEMSTORE
where
tenant_id > 1000
or TENANT_ID = 1
orderby
tenant_id,
TOTAL_GB desc;
以租户 ID 为 1001 的租户为例，从结果中可以看出（具体数据以实际返回结果为准），该租户活跃 MemTable 占用内存的估计值为 0.06G，MemTable 目前使用的总内存大小为 0.06G，触发 MemTable 冻结的内存大小为 0.10G，冻结比例为 57.63%，MemTable 的内存大小限制为 0.40G。
obclient(root@sys)[oceanbase]> select
-> /*+ READ_CONSISTENCY(WEAK),query_timeout(100000000) */
-> TENANT_ID,
-> SVR_IP,
-> round(ACTIVE_SPAN / 1024 / 1024 / 1024, 2) ACTIVE_GB,
-> round(MEMSTORE_USED / 1024 / 1024 / 1024, 2) TOTAL_GB,
-> round(FREEZE_TRIGGER / 1024 / 1024 / 1024, 2) FREEZE_TRIGGER_GB,
-> round(MEMSTORE_USED / FREEZE_TRIGGER * 100, 2) percent_trigger,
-> round(MEMSTORE_LIMIT / 1024 / 1024 / 1024, 2) MEM_LIMIT_GB
-> from
-> V$OB_MEMSTORE
-> where
-> tenant_id > 1000
-> or TENANT_ID = 1
-> orderby
-> tenant_id,
-> TOTAL_GB desc;
+-----------+-----------+-----------+----------+-------------------+-----------------+--------------+
| TENANT_ID | SVR_IP | ACTIVE_GB | TOTAL_GB | FREEZE_TRIGGER_GB | percent_trigger | MEM_LIMIT_GB |
+-----------+-----------+-----------+----------+-------------------+-----------------+--------------+
| 1 | 127.0.0.1 | 0.07 | 0.07 | 0.12 | 59.52 | 0.40 |
| 1001 | 127.0.0.1 | 0.06 | 0.06 | 0.10 | 57.63 | 0.40 |
| 1002 | 127.0.0.1 | 0.23 | 0.23 | 0.51 | 45.03 | 1.60 |
+-----------+-----------+-----------+----------+-------------------+-----------------+--------------+
3 rows in set (0.006 sec)
obclient(root@sys)[oceanbase]>
查看各模块内容占用情况
可以使用如下 SQL，查看各模块内存占用大小。
select * from V$OB_MEMORY order by hold desc limit 10;
从结果可以看出（具体数据以实际返回结果为准），MEMSTORE_CTX_ID 占用内存最多，为 243449856 字节，该模块属于 1002 租户。
obclient(root@sys)[oceanbase]> select * from V$OB_MEMORY orderby hold desclimit10;
+-----------+-----------+----------+------------------+----------------+-------+-----------+-----------+
| TENANT_ID | SVR_IP | SVR_PORT | CTX_NAME | MOD_NAME | COUNT | HOLD | USED |
+-----------+-----------+----------+------------------+----------------+-------+-----------+-----------+
| 1002 | 127.0.0.1 | 2882 | MEMSTORE_CTX_ID | Memstore | 117 | 243449856 | 243338472 |
| 1002 | 127.0.0.1 | 2882 | CO_STACK | CoStack | 244 | 125927424 | 125693184 |
| 1 | 127.0.0.1 | 2882 | CO_STACK | CoStack | 224 | 115605504 | 115390464 |
| 1001 | 127.0.0.1 | 2882 | CO_STACK | CoStack | 180 | 92897280 | 92724480 |
| 500 | 127.0.0.1 | 2882 | CO_STACK | CoStack | 179 | 92381184 | 92209344 |
| 1002 | 127.0.0.1 | 2882 | DEFAULT_CTX_ID | LogGroupBuffer | 2 | 83927040 | 83886080 |
| 1 | 127.0.0.1 | 2882 | MEMSTORE_CTX_ID | Memstore | 36 | 74907648 | 74873376 |
| 1001 | 127.0.0.1 | 2882 | MEMSTORE_CTX_ID | Memstore | 30 | 62423040 | 62394480 |
| 1 | 127.0.0.1 | 2882 | KVSTORE_CACHE_ID | KvstorCacheMb | 28 | 58720256 | 0 |
| 500 | 127.0.0.1 | 2882 | DEFAULT_CTX_ID | TZInfoArray | 3317 | 52517184 | 50996736 |
+-----------+-----------+----------+------------------+----------------+-------+-----------+-----------+
10 rows in set (0.019 sec)
查看 KVCACHE 的使用情况
可以使用如下 SQL，查看 KVCACHE 的使用情况。
select
tenant_id,
svr_ip,
cache_name,
concat(round(cache_size / 1024 / 1024 / 1024, 2), 'G') cache_size
from
V$OB_KVCACHE
orderby
1,
2,
4desc;
从结果可以看出，当前集群使用 KVCACHE 最多的是租户 1，这个是系统租户。
obclient(root@sys)[oceanbase]> select
-> tenant_id,
-> svr_ip,
-> cache_name,
-> concat(round(cache_size / 1024 / 1024 / 1024, 2), 'G') cache_size
-> from
-> V$OB_KVCACHE
-> orderby
-> 1,
-> 2,
-> 4desc;
+-----------+-----------+-----------------------+------------+
| tenant_id | svr_ip | cache_name | cache_size |
+-----------+-----------+-----------------------+------------+
| 1 | 127.0.0.1 | schema_cache | 0.01G |
| 1 | 127.0.0.1 | tablet_table_cache | 0.01G |
| 1 | 127.0.0.1 | vtable_cache | 0.01G |
| 1 | 127.0.0.1 | index_block_cache | 0.01G |
| 1 | 127.0.0.1 | user_block_cache | 0.01G |
| 1 | 127.0.0.1 | user_row_cache | 0.01G |
| 1 | 127.0.0.1 | bf_cache | 0.01G |
| 1 | 127.0.0.1 | fuse_row_cache | 0.01G |
| 1 | 127.0.0.1 | storage_meta_cache | 0.01G |
| 1 | 127.0.0.1 | opt_system_stat_cache | 0.01G |
| 1 | 127.0.0.1 | opt_table_stat_cache | 0.01G |
| 1 | 127.0.0.1 | opt_column_stat_cache | 0.01G |
| 1 | 127.0.0.1 | opt_ds_stat_cache | 0.01G |
| 1 | 127.0.0.1 | tx_data_kv_cache | 0.00G |
| 1001 | 127.0.0.1 | tx_data_kv_cache | 0.01G |
| 1001 | 127.0.0.1 | opt_table_stat_cache | 0.01G |
| 1001 | 127.0.0.1 | opt_system_stat_cache | 0.01G |
| 1001 | 127.0.0.1 | storage_meta_cache | 0.01G |
| 1002 | 127.0.0.1 | storage_meta_cache | 0.01G |
| 1002 | 127.0.0.1 | tx_data_kv_cache | 0.01G |
| 1002 | 127.0.0.1 | opt_table_stat_cache | 0.01G |
| 1002 | 127.0.0.1 | opt_system_stat_cache | 0.01G |
+-----------+-----------+-----------------------+------------+
22 rows in set (0.019 sec)
obclient(root@sys)[oceanbase]>
查询会话相关信息
查询各租户下活跃会话数
可以使用如下 SQL，查询活跃的会话数。
select
case
when cnt isnullthen0
else cnt
end
as cnt,
tenant_name,
tenant_id
from
(
select
DBA_OB_TENANTS.tenant_name,
DBA_OB_TENANTS.tenant_id,
cnt
from
DBA_OB_TENANTS
leftjoin (
select
count(
`state` = 'ACTIVE'
ORNULL
) as cnt,
tenant as tenant_name
from
GV$OB_PROCESSLIST
where
svr_ip = '10.xxx.xxx.xxx'
and svr_port = '2882'
groupby
tenant
) t1 on DBA_OB_TENANTS.tenant_name = t1.tenant_name
where
DBA_OB_TENANTS.tenant_type <> 'META'
) t2;
从结果可以看出，当前数据库各租户活跃情况。’mq_t1′ 租户没有活跃的会话。
obclient(root@sys)[oceanbase]> select
-> case
-> when cnt isnullthen0
-> else cnt
-> end
-> as cnt,
-> tenant_name,
-> tenant_id
-> from
-> (
-> select
-> DBA_OB_TENANTS.tenant_name,
-> DBA_OB_TENANTS.tenant_id,
-> cnt
-> from
-> DBA_OB_TENANTS
-> leftjoin (
-> select
-> count(
-> `state` = 'ACTIVE'
-> ORNULL
-> ) as cnt,
-> tenant as tenant_name
-> from
-> GV$OB_PROCESSLIST
-> where
-> svr_ip = '10.xxx.xxx.xxx'
-> and svr_port = '2882'
-> groupby
-> tenant
-> ) t1 on DBA_OB_TENANTS.tenant_name = t1.tenant_name
-> where
-> DBA_OB_TENANTS.tenant_type <> 'META'
-> ) t2;
+------+--------------+-----------+
| cnt | tenant_name | tenant_id |
+------+--------------+-----------+
| 0 | mysql_tenant | 1002 |
| 0 | sys | 1 |
+------+--------------+-----------+
2 rows in set (0.036 sec)
obclient(root@sys)[oceanbase]>
查看不同主机的实时的会话数
可以使用如下 SQL，查询不同主机的实时会话数。
select
user,
host,
state,
count(*)
from
GV$OB_PROCESSLIST
where
state <> 'SLEEP'
groupbyuser,host,state orderby host limit50;
从结果可以看出，当前只有一台主机上有会话连接进来，会话数为 1，对应的用户为 root。
obclient(root@sys)[oceanbase]> select
-> user,
-> host,
-> state,
-> count(*)
-> from
-> GV$OB_PROCESSLIST
-> where
-> state <> 'SLEEP'
-> groupbyuser,host,state orderby host limit50;
+------+-----------------+--------+----------+
| user | host | state | count(*) |
+------+-----------------+--------+----------+
| root | 127.0.0.1:41452 | ACTIVE | 1 |
+------+-----------------+--------+----------+
1 row in set (0.005 sec)
查询合并和转储相关信息
对所有租户手动触发一次合并。
ALTER SYSTEM MAJOR FREEZE TENANT = all_user;
sys 租户下查询所有租户的合并状态
可以使用如下 SQL，可以在 sys 租户下查询所有租户的合并状态。
SELECT
tenant_id,
global_broadcast_scn AS broadcast_scn,
is_error ASerror,
status,
frozen_scn,
last_scn,
is_suspended ASsuspend,
info,
start_time,
last_finish_time
FROM
CDB_OB_MAJOR_COMPACTION;
以租户 ID 为 1002 的租户为例，从结果 1 可以看出（具体数据以实际返回结果为准），该租户的广播合并的版本号和冻结合并版本号以及上一次已经完成合并的版本不一致，同时状态为 COMPACTING，表示正在合并中；合并开始的时间是 2026-05-26 12:16:18.279249。从结果 2 可以看出，合并已结束，此时该租户的广播合并的版本号和冻结合并版本号以及上一次已经完成合并的版本是一致的，同时状态为 IDLE，表示没有在合并中；error 为 NO，表示合并过程中没有出现错误；合并开始的时间是 2026-05-26 12:16:18.279249；合并结束的时间是 2026-05-26 12:22:10.545077。
obclient(root@sys)[oceanbase]> SELECT
-> tenant_id,
-> global_broadcast_scn AS broadcast_scn,
-> is_error ASerror,
-> status,
-> frozen_scn,
-> last_scn,
-> is_suspended ASsuspend,
-> info,
-> start_time,
-> last_finish_time
-> FROM
-> CDB_OB_MAJOR_COMPACTION;
+-----------+---------------------+-------+------------+---------------------+----------+---------+------+----------------------------+----------------------------+
| tenant_id | broadcast_scn | error | status | frozen_scn | last_scn | suspend | info | start_time | last_finish_time |
+-----------+---------------------+-------+------------+---------------------+----------+---------+------+----------------------------+----------------------------+
| 1 | 1 | NO | IDLE | 1 | 1 | NO | | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 |
| 1001 | 1 | NO | IDLE | 1 | 1 | NO | | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 |
| 1002 | 1779768978130955000 | NO | COMPACTING | 1779768978130955000 | 1 | NO | | 2026-05-26 12:16:18.279249 | 1970-01-01 08:00:00.000000 |
+-----------+---------------------+-------+------------+---------------------+----------+---------+------+----------------------------+----------------------------+
3 rows in set (0.005 sec)
+-----------+---------------------+-------+--------+---------------------+---------------------+---------+------+----------------------------+----------------------------+
| tenant_id | broadcast_scn | error | status | frozen_scn | last_scn | suspend | info | start_time | last_finish_time |
+-----------+---------------------+-------+--------+---------------------+---------------------+---------+------+----------------------------+----------------------------+
| 1 | 1 | NO | IDLE | 1 | 1 | NO | | 1970-01-0108:00:00.000000 | 1970-01-0108:00:00.000000 |
| 1001 | 1 | NO | IDLE | 1 | 1 | NO | | 1970-01-0108:00:00.000000 | 1970-01-0108:00:00.000000 |
| 1002 | 1779768978130955000 | NO | IDLE | 1779768978130955000 | 1779768978130955000 | NO | | 2026-05-2612:16:18.279249 | 2026-05-2612:22:10.545077 |
+-----------+---------------------+-------+--------+---------------------+---------------------+---------+------+----------------------------+----------------------------+
3rowsinset (0.004 sec)
普通租户下查询当前租户的合并状态
使用 exit 退出当前登录的 sys 租户，并使用如下方式，登录普通租户。
obclient -h127.0.0.1 -uroot@mq_t1 -P2881 -Doceanbase -A
可以使用如下 SQL，在普通租户下查询当前租户的合并状态。
SELECT
global_broadcast_scn AS broadcast_scn,
is_error ASerror,
status,
frozen_scn,
last_scn,
is_suspended ASsuspend,
info,
start_time,
last_finish_time
FROM
DBA_OB_MAJOR_COMPACTION;
从结果可以看出（具体数据以实际返回结果为准），当前租户的广播合并的版本号与冻结合并版本号及上一次已经完成合并的版本是一致的，均为 1756954537913245000；error 为 NO，表示合并过程中没有出现错误；状态为 IDLE，表示没有在合并中；合并开始的时间是 2026-05-26 12:16:18.279249，合并结束的时间是 2026-05-26 12:22:10.545077。
obclient(root@mq_t1)[oceanbase]> SELECT
-> global_broadcast_scn AS broadcast_scn,
-> is_error ASerror,
-> status,
-> frozen_scn,
-> last_scn,
-> is_suspended ASsuspend,
-> info,
-> start_time,
-> last_finish_time
-> FROM
-> DBA_OB_MAJOR_COMPACTION;
+---------------------+-------+--------+---------------------+---------------------+---------+------+----------------------------+----------------------------+
| broadcast_scn | error | status | frozen_scn | last_scn | suspend | info | start_time | last_finish_time |
+---------------------+-------+--------+---------------------+---------------------+---------+------+----------------------------+----------------------------+
| 1779768978130955000 | NO | IDLE | 1779768978130955000 | 1779768978130955000 | NO | | 2026-05-26 12:16:18.279249 | 2026-05-26 12:22:10.545077 |
+---------------------+-------+--------+---------------------+---------------------+---------+------+----------------------------+----------------------------+
1 row in set (0.028 sec)
获取租户的合并进度
可以使用如下 SQL，查询当前租户的合并进度。
SELECT
TENANT_ID,
COMPACTION_SCN,
CONCAT(100 * (
1 - SUM(UNFINISHED_TABLET_COUNT) / SUM(TOTAL_TABLET_COUNT)
),'%')
FROM
GV$OB_COMPACTION_PROGRESS;
从结果可以看出，当前租户的合并进展为 100%。
obclient(root@mq_t1)[oceanbase]> SELECT
-> TENANT_ID,
-> COMPACTION_SCN,
-> CONCAT(100 * (
-> 1 - SUM(UNFINISHED_TABLET_COUNT) / SUM(TOTAL_TABLET_COUNT)
-> ),'%')
-> FROM
-> GV$OB_COMPACTION_PROGRESS;
+-----------+---------------------+----------------------------------------------------------------------------------------+
| TENANT_ID | COMPACTION_SCN | CONCAT(100 * (
1 - SUM(UNFINISHED_TABLET_COUNT) / SUM(TOTAL_TABLET_COUNT)
),'%') |
+-----------+---------------------+----------------------------------------------------------------------------------------+
| 1002 | 1779768978130955000 | 100.0000% |
+-----------+---------------------+----------------------------------------------------------------------------------------+
1 row in set (0.047 sec)
obclient(root@mq_t1)[oceanbase]>
查询所有租户的 tablet 转储历史信息
使用 exit 退出当前登录的普通租户，并使用如下方式，登录 sys 租户。
SELECT
tenant_id,
start_time as min_start_time,
finish_time AS max_finish_time,
SUM(occupy_size) AS occupy_size,
SUM(total_row_count) AS total_row_count,
COUNT(1) AS tablet_count
FROM
GV$OB_TABLET_COMPACTION_HISTORY;
从结果可以看出，当前租户的转储开始时间为 2026-05-25 20:56:41.650271，结束时间为 2026-05-25 20:56:41.738214，数据量为 1.2G，tablet 转储总数为 1337。
obclient(root@sys)[oceanbase]> SELECT
-> tenant_id,
-> start_time as min_start_time,
-> finish_time AS max_finish_time,
-> SUM(occupy_size) AS occupy_size,
-> SUM(total_row_count) AS total_row_count,
-> COUNT(1) AS tablet_count
-> FROM
-> GV$OB_TABLET_COMPACTION_HISTORY;
+-----------+----------------------------+----------------------------+-------------+-----------------+--------------+
| tenant_id | min_start_time | max_finish_time | occupy_size | total_row_count | tablet_count |
+-----------+----------------------------+----------------------------+-------------+-----------------+--------------+
| 1 | 2026-05-25 20:56:41.650271 | 2026-05-25 20:56:41.738214 | 1198358605 | 12138881 | 1337 |
+-----------+----------------------------+----------------------------+-------------+-----------------+--------------+
1 row in set (0.010 sec)
obclient(root@sys)[oceanbase]>
查询 RS 相关信息
查询租户 RS 任务列表
可以使用如下 SQL，查询租户 RS 任务列表，用于租户运维后判断内核任务是否完成。这里 JOB_TYPE 只有 ALTER_TENANT_LOCALITY 和 SHRINK_RESOURCE_POOL_UNIT_NUM 两种，前者会在修改租户 locality，新增副本和删除副本场景遇到，后者会在减少 unit 数量场景遇到。
SELECT
TENANT_ID,
JOB_ID,
JOB_TYPE,
JOB_STATUS,
PROGRESS
FROM
oceanbase.DBA_OB_TENANT_JOBS
ORDER BY
JOB_ID DESC;
查询 RS 历史事件信息
SELECT
/*+QUERY_TIMEOUT(60000000) */
timestampas gmt_create,
module,
event,
name1,
value1,
name2,
value2,
name3,
value3,
name4,
value4,
name5,
value5,
name6,
value6,
extra_info,
rs_svr_ip,
rs_svr_port
FROM
DBA_OB_ROOTSERVICE_EVENT_HISTORY
ORDERBYtimestampdesc
limit
20;
从结果可以看出，在不同的时间，不同的租户进行的对应的事件。
obclient(root@sys)[oceanbase]> SELECT
-> /*+QUERY_TIMEOUT(60000000) */
-> timestampas gmt_create,
-> module,
-> event,
-> name1,
-> value1,
-> name2,
-> value2,
-> name3,
-> value3,
-> name4,
-> value4,
-> name5,
-> value5,
-> name6,
-> value6,
-> extra_info,
-> rs_svr_ip,
-> rs_svr_port
-> FROM
-> DBA_OB_ROOTSERVICE_EVENT_HISTORY
-> ORDERBYtimestampdesc
-> limit
-> 20;
+----------------------------+--------------+------------------------------+--------+------------------+-----------+------------------+-------+--------+-------+--------+-------+--------+-------+--------+------------+-----------+-------------+
| gmt_create | module | event | name1 | value1 | name2 | value2 | name3 | value3 | name4 | value4 | name5 | value5 | name6 | value6 | extra_info | rs_svr_ip | rs_svr_port |
+----------------------------+--------------+------------------------------+--------+------------------+-----------+------------------+-------+--------+-------+--------+-------+--------+-------+--------+------------+-----------+-------------+
| 2026-05-25 13:55:49.752460 | server | start_service | server | "127.0.0.1:2882" | | | | | | | | | | | | 127.0.0.1 | 2882 |
| 2026-05-25 13:55:49.439864 | root_service | admin_refresh_io_calibration | ret | 0 | | | | | | | | | | | | 127.0.0.1 | 2882 |
| 2026-05-25 13:55:45.725428 | server | start_service | server | "127.0.0.1:2882" | | | | | | | | | | | | 127.0.0.1 | 2882 |
| 2026-05-25 13:55:44.492830 | root_service | full_rootservice | result | 0 | self_addr | "127.0.0.1:2882" | | | | | | | | | | 127.0.0.1 | 2882 |
| 2026-05-25 13:55:44.410825 | server | load_servers | ret | 0 | has_build | 1 | | | | | | | | | | 127.0.0.1 | 2882 |
| 2026-05-25 13:55:44.194669 | server | load_servers | ret | 0 | has_build | 1 | | | | | | | | | | 127.0.0.1 | 2882 |
+----------------------------+--------------+------------------------------+--------+------------------+-----------+------------------+-------+--------+-------+--------+-------+--------+-------+--------+------------+-----------+-------------+
6 rows in set (0.003 sec)
obclient(root@sys)[oceanbase]>
权限
查询用户列表、全局权限授权情况
可以使用如下 SQL，查询用户授权的情况。
SELECT
`user`,
(
CASE`account_locked`
WHEN'Y'THEN1
ELSE0
END
) AS account_locked,
`select_priv`,
`insert_priv`,
`update_priv`,
`delete_priv`,
`create_priv`,
`drop_priv`,
`process_priv`,
`grant_priv`,
`index_priv`,
`alter_priv`,
`show_db_priv`,
`super_priv`,
`create_view_priv`,
`show_view_priv`,
`create_user_priv`,
`password`
FROM
`mysql`.`user`;
从结果可以看出（具体数据以实际返回结果为准），目前数据库中各用户不同的权限，这里 Y 表示拥有该权限，N 表示不拥有该权限。
obclient(root@sys)[oceanbase]> SELECT
-> `user`,
-> (
-> CASE`account_locked`
-> WHEN'Y'THEN1
-> ELSE0
-> END
-> ) AS account_locked,
-> `select_priv`,
-> `insert_priv`,
-> `update_priv`,
-> `delete_priv`,
-> `create_priv`,
-> `drop_priv`,
-> `process_priv`,
-> `grant_priv`,
-> `index_priv`,
-> `alter_priv`,
-> `show_db_priv`,
-> `super_priv`,
-> `create_view_priv`,
-> `show_view_priv`,
-> `create_user_priv`,
-> `password`
-> FROM
-> `mysql`.`user`;
+------------+----------------+-------------+-------------+-------------+-------------+-------------+-----------+--------------+------------+------------+------------+--------------+------------+------------------+----------------+------------------+-------------------------------------------+
| user | account_locked | select_priv | insert_priv | update_priv | delete_priv | create_priv | drop_priv | process_priv | grant_priv | index_priv | alter_priv | show_db_priv | super_priv | create_view_priv | show_view_priv | create_user_priv | password |
+------------+----------------+-------------+-------------+-------------+-------------+-------------+-----------+--------------+------------+------------+------------+--------------+------------+------------------+----------------+------------------+-------------------------------------------+
| root | 0 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | |
| ORAAUDITOR | 1 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | *30d4cc045b8aede9bc9c509df0d94abe4af59013 |
+------------+----------------+-------------+-------------+-------------+-------------+-------------+-----------+--------------+------------+------------+------------+--------------+------------+------------------+----------------+------------------+-------------------------------------------+
2 rows in set (0.015 sec)
obclient(root@sys)[oceanbase]>
查询数据库权限授权情况
可以使用如下 SQL，查看数据库授权情况。
SELECT
`db`,
`user`,
`select_priv`,
`insert_priv`,
`update_priv`,
`delete_priv`,
`create_priv`,
`drop_priv`,
`index_priv`,
`alter_priv`,
`create_view_priv`,
`show_view_priv`
FROM
`mysql`.`db`;
从结果可以看出（具体数据以实际返回结果为准），不同的用户对于不同数据库的权限拥有情况。
obclient(root@sys)[oceanbase]> SELECT
-> `db`,
-> `user`,
-> `select_priv`,
-> `insert_priv`,
-> `update_priv`,
-> `delete_priv`,
-> `create_priv`,
-> `drop_priv`,
-> `index_priv`,
-> `alter_priv`,
-> `create_view_priv`,
-> `show_view_priv`
-> FROM
-> `mysql`.`db`;
+--------------------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+------------+------------------+----------------+
| db | user | select_priv | insert_priv | update_priv | delete_priv | create_priv | drop_priv | index_priv | alter_priv | create_view_priv | show_view_priv |
+--------------------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+------------+------------------+----------------+
| mysql | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| oceanbase | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| test | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| information_schema | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| __public | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| __recyclebin | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
+--------------------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+------------+------------------+----------------+
6 rows in set (0.003 sec)
obclient(root@sys)[oceanbase]>
关于作者
网名：飞天，墨天轮2024年度、2025年度优秀原创作者，拥有 Oracle 10g OCM 认证、PGCE认证、MySQL 8.0 OCP认证以及OBCA、KCP、KCSM、ACP、YCP、HCIP-openGauss、HCCDP-GaussDB、磐维等众多国产数据库认证证书，目前从事Oracle、Mysql、PostgreSQL、磐维数据库管理运维工作，喜欢结交更多志同道合的朋友，热衷于研究、分享数据库技术。
微信公众号：飞天online
墨天轮：https://www.modb.pro/u/15197
版权申明：内容来源网络，版权归原创者所有，如有侵权请联系删除
想了解更多干货，可通过下方扫码关注
可扫码添加上智启元官方客服微信👇
