更改Oracle10g服务端字符集

因为数据库的字符使用的是WE8ISO8859P1，不支持中文显示，故要换成AL32UTF8

 

1.在更改数据库字符集之前先备份一下数据

exp test/test@orcl file=/usr/local/20110408.dmp

 

2.查看当前数据库的字符集,其来源于props$，是表示数据库的字符集
select * from nls_database_parameters
select userenv('language') from dual
其中NLS_CHARACTERSET 为server端字符集
NLS_LANGUAGE 为 server端字符显示形式

 

3.修改数据库服务端字符集(WE8ISO8859P1 --> AL32UTF8)

首要条件是：将数据库启动到RESTRICTED模式下做字符集更改

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 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 AL32UTF8;
ALTER DATABASE CHARACTER SET AL32UTF8
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
提示我们的字符集：新字符集必须为旧字符集的超集，这时我们可以跳过超集的检查做更改：
SQL> ALTER DATABASE character set INTERNAL_USE AL32UTF8;
Database altered.
SQL> select * from v$nls_parameters;
重启检查是否更改完成：

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;

 

 

4.数据库服务端字符集更改完成，但在clob字段里出现乱码情况。

解决办法：把20110408.dmp中有clob表导入已变更字符集的oralce数据库中，

clob是没有乱码的，再使用update语句把clob字段数据更新至原数据库的clob字段