[20220105]再论ORA-29275与toad 12.txt

[20220105]再论ORA-29275与toad 12.txt --//以前的链接 [20131030]ORA-29275与toad 12.txt => http://blog.itpub.net/267265/viewspace-775364/ --//别人问当时出现版本是10g,为什么现在11g还是存在这个问题.实际上问题在于执行DBMS_APPLICATION_INFO.set_action截取了32各字 --//符.如果截取出现半个汉字,问题再现,我个人感觉目前toad 12版本无法解决这个问题,除非你修改一些视图定义,这样存在一些不必要 --//的风险. 1.环境: SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.测试: SCOTT@book> @ desc_proc sys DBMS_APPLICATION_INFO set_action INPUT OWNER PACKAGE_NAME OBJECT_NAME sample : @desc_proc sys dbms_stats gather_%_stats OWNER      PACKAGE_NAME          OBJECT_NAME                      SEQUENCE ARGUMENT_NAME                  DATA_TYPE                      IN_OUT    DEFAULTED ---------- --------------------- ------------------------------ ---------- ------------------------------ ------------------------------ --------- ---------- SYS        DBMS_APPLICATION_INFO SET_ACTION                              1 ACTION_NAME                    VARCHAR2                       IN        N SCOTT@book> exec DBMS_APPLICATION_INFO.set_action('1234567890123456789012345678901234567890123456789012345678901文化'); PL/SQL procedure successfully completed. SCOTT@book> select module,action,length(action) from v$session where substr(action,1,2)='12'; MODULE       ACTION                                                           LENGTH(ACTION) ------------ ---------------------------------------------------------------- -------------- SQL*Plus     12345678901234567890123456789012                                             32 --//实际上在11g下执行exec DBMS_APPLICATION_INFO.set_action时,仅仅截取32长度. --//如果执行如下,这样"文"的字符一半被截取, --//文 编码 cec4. SCOTT@book> exec DBMS_APPLICATION_INFO.set_action('1234567890123456789012345678901文化'); PL/SQL procedure successfully completed. SCOTT@book> select module,action,length(action) from v$session where substr(action,1,2)='12'; MODULE       ACTION                                                           LENGTH(ACTION) ------------ ---------------------------------------------------------------- -------------- SP2-0784: Invalid or incomplete character beginning 0xCE returned --//0xCE的编码就是文的前半部分。 3.toad受影响的范围: --//实际上不仅仅影响session browser,也应该sga trace模块一样受影响。实际上主要原因是许多视图定义支持varchar2(64),而 --//DBMS_APPLICATION_INFO.set_action没有更正过来。我个人也不知道什么好方法解决这个问题. 4.测试19c下情况： SYS@127.0.0.1:17101/dyhis> @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.9.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. SYS@127.0.0.1:17101/dyhis> exec DBMS_APPLICATION_INFO.set_action('1234567890123456789012345678901234567890123456789012345678901文化'); PL/SQL procedure successfully completed. SYS@127.0.0.1:17101/dyhis> select module,action,length(action) from v$session where substr(action,1,2)='12'; MODULE       ACTION                                                           LENGTH(ACTION) ------------ ---------------------------------------------------------------- -------------- SQL*Plus     1234567890123456789012345678901234567890123456789012345678901文              63 --//可以看出19c已经修复了这个问题，不会出现半个汉字的情况，后面的LENGTH(ACTION)=63. SYS@127.0.0.1:17101/dyhis> select length(substrb('1234567890123456789012345678901234567890123456789012345678901文化',1,64)) N10 from dual;                   N10 ---------------------                    63 --//而11g下就存在问题。 SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SCOTT@book> select length(substrb('1234567890123456789012345678901文化',1,32)) N10 from dual;                   N10 ---------------------                    32