oracle+修改实例字符集,Oracle 11g_rac_更改字符集

Oracle

11g_rac_更改字符集

背景：

在使用ORACLE的过程中,会出现各种各样的问题,各种各样的错误,其中ORA-12899就是前段时间我在将数据导入到我本地机器上的时候一直出现的问题.不过还好已经解决了这个问题,现在分享一下,解决方案;

出现ORA-12899,是字符集引起的,中文在UTF-8中占3个字节,ZHS16GBK中占2个字节,而源dmp文件字符集是ZHS16GBK库里倒出来的数据,现在要导入到目标字符集为UTF-8的库里,所以会出现ORA-12899

其实只要修改一下ORACLE

的字符集就可以很好的解决这个问题;

1.查看现有字符集(rac1)

SQL> select value from

NLS_DATABASE_PARAMETERS where

parameter='NLS_CHARACTERSET';

VALUE

--------------------------------------------------------------------------------

AL32UTF8

SQL> select instance_name

from v$instance;

INSTANCE_NAME

----------------

orcl11

SQL> alter system set

cluster_database=false scope=spfile sid='orcl11';

System altered.

红色部分对于单实例的是不要加的，对于rac来说必须加上，不然会报

ORA-12720: operation requires database is in

EXCLUSIVE mode

2、停止数据库(rac1和rac2)

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

3、启动数据库(rac1)

SQL> startup

nomount;

ORACLE instance

started.

Total System Global Area

2.0243E+10 bytes

Fixed

Size 2237088

bytes

Variable

Size 3825208672 bytes

Database

Buffers 1.6375E+10 bytes

Redo

Buffers 41189376 bytes

SQL>Alter database

mount exclusive;

Database altered.

SQL> Alter system

enable restricted session;

System altered.

SQL>ALTER SYSTEM SET

JOB_QUEUE_PROCESSES=0;

System altered.

SQL> Alter database

open;

Database altered.

SQL>

4、修改字符集(rac1)

SQL> ALTER DATABASE

character set INTERNAL_USE zhs16gbk;

Database altered.

SQL> alter system set

cluster_database=true scope=spfile sid='orcl11';

System altered.

5、验证(rac1和rac2)

SQL> shutdown

immediate;

Database closed.

Database

dismounted.

ORACLE instance shut

down.

SQL> startup;

ORACLE instance

started.

Total System Global Area

2.0243E+10 bytes

Fixed

Size 2237088

bytes

Variable

Size 3825208672 bytes

Database

Buffers 1.6375E+10 bytes

Redo

Buffers 41189376 bytes

Database mounted.

Database opened.

SQL> select value from

NLS_DATABASE_PARAMETERS where

parameter='NLS_CHARACTERSET';

VALUE

--------------------------------------------------------------------------------

ZHS16GBK

两边的结果是一样的！