ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20131030]ORA-29275与toad 12.txt

[20131030]ORA-29275与toad 12.txt

原创 Linux操作系统 作者:lfree 时间:2013-10-30 16:17:31 0 删除 编辑
[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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-775364/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2457
  • 访问量
    6262176