[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.