[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