划线反馈

普通租户下查到的 GV$OB_SQL_AUDIT 视图中的 QUERY_SQL 列总是为空的原因和解决方法

更新时间：2026-05-25 01:56

适用版本：V4.2.x内容类型：Troubleshoot

问题现象

在普通业务租户下通过管理员用户查到的 GV$OB_SQL_AUDIT 视图中的 QUERY_SQL 列总是为空。

$ mysql -hxx.xxx.80.111 -P2881 -uroot@mysqlt -pxxx -A -c test
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3221573438
Server version: 5.7.25 OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [test]> select usec_to_time(request_time),db_name,query_sql from oceanbase.gv$ob_sql_audit where db_name='test' order by request_time limit 10;
+----------------------------+---------+-----------+
| usec_to_time(request_time) | db_name | query_sql |
+----------------------------+---------+-----------+
| 2025-05-18 10:38:47.784887 | test    |           |
| 2025-05-18 10:38:51.286249 | test    |           |
| 2025-05-18 10:39:20.732180 | test    |           |
| 2025-05-18 10:39:25.008732 | test    |           |
| 2025-05-18 10:39:30.835809 | test    |           |
| 2025-05-18 11:07:28.822302 | test    |           |
| 2025-05-18 11:09:35.099681 | test    |           |
| 2025-05-18 11:12:42.318055 | test    |           |
| 2025-05-18 11:21:22.361988 | test    |           |
| 2025-05-18 11:53:33.323748 | test    |           |
+----------------------------+---------+-----------+
10 rows in set (0.15 sec)

MySQL [test]> Bye

如果切换到系统 SYS 租户下，则可以正常查看到对应普通租户的 QUERY_SQL 信息。

$ mysql -hxx.xxx.80.111 -P2881 -uroot@sys -pxxx -A -c oceanbase
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3221689588
Server version: 5.7.25 OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [oceanbase]> select usec_to_time(request_time),db_name,query_sql from oceanbase.gv$ob_sql_audit where db_name='test' and tenant_name='mysqlt' order by request_time limit 10;
+----------------------------+---------+----------------------------------+
| usec_to_time(request_time) | db_name | query_sql                        |
+----------------------------+---------+----------------------------------+
| 2025-05-18 10:38:47.784887 | test    | select @@version_comment limit 1 |
| 2025-05-18 10:38:51.286249 | test    | desc t1                          |
| 2025-05-18 10:39:20.732180 | test    | desc t1                          |
| 2025-05-18 10:39:25.008732 | test    | show create table t1             |
| 2025-05-18 10:39:30.835809 | test    | select * from t1                 |
| 2025-05-18 11:07:28.822302 | test    | select @@version_comment limit 1 |
| 2025-05-18 11:09:35.099681 | test    | select @@version_comment limit 1 |
| 2025-05-18 11:12:42.318055 | test    | select @@version_comment limit 1 |
| 2025-05-18 11:21:22.361988 | test    | select @@version_comment limit 1 |
| 2025-05-18 11:53:33.323748 | test    | select @@version_comment limit 1 |
+----------------------------+---------+----------------------------------+
10 rows in set (0.26 sec)

MySQL [oceanbase]> Bye

问题原因

该表现符合预期。从 OceanBase 数据库 V4.2.5 BP2（含）版本开始，出于保护敏感信息安全考虑，GV$OB_SQL_AUDIT 表中的 QUERY_SQL 默认仅对系统租户展示，如果普通租户需要，需要打开租户级隐藏配置项 _enable_sql_audit_query_sql。

注意：此信息仍会记录在 GV$OB_SQL_AUDIT 中，只是默认在普通租户场景不对外展示信息。

配置项名称：_enable_sql_audit_query_sql（默认值：False，TENANT 租户级别/DYNAMIC_EFFECTIVE）。
配置项描述： 控制普通租户是否允许查看 GV$OB_SQL_AUDIT 中的 QUERY_SQL。如果打开开关，可以在普通租户场景下展示 QUERY_SQL 信息；如果关闭开关，普通租户下不会展示 QUERY_SQL 信息。
Parameter Description(In English)： Control whether to use sql_audit_query_sql。
功能详解： 此开关默认关闭，普通租户查询 QUERY_SQL 为空；如果打开开关，对普通租户暴露 QUERY_SQL 信息。

