[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