[20131030]ORA-29275与toad 12.txt
$ oerr ora 29275
29275, 00000, "partial multibyte character"
// *Cause: The requested read operation could not complete because a partial
// multibyte character was found at the end of the input.
// *Action: Ensure that the complete multibyte character is sent from the
// remote server and retry the operation. Or read the partial
// multibyte character as RAW.
今天使用toad 12 (64位版本)，遇到一个问题，在使用session browser模块时，出现提示：
ORA-29275: 部分多字节字符
跟踪确定在执行以下语句出错。
SELECT round(bitand(s.ownerid, 65535)) parent_session_sid, round(bitand(s.ownerid, 16711680)/65536) parent_session_instid, rawtohex(SADDR) as saddr, s.SID, s.SERIAL#,
s.AUDSID, rawtohex(PADDR) as paddr, s.USER#, s.USERNAME, s.COMMAND, s.OWNERID, s.TADDR, s.LOCKWAIT, s.STATUS, s.SERVER, s.SCHEMA#, s.SCHEMANAME, s.OSUSER, s.PROCESS,
s.MACHINE, s.TERMINAL, UPPER(s.PROGRAM) PROGRAM, s.TYPE, s.SQL_ADDRESS, s.SQL_HASH_VALUE, s.SQL_ID, s.SQL_CHILD_NUMBER, s.PREV_SQL_ADDR, s.PREV_HASH_VALUE,
s.PREV_SQL_ID, s.PREV_CHILD_NUMBER, s.PLSQL_ENTRY_OBJECT_ID, s.PLSQL_ENTRY_SUBPROGRAM_ID, s.PLSQL_OBJECT_ID, s.PLSQL_SUBPROGRAM_ID, s.MODULE, s.MODULE_HASH, s.ACTION,
s.ACTION_HASH, s.CLIENT_INFO, s.FIXED_TABLE_SEQUENCE, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW#, s.LOGON_TIME, s.LAST_CALL_ET,
s.PDML_ENABLED, s.FAILOVER_TYPE, s.FAILOVER_METHOD, s.FAILED_OVER, s.RESOURCE_CONSUMER_GROUP, s.PDML_STATUS, s.PDDL_STATUS, s.PQ_STATUS, s.CURRENT_QUEUE_DURATION,
s.CLIENT_IDENTIFIER, s.BLOCKING_SESSION_STATUS, s.BLOCKING_INSTANCE, s.BLOCKING_SESSION, s.SEQ#, s.EVENT#, s.EVENT, s.P1TEXT, s.P1, s.P1RAW, s.P2TEXT, s.P2, s.P2RAW,
s.P3TEXT, s.P3, s.P3RAW, s.WAIT_CLASS_ID, s.WAIT_CLASS#, s.WAIT_CLASS, s.WAIT_TIME, s.SECONDS_IN_WAIT, s.STATE, s.SERVICE_NAME, s.SQL_TRACE, s.SQL_TRACE_WAITS,
s.SQL_TRACE_BINDS
FROM V$SESSION S
WHERE ( (s.USERNAME is not null)
AND (NVL(s.osuser,'x') 'SYSTEM')
AND (s.type 'BACKGROUND') );
我一点一点注释字段，最后确定是v$session.action字段注解后单独执行正常。
SQL> select module,action from v$session where action is not null;
MODULE ACTION
-------------------- --------------------------------
PL/SQL Developer Main session
PL/SQL Developer Main session
PL/SQL Developer SQL Window - New
PL/SQL Developer SQL Window - New
PL/SQL Developer SQL Window - New
PL/SQL Developer SQL Window - 将pd_dlb中垃圾数据
--很明显，这个是使用工具PL/SQL Developer时调用DBMS_APPLICATION_INFO.set_action模块设置的。
desc v$session
Name Null? Type
------------- -------- ----------------------------------------------------------------------------
....
ACTION VARCHAR2(32)
ACTION_HASH NUMBER
...
生产系统10g版本，我与我的测试环境做了对比发现11G
ACTION VARCHAR2(64)
--但是如果在11g下执行:
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> exec DBMS_APPLICATION_INFO.set_action('1234567890123456789012345678901234567890123456789012345678901文化');
PL/SQL procedure successfully completed.
SCOTT@test> select module,action from v$session where action is not null;
MODULE ACTION
------------ ----------------------------------------------------------------
Streams QMON Coordinator
KTSJ KTSJ Slave
SQL*Plus 12345678901234567890123456789012
KTSJ KTSJ Coordinator
Streams QMON Slave
KTSJ KTSJ Slave
KTSJ KTSJ Slave
Streams QMON Slave
8 rows selected.
--可以发现仅仅显示32宽度,后面的信息被截断了。如果执行如下语句，在视图v$session将显示字段action的'文'的一半进入，另外一半
--丢失。
SCOTT@test> exec DBMS_APPLICATION_INFO.set_action('1234567890123456789012345678901文化');
PL/SQL procedure successfully completed.
-- 成功！
SCOTT@test> select module,action from v$session where action is not null;
MODULE ACTION
------------ ----------------------------------------------------------------
Streams QMON Coordinator
KTSJ KTSJ Slave
KTSJ KTSJ Coordinator
Streams QMON Slave
KTSJ KTSJ Slave
KTSJ KTSJ Slave
Streams QMON Slave
SP2-0784: Invalid or incomplete character beginning 0xCE returned
8 rows selected.
--出现SP2-0784.
$ oerr SP2 0784
00784,0, "Invalid or incomplete character beginning 0x%02X returned\n"
// *Cause: Attempted to return a string from the database that contained
// an invalid or incomplete character.
// *Action: Replace the invalid or incomplete string in the database with
// a valid or complete string.
--再在toad 12下查询就会出现如上错误。
ORA-29275: 部分多字节字符
--而在toad 11下这个问题不存在。
--而且很奇怪，与client有关。
使用12c的client：
16:09:50 SQL> select module,action from v$session where substr(action,1,2)='12';
未选定行
16:10:28 SQL> exec DBMS_APPLICATION_INFO.set_action('1234567890123456789012345678901文化');
PL/SQL 过程已成功完成。
16:10:31 SQL> select module,action from v$session where substr(action,1,2)='12';
MODULE ACTION
---------------------------------------------------------------- ---------------------------------------
SQL*Plus 1234567890123456789012345678901
--正常！
而11g的client：
SCOTT@test> select module,action from v$session where substr(action,1,2)='12';
no rows selected
SCOTT@test> exec DBMS_APPLICATION_INFO.set_action('1234567890123456789012345678901文化');
PL/SQL procedure successfully completed.
SCOTT@test> select module,action from v$session where substr(action,1,2)='12';
MODULE ACTION
------------ ----------------------------------------------------------------
SP2-0784: Invalid or incomplete character beginning 0xCE returned
SCOTT@test> select module,substr(action,1,31),dump(action) c60 from v$session where substr(action,1,2)='12';
MODULE SUBSTR(ACTION,1,31) C60
------------ -------------------------------------------------------------- ------------------------------------------------------------
SQL*Plus 1234567890123456789012345678901 Typ=1 Len=32: 49,50,51,52,53,54,55,56,57,48,49,50,51,52,53,5
4,55,56,57,48,49,50,51,52,53,54,55,56,57,48,49,206