--- title: 升级 JDBC 后 sql_audit 中 param_value 列全部为空-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于升级 JDBC 后 sql_audit 中 param_value 列全部为空相关的常见问题和使用技巧，帮助您快速解决升级 JDBC 后 sql_audit 中 param_value 列全部为空的难题。 image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # 升级 JDBC 后 sql_audit 中 param_value 列全部为空 更新时间：2026-05-25 01:56 适用版本： V3.2.x 内容类型：Troubleshoot ## 问题现象 在存在问题的 OceanBase 数据库版本中，使用 JDBC V2.2.7.6 版本（oceanbase-client-2.2.7.6.jar）执行带参数的 SQL 可以看到对应 SQL 在 `GV$SQL_AUDIT` 视图中都有 `PARAM_VALUE`，升级 JDBC 到新版本 V2.4.5 版本（oceanbase-client-2.4.5.jar）可以看到都没有 `PARAM_VALUE`。 - 老版本驱动 oceanbase-client-2.2.7.6.jar ```shell MySQL [oceanbase]> select usec_to_time(request_time),user_client_ip,elapsed_time,params_value,query_sql from gv$sql_audit where query_sql like '%SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POS%' order by request_time desc limit 20; +----------------------------+----------------+--------------+------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | usec_to_time(request_time) | user_client_ip | elapsed_time | params_value | query_sql | +----------------------------+----------------+--------------+------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 2023-10-13 11:21:11.605987 | xxx.xx.xxx.xxx | 1838 | | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM (SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, B.POSFORMNUM, 0 BANKSTATUS, 0 EXCHANGEAMT, '' NOTE, SYSDATE BANKSTATUSDATE FROM RECEPTION A, REC_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND B.REGION = 537 AND A.OID = B.OID AND A.STATUS = ? AND B.PAYTYPE IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'LastRefundFeePayType') ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:21:11.608764 | xxx.xx.xxx.xxx | 101564 | '13515370007','g1008908','2023-10-13 00:00:00','2023-10-13 23:59:59','stcmNml' | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM (SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, B.POSFORMNUM, 0 BANKSTATUS, 0 EXCHANGEAMT, '' NOTE, SYSDATE BANKSTATUSDATE FROM RECEPTION A, REC_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND B.REGION = 537 AND A.OID = B.OID AND A.STATUS = ? AND B.PAYTYPE IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'LastRefundFeePayType') ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:21:11.921517 | xxx.xx.xxx.xxx | 3557 | | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM ( SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, TO_CHAR(B.PAYID) POSFORMNUM, TO_NUMBER(B.STATUS) BANKSTATUS, DECODE(SIGN(B.FEE), -1, (-1)*B.FEE, B.FEE) EXCHANGEAMT, '' NOTE, B.STATUSDATE BANKSTATUSDATE FROM RECEPTION A, CS_ORDER_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND LENGTH(REGEXP_REPLACE(A.ORDERID, '[^0-9]', '')) = LENGTH(A.ORDERID) AND A.ORDERID = B.ORDERID AND A.STATUS = ? AND B.PAYTYPE LIKE 'ThirdParty%' AND B.STATUS IN (0, 1, 9) AND B.STATUS = NVL(?, B.STATUS) ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:21:11.926150 | xxx.xx.xxx.xxx | 641344 | '13515370007','g1008908','2023-10-13 00:00:00','2023-10-13 23:59:59','stcmNml','0' | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM ( SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, TO_CHAR(B.PAYID) POSFORMNUM, TO_NUMBER(B.STATUS) BANKSTATUS, DECODE(SIGN(B.FEE), -1, (-1)*B.FEE, B.FEE) EXCHANGEAMT, '' NOTE, B.STATUSDATE BANKSTATUSDATE FROM RECEPTION A, CS_ORDER_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND LENGTH(REGEXP_REPLACE(A.ORDERID, '[^0-9]', '')) = LENGTH(A.ORDERID) AND A.ORDERID = B.ORDERID AND A.STATUS = ? AND B.PAYTYPE LIKE 'ThirdParty%' AND B.STATUS IN (0, 1, 9) AND B.STATUS = NVL(?, B.STATUS) ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:21:38.171480 | xxx.xx.xxx.xxx | 10229 | '13515370007','g1008908','2023-10-04 00:00:00','2023-10-13 23:59:59','stcmNml' | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM (SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, B.POSFORMNUM, 0 BANKSTATUS, 0 EXCHANGEAMT, '' NOTE, SYSDATE BANKSTATUSDATE FROM RECEPTION A, REC_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND B.REGION = 537 AND A.OID = B.OID AND A.STATUS = ? AND B.PAYTYPE IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'LastRefundFeePayType') ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:21:38.194638 | xxx.xx.xxx.xxx | 34048 | '13515370007','g1008908','2023-10-04 00:00:00','2023-10-13 23:59:59','stcmNml','0' | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM ( SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, TO_CHAR(B.PAYID) POSFORMNUM, TO_NUMBER(B.STATUS) BANKSTATUS, DECODE(SIGN(B.FEE), -1, (-1)*B.FEE, B.FEE) EXCHANGEAMT, '' NOTE, B.STATUSDATE BANKSTATUSDATE FROM RECEPTION A, CS_ORDER_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND LENGTH(REGEXP_REPLACE(A.ORDERID, '[^0-9]', '')) = LENGTH(A.ORDERID) AND A.ORDERID = B.ORDERID AND A.STATUS = ? AND B.PAYTYPE LIKE 'ThirdParty%' AND B.STATUS IN (0, 1, 9) AND B.STATUS = NVL(?, B.STATUS) ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:22:09.636637 | xxx.xx.xxx.xxx | 2114 | | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM (SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, B.POSFORMNUM, 0 BANKSTATUS, 0 EXCHANGEAMT, '' NOTE, SYSDATE BANKSTATUSDATE FROM RECEPTION A, REC_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND B.REGION = 537 AND A.OID = B.OID AND A.STATUS = ? AND B.PAYTYPE IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'LastRefundFeePayType') ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:22:09.639034 | xxx.xx.xxx.xxx | 147118 | '13515370007','g1008908','2023-10-04 00:00:00','2023-10-13 23:59:59','stcmNml' | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM (SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, B.POSFORMNUM, 0 BANKSTATUS, 0 EXCHANGEAMT, '' NOTE, SYSDATE BANKSTATUSDATE FROM RECEPTION A, REC_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND B.REGION = 537 AND A.OID = B.OID AND A.STATUS = ? AND B.PAYTYPE IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'LastRefundFeePayType') ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:22:09.799181 | xxx.xx.xxx.xxx | 4712 | | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM ( SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, TO_CHAR(B.PAYID) POSFORMNUM, TO_NUMBER(B.STATUS) BANKSTATUS, DECODE(SIGN(B.FEE), -1, (-1)*B.FEE, B.FEE) EXCHANGEAMT, '' NOTE, B.STATUSDATE BANKSTATUSDATE FROM RECEPTION A, CS_ORDER_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND LENGTH(REGEXP_REPLACE(A.ORDERID, '[^0-9]', '')) = LENGTH(A.ORDERID) AND A.ORDERID = B.ORDERID AND A.STATUS = ? AND B.PAYTYPE LIKE 'ThirdParty%' AND B.STATUS IN (0, 1, 9) AND B.STATUS = NVL(?, B.STATUS) ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:22:09.804161 | xxx.xx.xxx.xxx | 1156892 | '13515370007','g1008908','2023-10-04 00:00:00','2023-10-13 23:59:59','stcmNml','' | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM ( SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, TO_CHAR(B.PAYID) POSFORMNUM, TO_NUMBER(B.STATUS) BANKSTATUS, DECODE(SIGN(B.FEE), -1, (-1)*B.FEE, B.FEE) EXCHANGEAMT, '' NOTE, B.STATUSDATE BANKSTATUSDATE FROM RECEPTION A, CS_ORDER_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND LENGTH(REGEXP_REPLACE(A.ORDERID, '[^0-9]', '')) = LENGTH(A.ORDERID) AND A.ORDERID = B.ORDERID AND A.STATUS = ? AND B.PAYTYPE LIKE 'ThirdParty%' AND B.STATUS IN (0, 1, 9) AND B.STATUS = NVL(?, B.STATUS) ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:23:02.540711 | xxx.xx.xxx.xxx | 13003 | '13515370007','g1008908','2023-10-04 00:00:00','2023-10-13 23:59:59','stcmNml' | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM (SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, B.POSFORMNUM, 0 BANKSTATUS, 0 EXCHANGEAMT, '' NOTE, SYSDATE BANKSTATUSDATE FROM RECEPTION A, REC_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND B.REGION = 537 AND A.OID = B.OID AND A.STATUS = ? AND B.PAYTYPE IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'LastRefundFeePayType') ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:23:02.613034 | xxx.xx.xxx.xxx | 73 | | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM ( SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, TO_CHAR(B.PAYID) POSFORMNUM, TO_NUMBER(B.STATUS) BANKSTATUS, DECODE(SIGN(B.FEE), -1, (-1)*B.FEE, B.FEE) EXCHANGEAMT, '' NOTE, B.STATUSDATE BANKSTATUSDATE FROM RECEPTION A, CS_ORDER_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND LENGTH(REGEXP_REPLACE(A.ORDERID, '[^0-9]', '')) = LENGTH(A.ORDERID) AND A.ORDERID = B.ORDERID AND A.STATUS = ? AND B.PAYTYPE LIKE 'ThirdParty%' AND B.STATUS IN (0, 1, 9) AND B.STATUS = NVL(?, B.STATUS) ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:23:02.613721 | xxx.xx.xxx.xxx | 48634 | '13515370007','g1008908','2023-10-04 00:00:00','2023-10-13 23:59:59','stcmNml','' | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM ( SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, TO_CHAR(B.PAYID) POSFORMNUM, TO_NUMBER(B.STATUS) BANKSTATUS, DECODE(SIGN(B.FEE), -1, (-1)*B.FEE, B.FEE) EXCHANGEAMT, '' NOTE, B.STATUSDATE BANKSTATUSDATE FROM RECEPTION A, CS_ORDER_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND LENGTH(REGEXP_REPLACE(A.ORDERID, '[^0-9]', '')) = LENGTH(A.ORDERID) AND A.ORDERID = B.ORDERID AND A.STATUS = ? AND B.PAYTYPE LIKE 'ThirdParty%' AND B.STATUS IN (0, 1, 9) AND B.STATUS = NVL(?, B.STATUS) ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:23:32.950281 | xxx.xx.xxx.xxx | 58201 | '','g1008908','2023-10-04 00:00:00','2023-10-13 23:59:59','stcmNml' | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM (SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, B.POSFORMNUM, 0 BANKSTATUS, 0 EXCHANGEAMT, '' NOTE, SYSDATE BANKSTATUSDATE FROM RECEPTION A, REC_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND B.REGION = 537 AND A.OID = B.OID AND A.STATUS = ? AND B.PAYTYPE IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'LastRefundFeePayType') ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:23:33.041884 | xxx.xx.xxx.xxx | 161288 | '','g1008908','2023-10-04 00:00:00','2023-10-13 23:59:59','stcmNml','0' | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM ( SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, TO_CHAR(B.PAYID) POSFORMNUM, TO_NUMBER(B.STATUS) BANKSTATUS, DECODE(SIGN(B.FEE), -1, (-1)*B.FEE, B.FEE) EXCHANGEAMT, '' NOTE, B.STATUSDATE BANKSTATUSDATE FROM RECEPTION A, CS_ORDER_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND LENGTH(REGEXP_REPLACE(A.ORDERID, '[^0-9]', '')) = LENGTH(A.ORDERID) AND A.ORDERID = B.ORDERID AND A.STATUS = ? AND B.PAYTYPE LIKE 'ThirdParty%' AND B.STATUS IN (0, 1, 9) AND B.STATUS = NVL(?, B.STATUS) ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:23:46.047832 | xxx.xx.xxx.xxx | 13550 | '','g1008908','2023-10-04 00:00:00','2023-10-13 23:59:59','stcmNml' | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM (SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, B.POSFORMNUM, 0 BANKSTATUS, 0 EXCHANGEAMT, '' NOTE, SYSDATE BANKSTATUSDATE FROM RECEPTION A, REC_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND B.REGION = 537 AND A.OID = B.OID AND A.STATUS = ? AND B.PAYTYPE IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'LastRefundFeePayType') ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:23:46.132146 | xxx.xx.xxx.xxx | 191241 | '','g1008908','2023-10-04 00:00:00','2023-10-13 23:59:59','stcmNml','' | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM ( SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, TO_CHAR(B.PAYID) POSFORMNUM, TO_NUMBER(B.STATUS) BANKSTATUS, DECODE(SIGN(B.FEE), -1, (-1)*B.FEE, B.FEE) EXCHANGEAMT, '' NOTE, B.STATUSDATE BANKSTATUSDATE FROM RECEPTION A, CS_ORDER_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND LENGTH(REGEXP_REPLACE(A.ORDERID, '[^0-9]', '')) = LENGTH(A.ORDERID) AND A.ORDERID = B.ORDERID AND A.STATUS = ? AND B.PAYTYPE LIKE 'ThirdParty%' AND B.STATUS IN (0, 1, 9) AND B.STATUS = NVL(?, B.STATUS) ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:24:07.439290 | xxx.xx.xxx.xxx | 15573 | '','g1008908','2023-10-04 00:00:00','2023-10-13 23:59:59','stcmNml' | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM (SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, B.POSFORMNUM, 0 BANKSTATUS, 0 EXCHANGEAMT, '' NOTE, SYSDATE BANKSTATUSDATE FROM RECEPTION A, REC_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND B.REGION = 537 AND A.OID = B.OID AND A.STATUS = ? AND B.PAYTYPE IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'LastRefundFeePayType') ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:24:07.516379 | xxx.xx.xxx.xxx | 33826 | '','g1008908','2023-10-04 00:00:00','2023-10-13 23:59:59','stcmNml','' | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM ( SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, TO_CHAR(B.PAYID) POSFORMNUM, TO_NUMBER(B.STATUS) BANKSTATUS, DECODE(SIGN(B.FEE), -1, (-1)*B.FEE, B.FEE) EXCHANGEAMT, '' NOTE, B.STATUSDATE BANKSTATUSDATE FROM RECEPTION A, CS_ORDER_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND LENGTH(REGEXP_REPLACE(A.ORDERID, '[^0-9]', '')) = LENGTH(A.ORDERID) AND A.ORDERID = B.ORDERID AND A.STATUS = ? AND B.PAYTYPE LIKE 'ThirdParty%' AND B.STATUS IN (0, 1, 9) AND B.STATUS = NVL(?, B.STATUS) ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:32:45.620899 | xxx.xx.xxx.xxx | 153 | | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM (SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, B.POSFORMNUM, 0 BANKSTATUS, 0 EXCHANGEAMT, '' NOTE, SYSDATE BANKSTATUSDATE FROM RECEPTION A, REC_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND B.REGION = 537 AND A.OID = B.OID AND A.STATUS = ? AND B.PAYTYPE IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'LastRefundFeePayType') ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:32:45.621986 | xxx.xx.xxx.xxx | 19292 | '13515370007','g1008908','2023-10-04 00:00:00','2023-10-13 23:59:59','stcmNml' | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM (SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, B.POSFORMNUM, 0 BANKSTATUS, 0 EXCHANGEAMT, '' NOTE, SYSDATE BANKSTATUSDATE FROM RECEPTION A, REC_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND B.REGION = 537 AND A.OID = B.OID AND A.STATUS = ? AND B.PAYTYPE IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'LastRefundFeePayType') ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:32:45.659780 | xxx.xx.xxx.xxx | 89 | | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM ( SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, TO_CHAR(B.PAYID) POSFORMNUM, TO_NUMBER(B.STATUS) BANKSTATUS, DECODE(SIGN(B.FEE), -1, (-1)*B.FEE, B.FEE) EXCHANGEAMT, '' NOTE, B.STATUSDATE BANKSTATUSDATE FROM RECEPTION A, CS_ORDER_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND LENGTH(REGEXP_REPLACE(A.ORDERID, '[^0-9]', '')) = LENGTH(A.ORDERID) AND A.ORDERID = B.ORDERID AND A.STATUS = ? AND B.PAYTYPE LIKE 'ThirdParty%' AND B.STATUS IN (0, 1, 9) AND B.STATUS = NVL(?, B.STATUS) ) C ORDER BY C.RECDATE DESC | | 2023-10-13 11:32:45.660588 | xxx.xx.xxx.xxx | 229575 | '13515370007','g1008908','2023-10-04 00:00:00','2023-10-13 23:59:59','stcmNml','0' | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM ( SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, TO_CHAR(B.PAYID) POSFORMNUM, TO_NUMBER(B.STATUS) BANKSTATUS, DECODE(SIGN(B.FEE), -1, (-1)*B.FEE, B.FEE) EXCHANGEAMT, '' NOTE, B.STATUSDATE BANKSTATUSDATE FROM RECEPTION A, CS_ORDER_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND LENGTH(REGEXP_REPLACE(A.ORDERID, '[^0-9]', '')) = LENGTH(A.ORDERID) AND A.ORDERID = B.ORDERID AND A.STATUS = ? AND B.PAYTYPE LIKE 'ThirdParty%' AND B.STATUS IN (0, 1, 9) AND B.STATUS = NVL(?, B.STATUS) ) C ORDER BY C.RECDATE DESC | +----------------------------+----------------+--------------+------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 23 rows in set (6.22 sec) ``` #### 注意 可以看到 SQL 有 PARAM_VALUE 的值。 - 新版本驱动 oceanbase-client-2.4.5.jar ```shell MySQL [oceanbase]> select usec_to_time(request_time),user_client_ip,elapsed_time,params_value,query_sql from gv$sql_audit where query_sql like '%SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POS%' and usec_to_time(request_time)>'2023-10-13 14:30:00' order by request_time desc limit 20; +----------------------------+----------------+--------------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | usec_to_time(request_time) | user_client_ip | elapsed_time | params_value | query_sql | +----------------------------+----------------+--------------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 2023-10-13 14:43:32.943112 | | 135 | | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM ( SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, TO_CHAR(B.PAYID) POSFORMNUM, TO_NUMBER(B.STATUS) BANKSTATUS, DECODE(SIGN(B.FEE), -1, (-1)*B.FEE, B.FEE) EXCHANGEAMT, '' NOTE, B.STATUSDATE BANKSTATUSDATE FROM RECEPTION A, CS_ORDER_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND LENGTH(REGEXP_REPLACE(A.ORDERID, '[^0-9]', '')) = LENGTH(A.ORDERID) AND A.ORDERID = B.ORDERID AND A.STATUS = ? AND B.PAYTYPE LIKE 'ThirdParty%' AND B.STATUS IN (0, 1, 9) AND B.STATUS = NVL(?, B.STATUS) ) C ORDER BY C.RECDATE DESC | | 2023-10-13 14:43:32.886470 | xxx.xx.xxx.xxx | 55710 | | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM ( SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, TO_CHAR(B.PAYID) POSFORMNUM, TO_NUMBER(B.STATUS) BANKSTATUS, DECODE(SIGN(B.FEE), -1, (-1)*B.FEE, B.FEE) EXCHANGEAMT, '' NOTE, B.STATUSDATE BANKSTATUSDATE FROM RECEPTION A, CS_ORDER_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND LENGTH(REGEXP_REPLACE(A.ORDERID, '[^0-9]', '')) = LENGTH(A.ORDERID) AND A.ORDERID = B.ORDERID AND A.STATUS = ? AND B.PAYTYPE LIKE 'ThirdParty%' AND B.STATUS IN (0, 1, 9) AND B.STATUS = NVL(?, B.STATUS) ) C ORDER BY C.RECDATE DESC | | 2023-10-13 14:43:32.885315 | xxx.xx.xxx.xxx | 114 | | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM ( SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, TO_CHAR(B.PAYID) POSFORMNUM, TO_NUMBER(B.STATUS) BANKSTATUS, DECODE(SIGN(B.FEE), -1, (-1)*B.FEE, B.FEE) EXCHANGEAMT, '' NOTE, B.STATUSDATE BANKSTATUSDATE FROM RECEPTION A, CS_ORDER_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND LENGTH(REGEXP_REPLACE(A.ORDERID, '[^0-9]', '')) = LENGTH(A.ORDERID) AND A.ORDERID = B.ORDERID AND A.STATUS = ? AND B.PAYTYPE LIKE 'ThirdParty%' AND B.STATUS IN (0, 1, 9) AND B.STATUS = NVL(?, B.STATUS) ) C ORDER BY C.RECDATE DESC | | 2023-10-13 14:43:32.789248 | | 94 | | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM (SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, B.POSFORMNUM, 0 BANKSTATUS, 0 EXCHANGEAMT, '' NOTE, SYSDATE BANKSTATUSDATE FROM RECEPTION A, REC_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND B.REGION = 537 AND A.OID = B.OID AND A.STATUS = ? AND B.PAYTYPE IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'LastRefundFeePayType') ) C ORDER BY C.RECDATE DESC | | 2023-10-13 14:43:32.771627 | xxx.xx.xxx.xxx | 16859 | | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM (SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, B.POSFORMNUM, 0 BANKSTATUS, 0 EXCHANGEAMT, '' NOTE, SYSDATE BANKSTATUSDATE FROM RECEPTION A, REC_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND B.REGION = 537 AND A.OID = B.OID AND A.STATUS = ? AND B.PAYTYPE IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'LastRefundFeePayType') ) C ORDER BY C.RECDATE DESC | | 2023-10-13 14:43:32.768153 | xxx.xx.xxx.xxx | 147 | | SELECT REGION, OID, FORMNUM, ORDERID, SERVNUMBER, RECDATE, RECDEFID, SUBRECDEFID, PAYTYPE, POSFORMNUM, BANKSTATUS, EXCHANGEAMT, NOTE, BANKSTATUSDATE FROM (SELECT A.REGION, A.OID, A.FORMNUM, A.ORDERID, A.SERVNUMBER, A.RECDATE, A.RECDEFID, A.SUBRECDEFID, B.PAYTYPE, B.POSFORMNUM, 0 BANKSTATUS, 0 EXCHANGEAMT, '' NOTE, SYSDATE BANKSTATUSDATE FROM RECEPTION A, REC_PAYTYPE B WHERE A.REGION = 537 AND A.SERVNUMBER = NVL(?, A.SERVNUMBER) AND A.RECOPID = NVL(?, A.RECOPID) AND A.RECDATE BETWEEN ? AND ? AND (A.ISROLLBACK = 1 OR A.RECDEFID IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'orderCommitButBackFeeRecType' AND STATUS = 1)) AND B.REGION = 537 AND A.OID = B.OID AND A.STATUS = ? AND B.PAYTYPE IN(SELECT DICTID FROM DICT_ITEM WHERE GROUPID = 'LastRefundFeePayType') ) C ORDER BY C.RECDATE DESC | +----------------------------+----------------+--------------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 6 rows in set (1.76 sec) ``` #### 注意 可以看到都没有 PARAM_VALUE 的值。 ## 关键诊断信息 ### 触发条件 JDBC 升级。 ### 事前巡检 1. 使用高于 oceanbase-client-2.2.6 版本的JDBC驱动。Oracle 模式下默认开启 cursor（V2.2.6 版本中，默认不开启 cursor）。 2. 使用存在问题的 OceanBase 数据库 V3.2.3 BP10 Hotfix3 之前的版本并且使用的 JDBC 版本高于 V2.2.6。 ### 事后诊断 1. 执行带参数的 SQL，但是查询 `GV$SQL_AUDIT` 视图发现 `PARAM_VALUE` 全部为空。 2. 客户链接串没有设置开启 cursor 的参数，但是抓包新驱动发包时协议 flags =1 开启了 cursor，JDBC V2.4.7.1 以下版本无法显式关掉 cursor。 ![78hui-yiy870-hj78i-hu789](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20231127kbacanshu.png) ## 问题原因 PARAM_VALUE 实现的时候没有考虑 cursor 的审计信息记录，需要对 cursor 的审计信息在 inner_open 阶段进行单独处理，客户遇到场景是升级到新版本 JDBC 驱动后是默认开启 cursor 由于代码层面没做 sql audit record 所以看不到 PARAM_VALUE 的值。 ## 问题的风险及影响 OceanBase 数据库执行带参数的 SQL 在 `GV$SQL_AUDIT` 视图中 `PARAM_VALUE` 缺失。 ## 影响租户 影响 OceanBase 数据库中的 SYS 租户和 Oracle 租户以及 MySQL 租户。 ## 影响的版本 OceanBase 数据库 V3.2.3 GA（oceanbase-3.2.3.0-20220418212020）及之后版本、V3.2.4 GA（oceanbase-3.2.4.0-100000072022102819）及之后版本。 ## 解决方法及规避方式 ### 解决方法 升级至问题已修复版本。目前已修复的版本包括 OceanBase 数据库 V3.2.3 BP10 Hotfix3（oceanbase-3.2.3.3-110030012023101913）、V3.2.3 BP10 Hotfix4（oceanbase-3.2.3.3-110040022023110614）、V3.2.3 BP10 Hotfix5（oceanbase-3.2.3.3-110050012023112210）、V3.2.3 BP10 Hotfix6（oceanbase-3.2.3.3-110060012024011615）、V3.2.3 BP10 Hotfix7（oceanbase-3.2.3.3-110070012024012316）、V3.2.3 BP10 Hotfix8（oceanbase-3.2.3.3-110080012024022713）、V3.2.3 BP10 Hotfix9（oceanbase-3.2.3.3-110090012024041611）、V3.2.3 BP10 Hotfix10（oceanbase-3.2.3.3-110100012024042814）、V3.2.3 BP10 Hotfix11（oceanbase-3.2.3.3-110110032024052217）、V3.2.3 BP10 Hotfix12（oceanbase-3.2.3.3-110120012024060510）、V3.2.3 BP10 Hotfix13（oceanbase-3.2.3.3-110130012024061819）、V3.2.3 BP10 Hotfix14（oceanbase-3.2.3.3-110140012024082010）、V3.2.3 BP10 Hotfix15（oceanbase-3.2.3.3-110150012024102111）、V3.2.3 BP10 Hotfix16（oceanbase-3.2.3.3-110160012025021315）、V3.2.3 BP10 Hotfix17（oceanbase-3.2.3.3-110170012025032112）、V3.2.3 BP10 Hotfix18（oceanbase-3.2.3.3-110180012025060316）、V3.2.3 BP10 Hotfix19（oceanbase-3.2.3.3-110190022025082615）、V3.2.3 BP10 Hotfix20（oceanbase-3.2.3.3-110200012025111317）、V3.2.3 BP11（oceanbase-3.2.3.3-111000032024070822）、V3.2.4 BP5（oceanbase-3.2.4.5-105000012023081513）。 ### 规避方式 新版本驱动默认开启 cursor 无法关闭，升级到已经修复的 OBServer 来彻底解决。 Previous [OceanBase 数据库 V2.2.77 版本查询 `gv$obrpc_outgoing` 和 `gv$obrpc_incoming` 报错 -4016 的原因和解决方法](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000001961833) Next [OceanBase 数据库 V4.x 版本中因 __all_virtual_sql_audit 查询超时导致死锁信息无法成功记录到视图 __all_virtual_deadlock_event_history](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000003188178) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线