MySQL [oceanbase]> select * from gv$ob_parameters where name='_enable_sql_audit_query_sql';                                               +---------------+----------+-------+--------+-----------+-----------------------------+-----------+-------+---------------------------------------------+----------+-------------------+---------------+-----------+
| SVR_IP        | SVR_PORT | ZONE  | SCOPE  | TENANT_ID | NAME                        | DATA_TYPE | VALUE | INFO                                        | SECTION  | EDIT_LEVEL        | DEFAULT_VALUE | ISDEFAULT |
+---------------+----------+-------+--------+-----------+-----------------------------+-----------+-------+---------------------------------------------+----------+-------------------+---------------+-----------+
| xx.xxx.80.111 |     2882 | zone1 | TENANT |         1 | _enable_sql_audit_query_sql | NULL      | False | Control whether to use sql_audit_query_sql. | OBSERVER | DYNAMIC_EFFECTIVE | False         | YES       |
| xx.xxx.80.111 |     2882 | zone1 | TENANT |      1001 | _enable_sql_audit_query_sql | NULL      | False | Control whether to use sql_audit_query_sql. | OBSERVER | DYNAMIC_EFFECTIVE | False         | YES       |
| xx.xxx.80.111 |     2882 | zone1 | TENANT |      1002 | _enable_sql_audit_query_sql | NULL      | False | Control whether to use sql_audit_query_sql. | OBSERVER | DYNAMIC_EFFECTIVE | False         | YES       |
| xx.xxx.80.111 |     2882 | zone1 | TENANT |      1003 | _enable_sql_audit_query_sql | NULL      | False | Control whether to use sql_audit_query_sql. | OBSERVER | DYNAMIC_EFFECTIVE | False         | YES       |
| xx.xxx.80.111 |     2882 | zone1 | TENANT |      1004 | _enable_sql_audit_query_sql | NULL      | False | Control whether to use sql_audit_query_sql. | OBSERVER | DYNAMIC_EFFECTIVE | False         | YES       |
+---------------+----------+-------+--------+-----------+-----------------------------+-----------+-------+---------------------------------------------+----------+-------------------+---------------+-----------+
5 rows in set (0.01 sec)

问题的风险及影响

在普通业务租户下通过管理员用户无法查到的 GV$OB_SQL_AUDIT 视图中的 QUERY_SQL 信息。

适用版本

OceanBase 数据库 V4.2.5 BP2（oceanbase-4.2.5.2-102000122025011711）及之后版本。（OceanBase 数据库 V4.2.x 系列）

解决方法

连接到 root@sys# 集群名，对于测试租户或者 QUERY_SQL 信息不敏感的非关键业务租户，可以临时为该租户打开该隐藏配置项。

$ mysql -hxx.xxx.80.111 -P2881 -uroot@sys -pxxx -A -c oceanbase
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3221498614
Server version: 5.7.25 OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [oceanbase]> alter system set _enable_sql_audit_query_sql=True tenant=mysqlt;
Query OK, 0 rows affected (0.01 sec)

MySQL [oceanbase]> Bye

开关打开后，在普通业务租户下就可以查看到 GV$OB_SQL_AUDIT 视图中的 QUERY_SQL 信息了。

$ mysql -hxx.xxx.80.111 -P2881 -uroot@mysqlt -pxxx -A -c test
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3221510935
Server version: 5.7.25 OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [test]> select usec_to_time(request_time),db_name,query_sql from oceanbase.gv$ob_sql_audit where db_name='test' order by request_time limit 10;
+----------------------------+---------+----------------------------------+
| usec_to_time(request_time) | db_name | query_sql                        |
+----------------------------+---------+----------------------------------+
| 2025-05-18 10:38:47.784887 | test    | select @@version_comment limit 1 |
| 2025-05-18 10:38:51.286249 | test    | desc t1                          |
| 2025-05-18 10:39:20.732180 | test    | desc t1                          |
| 2025-05-18 10:39:25.008732 | test    | show create table t1             |
| 2025-05-18 10:39:30.835809 | test    | select * from t1                 |
| 2025-05-18 11:07:28.822302 | test    | select @@version_comment limit 1 |
| 2025-05-18 11:09:35.099681 | test    | select @@version_comment limit 1 |
| 2025-05-18 11:12:42.318055 | test    | select @@version_comment limit 1 |
| 2025-05-18 11:21:22.361988 | test    | select @@version_comment limit 1 |
| 2025-05-18 11:53:33.323748 | test    | select @@version_comment limit 1 |
+----------------------------+---------+----------------------------------+
10 rows in set (0.13 sec)

规避方式

无。

嵌套物化视图使用说明

dba_ob_tenant_jobs 视图 job_status 字段显示 skip_checking_ls_status

OceanBase 数据库