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 否则会报下面的错误
[quote]
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
[/quote]
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:
[quote]
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.
[/quote]

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.