查询v$session报ORA-29275
客户反馈查询v$session碰到ORA-29275错误
按经验是ACTION列的问题，以前用toad时碰到过
先去掉ACTION列查询一把：
select SADDR ,
SID ,
SERIAL# ,
AUDSID ,
PADDR ,
USER# ,
USERNAME ,
COMMAND ,
OWNERID ,
TADDR ,
LOCKWAIT ,
STATUS ,
SERVER ,
SCHEMA# ,
SCHEMANAME ,
OSUSER ,
PROCESS ,
MACHINE ,
PORT ,
TERMINAL ,
PROGRAM ,
TYPE ,
SQL_ADDRESS ,
SQL_HASH_VALUE ,
SQL_ID ,
SQL_CHILD_NUMBER ,
SQL_EXEC_START ,
SQL_EXEC_ID ,
PREV_SQL_ADDR ,
PREV_HASH_VALUE ,
PREV_SQL_ID ,
PREV_CHILD_NUMBER ,
PREV_EXEC_START ,
PREV_EXEC_ID ,
PLSQL_ENTRY_OBJECT_ID ,
PLSQL_ENTRY_SUBPROGRAM_ID ,
PLSQL_OBJECT_ID ,
PLSQL_SUBPROGRAM_ID ,
MODULE ,
MODULE_HASH ,
--ACTION ,
ACTION_HASH ,
CLIENT_INFO ,
FIXED_TABLE_SEQUENCE ,
ROW_WAIT_OBJ# ,
ROW_WAIT_FILE# ,
ROW_WAIT_BLOCK# ,
ROW_WAIT_ROW# ,
TOP_LEVEL_CALL# ,
LOGON_TIME ,
LAST_CALL_ET ,
PDML_ENABLED ,
FAILOVER_TYPE ,
FAILOVER_METHOD ,
FAILED_OVER ,
RESOURCE_CONSUMER_GROUP ,
PDML_STATUS ,
PDDL_STATUS ,
PQ_STATUS ,
CURRENT_QUEUE_DURATION ,
CLIENT_IDENTIFIER ,
BLOCKING_SESSION_STATUS ,
BLOCKING_INSTANCE ,
BLOCKING_SESSION ,
FINAL_BLOCKING_SESSION_STATUS ,
FINAL_BLOCKING_INSTANCE ,
FINAL_BLOCKING_SESSION ,
SEQ# ,
EVENT# ,
EVENT ,
P1TEXT ,
P1 ,
P1RAW ,
P2TEXT ,
P2 ,
P2RAW ,
P3TEXT ,
P3 ,
P3RAW ,
WAIT_CLASS_ID ,
WAIT_CLASS# ,
WAIT_CLASS ,
WAIT_TIME ,
SECONDS_IN_WAIT ,
STATE ,
WAIT_TIME_MICRO ,
TIME_REMAINING_MICRO ,
TIME_SINCE_LAST_WAIT_MICRO ,
SERVICE_NAME ,
SQL_TRACE ,
SQL_TRACE_WAITS ,
SQL_TRACE_BINDS ,
SQL_TRACE_PLAN_STATS ,
SESSION_EDITION_ID ,
CREATOR_ADDR ,
CREATOR_SERIAL# ,
ECID from v$session ;
一切正常，接下来看是哪些会话的ACTION列导致的问题：
拼接SQL来执行：
select 'select action from v$session where sid='||sid||';' from v$session ;
得到结果：
SQL> select action from v$session where sid=7529;
ERROR:
ORA-29275: partial multibyte character
SQL> select action from v$session where sid=1513;
ERROR:
ORA-29275: partial multibyte character
SQL> select sid,serial#,sql_id,username,program,module,substr(action,-1,100) action from v$session where sid in (7529,1513);
SID SERIAL# SQL_ID USERNAME PROGRAM MODULE AC
---------- ---------- ------------- ------------------------------ ------------------------------------------------ ---------------------- --
1513 10519 ***** plsqldev.exe PL/SQL Developer ?
7529 635 ******** plsqldev.exe PL/SQL Developer ?
检查数据库字符集为：ZHS16CGB231280
这个字符集应该是ZHS16GBK的一个子集，比较老的系统才会用这个字符集了（这个数据库是从9i升级上来的），现在国内主流是ZHS16GBK了，这就存在一个问题，ZHS16BK中的部分字符集在ZHS16CGB231280中是非法的，比如0XCD，这样客户端PLSQL/Developer在调用DBMS_APPLICATION_INFO.set_action的时候就可能传入被认为非法的字符，导致查询的时候出错。
解决办法：
1、KILL原来的导致问题的会话，对v$session的查询恢复正常
2、为防止以后再出现此类问题：
linux的话export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280"
Windows的话新建系统环境变量NLS_LANG，值为SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280