[20221123]19cDBA_EXPRESSION_STATISTICS查询expression_text中字符串带双引号的问题

[20221123]19cDBA_EXPRESSION_STATISTICS查询expression_text中字符串带双引号的问题.txt --//今天遇到一个小问题,就是查询DBA_EXPRESSION_STATISTICS视图 expression_text中字符串带双引号的问题.无法使用等于方式查询, --//而实际上的情况是字符串结尾包含\0,通过例子说明问题: 1.环境: SYS@192.168.100.235:1521/orcl> @ pr ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 19.0.0.0.0 BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.测试: SYS@192.168.100.235:1521/orcl> SELECT *   FROM SYS.DBA_EXPRESSION_STATISTICS  WHERE last_modified   >= trunc(sysdate)    AND table_name      =  'LIS_TEST'    AND expression_text =  '"TEST_NO"'; no rows selected SYS@192.168.100.235:1521/orcl>  SELECT *   FROM SYS.DBA_EXPRESSION_STATISTICS  WHERE last_modified >= trunc(sysdate)    AND table_name    =  'LIS_TEST'    AND expression_text like '%TEST_NO%'    AND rownum        =  1   2  @ pr ============================== OWNER                         : LIS TABLE_NAME                    : LIS_TEST EXPRESSION_ID                 : 3602778108646932730 SNAPSHOT                      : LATEST EVALUATION_COUNT              : 13136741 FIXED_COST                    : .0000000268215720022949 DYNAMIC_COST                  : EXPRESSION_TEXT               : "TEST_NO" CREATED                       : 2020-11-27 17:18:02 LAST_MODIFIED                 : 2022-11-23 10:38:37 PL/SQL procedure successfully completed. SYS@192.168.100.235:1521/orcl> select expression_text,dump(expression_text) from SYS.DBA_EXPRESSION_STATISTICS where last_modified >=trunc(sysdate)and table_name='LIS_TEST' and expression_text  like  '%TEST_NO%' and rownum=1   2  @ pr ============================== EXPRESSION_TEXT               : "TEST_NO" DUMP(EXPRESSION_TEXT)         : Typ=1 Len=10: 34,84,69,83,84,95,78,79,34,0 PL/SQL procedure successfully completed. --//注意看实际上结尾存在\0字符.改写如下就ok了. SYS@192.168.100.235:1521/orcl> select * from SYS.DBA_EXPRESSION_STATISTICS where last_modified >=trunc(sysdate)and table_name='LIS_TEST' and expression_text  = '"TEST_NO"'||chr(0)   2  @ pr ============================== OWNER                         : LIS TABLE_NAME                    : LIS_TEST EXPRESSION_ID                 : 3602778108646932730 SNAPSHOT                      : LATEST EVALUATION_COUNT              : 13136741 FIXED_COST                    : .0000000268215720022949 DYNAMIC_COST                  : EXPRESSION_TEXT               : "TEST_NO" CREATED                       : 2020-11-27 17:18:02 LAST_MODIFIED                 : 2022-11-23 10:38:37 ============================== OWNER                         : LIS TABLE_NAME                    : LIS_TEST EXPRESSION_ID                 : 3602778108646932730 SNAPSHOT                      : CUMULATIVE EVALUATION_COUNT              : 25595018601 FIXED_COST                    : .0000000268215720022949 DYNAMIC_COST                  : EXPRESSION_TEXT               : "TEST_NO" CREATED                       : 2020-11-27 17:18:02 LAST_MODIFIED                 : 2022-11-23 10:38:37 PL/SQL procedure successfully completed.