如何修改数据库字符集

环境:

OS:Red Hat Linux As 5

DB:10.2.0.4

 

通常情况下,字符集是在安装的时候选定好的,需要修改数据库的字符集Oracle建议的做法是重建数据库(EXP/EXPDP导出后再导入),虽然Oracle官方文档也有说如何修改字符集,但这不是Orale推荐的方法.下面是Oracle官方文档中提到的如何修改字符集的方法.

 

1.查看当前数据库字符集
select * from nls_database_parameters
where parameter='NLS_CHARACTERSET';
-----------------------------------
PARAMETER         VALUE
NLS_CHARACTERSET WE8ISO8859P1

 

这里的字符集WE8ISO8859P1是不支持中文的

 

2.将字符集修改为中文字符集ZHS16GBK

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area  734003200 bytes
Fixed Size                  1221564 bytes
Variable Size             264244292 bytes
Database Buffers          465567744 bytes
Redo Buffers                2969600 bytes
Database mounted.
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

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> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  734003200 bytes
Fixed Size                  1221564 bytes
Variable Size             264244292 bytes
Database Buffers          465567744 bytes
Redo Buffers                2969600 bytes
Database mounted.
Database opened.

 

这样将数据库的字符集由原来的WE8ISO8859P1修改成了ZHS16GBK,对原来的字符集有中文的显示的还是乱码,但对新入库的中文字符集就能正常显示.

 
9i RAC环境下修改
关闭其他实例,以下步骤在实例1上进行

SQL>alter system set cluster_database=false scope=spfile sid='racdb1';

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area  588322312 bytes
Fixed Size                   452104 bytes
Variable Size             251658240 bytes
Database Buffers          335544320 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> show parameters cluster;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1
cluster_interconnects                string
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE UTF8;

Database altered.

SQL> alter database national character set internal_use utf8;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict;
ORACLE instance started.

Total System Global Area  588322312 bytes
Fixed Size                   452104 bytes
Variable Size             251658240 bytes
Database Buffers          335544320 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  588322312 bytes
Fixed Size                   452104 bytes
Variable Size             251658240 bytes
Database Buffers          335544320 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> alter system set cluster_database=true scope=spfile sid='racdb1';

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  588322312 bytes
Fixed Size                   452104 bytes
Variable Size             251658240 bytes
Database Buffers          335544320 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

SQL> alter system set job_queue_processes=300;

System altered.

SQL> alter system set aq_tm_processes=1;

节点2：

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  588322312 bytes
Fixed Size                   452104 bytes
Variable Size             251658240 bytes
Database Buffers          335544320 bytes
Redo Buffers                 667648 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode

SQL> show parameters clust;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1
cluster_interconnects                string
SQL> alter system set cluster_database=true scope=spfile sid='racdb2';

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  588322312 bytes
Fixed Size                   452104 bytes
Variable Size             251658240 bytes
Database Buffers          335544320 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

-- The End --