如何在RAC环境下修改Oracle字符集
SQL> conn /as sysdba
SQL> shutdown immediate;
SQL> startup mount
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> alter database open;
SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
SQL> select from v$nls_parameters;
SQL> shutdown immediate;
SQL> startup
Database character set (AL32UTF8) and Client characterset (ZHS16GBK) are different
Character set conversion may cause unexpected results
Note: you can set the client character set through the NLS_LANG environmentvariable or the NLS_LANG registry key in
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb11g_home1
SIMPLIFIED CHINESE_CHINAZHS16GBK 包含 AMERICAN_AMERICAAL32UTF8
这可是个麻烦事,不是改客户端字符集的问题。要改数据库的字符集。我是自己笔记本上面的oracle11g,所以胆子还是很大的:
SQL> conn /as sysdba
SQL> shutdown immediate;
SQL> startup mount
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> alter database open;
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
ORA-12712: new character set must be a superset of old character set
提示我们的字符集:新字符集必须为旧字符集的超集,这时我们可以跳过超集的检查做更改:
SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
--我们看到这个过程和之前ALTER DATABASE CHARACTER SET操作的内部过程是完全相同的,也就是说INTERNAL_USE提供的帮助就是使Oracle数据库绕过了子集与超集的校验
SQL> select from v$nls_parameters;
SQL> shutdown immediate;
SQL> startup
SQL> select from v$nls_parameters;
以后安装oracle11g的时候记得选择自定义安装,把这个字符集的事情事先弄好。
服务端修改字符集有点麻烦,要使客户端字符集与之一致,还有以前的dmp文件可能作废,
最好是修改client端的会话的字符集环境。
SQL>SHUTDOWN
IMMEDIATE;
SQL>STARTUP
MOUNT
EXCLUSIVE;
SQL>ALTER
SYSTEM
ENABLE
RESTRICTED
SESSION;
SQL>ALTER
SYSTEM
SET
JOB_QUEUE_PROCESSES=0;
SQL>ALTER
SYSTEM
SET
AQ_TM_PROCESSES=0;
SQL>ALTER
DATABASE
OPEN;
SQL>ALTER
DATABASE
NATIONAL
CHARACTER
SET
INTERNAL_USE
UTF8;
SQL>SHUTDOWN
immediate;
SQL>startup;
0条评论