[20160401]修改props$中字符集导致问题.txt
--介绍一种简单的修复方法.
--修改以后很容易修复.我仅仅测试情况的情况.
1.环境:
SYS@book> @ &r/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
$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK
SYS@book> column comment$ format a40
SYS@book> select rowid,a.* from props$ a where name='NLS_CHARACTERSET';
ROWID NAME VALUE$ COMMENT$
------------------ ---------------------------------------- -------------------- ----------------------------------------
AAAABiAABAAAAMhAAQ NLS_CHARACTERSET ZHS16GBK Character set
SYS@book> @ &r/rowid AAAABiAABAAAAMhAAQ
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
98 1 801 16 1,801 alter system dump datafile 1 block 801 ;
SYS@book> update props$ set value$=' ' where name='NLS_CHARACTERSET' ;
1 row updated.
SYS@book> commit ;
Commit complete.
--也就是讲如果修改错误,仅仅通过bbed或者bvi之类的工具将它设置为空格就ok了.
--进入再修改正确的值.
2.重启看看.
SYS@book> startup
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
Database opened.
--可以正常登录.检查
SYS@book> select rowid,a.* from props$ a where name='NLS_CHARACTERSET';
ROWID NAME VALUE$ COMMENT$
------------------ -------------------- ------------------------------ ------------------------------
AAAABiAABAAAAMhAAQ NLS_CHARACTERSET Character set
SYS@book> select * from v$nls_parameters ;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET US7ASCII
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT YYYY-MM-DD HH24:MI:SS.FF
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
19 rows selected.
--注意~. NLS_CHARACTERSET ='US7ASCII',这样是不能使用的,如果显示中文是乱码.
SYS@book> select '森' c10 from dual ;
C10
----------
?
SYS@book> update props$ set value$='ZHS16GBK' where name='NLS_CHARACTERSET' ;
1 row updated.
SYS@book> commit ;
Commit complete.
--再重启ok.要退出再进入,中文显示就ok了.
SYS@book> select '森' c10 from dual ;
C10
----------
森
--千万不要在生产系统做这种测试!!!