[20160401]修改props$中字符集导致问题.txt

[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
----------
森
--千万不要在生产系统做这种测试!!!

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

转载于:http://blog.itpub.net/267265/viewspace-2073746/