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