oracle 查看、修改字符集编码

oracle 查看、修改字符集编码

      ALTER DATABASE CHARACTER SET ZHS16GBK;
     出现了error 
     ORA-12712: new character set must be a superset of old character set 

    method ：ALTER DATABASE character set INTERNAL_USE ZHS16GBK;

写道
SQL> conn /as sysdba 
Connected. 
SQL> shutdown immediate; 
Database closed. 
Database dismounted. 
ORACLE instance shut down. 
SQL> startup mount 
ORACLE instance started. 

Total System Global Area  236000356 bytes 
Fixed Size                   451684 bytes 
Variable Size             201326592 bytes 
Database Buffers           33554432 bytes 
Redo Buffers                 667648 bytes 
Database mounted. 
SQL> ALTER SESSION SET SQL_TRACE=TRUE;//语句跟踪 
System altered. 
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION; 

System altered. 

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 

System altered. 

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0; 

System altered. 

SQL> alter database open; 

Database altered. 

SQL> ALTER DATABASE CHARACTER SET ZHS16GBK; 
ALTER DATABASE CHARACTER SET ZHS16GBK 
* 
ERROR at line 1: 
ORA-12712: new character set must be a superset of old character set 


 
 
提示我们的字符集：新字符集必须为旧字符集的超集，这时我们可以跳过超集的检查做更改： 
 
SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK; 
 
Database altered. 
 
SQL> 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                                                  DD-MON-RR 
NLS_DATE_LANGUAGE                                                AMERICAN 
NLS_CHARACTERSET                                                 ZHS16GBK 
NLS_SORT                                                         BINARY 
NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM 
 
PARAMETER                                                        VALUE 
---------------------------------------------------------------- --------------- 
NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI 
NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM 
NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI 
NLS_DUAL_CURRENCY                                                $ 
NLS_NCHAR_CHARACTERSET                                           UTF8 
NLS_COMP                                                         BINARY 
NLS_LENGTH_SEMANTICS                                             BYTE 
NLS_NCHAR_CONV_EXCP                                              FALSE 
 
19 rows selected. 
 
 
重启检查是否更改完成： 
 
SQL> shutdown immediate; 
Database closed. 
Database dismounted. 
ORACLE instance shut down. 
SQL> startup 
ORACLE instance started. 
 
Total System Global Area  236000356 bytes 
Fixed Size                   451684 bytes 
Variable Size             201326592 bytes 
Database Buffers           33554432 bytes 
Redo Buffers                 667648 bytes 
Database mounted. 
Database opened. 
SQL> 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                                                  DD-MON-RR 
NLS_DATE_LANGUAGE                                                AMERICAN 
NLS_CHARACTERSET                                                 ZHS16GBK 
NLS_SORT                                                         BINARY 
NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM 
 
PARAMETER                                                        VALUE 
---------------------------------------------------------------- --------------- 
NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI 
NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM 
NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI 
NLS_DUAL_CURRENCY                                                $ 
NLS_NCHAR_CHARACTERSET                                           UTF8 
NLS_COMP                                                         BINARY 
NLS_LENGTH_SEMANTICS                                             BYTE 
NLS_NCHAR_CONV_EXCP                                              FALSE 
 
19 rows selected. 


其实没有那么麻烦 ，只有一下几步：
1、 将数据库至于restricted mode 否则会报下面的错误

引用

SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
ALTER DATABASE character set INTERNAL_USE ZHS16GBK
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode

SQL> alter system enable restricted session;

System altered.

SQL> select status,logins from v$instance;

STATUS      LOGINS
------------ ----------
OPEN      RESTRICTED
可以查看当前数据库存储什么状态

SQL> alter system disable restricted session;

System altered.

重启数据库就默认恢复为正常状态,即 allowed。

PS:

引用

RESTRICTED SESSION
The RESTRICTED SESSION clause lets you restrict logon to Oracle Database. You can
use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.
    Specify ENABLE to allow only users with RESTRICTED SESSION system privilege to log on to Oracle Database. Existing sessions are not terminated.This clause applies only to the current instance.Therefore, in an Oracle RAC environment, authorized users without the RESTRICTED SESSION system privilege can still access the database by way of other instances. Specify DISABLE to reverse the effect of the ENABLE RESTRICTED SESSION clause, allowing all users with CREATE SESSION system privilege to log on to Oracle Database. This is the default.

2、
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
     出现了error 
     ORA-12712: new character set must be a superset of old character set 

SQL> alter database character set internal_use zhs16gbk;

Database altered.

评论

1 楼 sunningqu 2013-01-30  

  很好，昨晚我就遇到这个问题了。