修改 Oracle10g 数据库字符集的方法

 

修改oracle10g数据库字符集的方法：

 

 

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

 

SQL> select name,value$ from props$ where name like '%NLS%';

 

 

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;

 

SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

 

 

 

 

oracle数据库在导入不同字符集的数据时经常会出现以下类似问题:

 

IMP-00017: following statement failed with ORACLE error 2299:

"ALTER TABLE "TMPUSERINFO" ADD UNIQUE ("MDN") USING INDEX PCTFREE 10 INITRA"

"NS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABL"

"ESPACE "JLTGAME" LOGGING ENABLE"

IMP-00003: ORACLE error 2299 encountered

ORA-02299: cannot validate (JLTGAME.SYS_C003199) - duplicate keys found

IMP-00017: following statement failed with ORACLE error 2299:

"ALTER TABLE "TMPUSERINFO" ADD UNIQUE ("USERNAME") USING INDEX PCTFREE 10 I"

"NITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1)"

" TABLESPACE "JLTGAME" LOGGING ENABLE"

IMP-00003: ORACLE error 2299 encountered

ORA-02299: cannot validate (JLTGAME.SYS_C003200) - duplicate keys found

 

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

 

 

 

 

解决办法:修改数据库字符集

 

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

重点：ALTER DATABASE character set INTERNAL_USE zhs16gbk;  # 使用INTERNAL_USE可以跳过超集的检查,

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

 

 

SQL> connect sys/test123@szdb as sysdba;

Connected to an idle instance.

SQL> startup 

ORACLE instance started.

 

Total System Global Area 235999352 bytes

Fixed Size 450680 bytes

Variable Size 201326592 bytes

Database Buffers 33554432 bytes

Redo Buffers 667648 bytes

Database mounted.

Database opened.

SQL> select name,value$ from props$ where name like '%NLS%';

 

NAME

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

VALUE$

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

NLS_LANGUAGE

AMERICAN

 

NLS_TERRITORY

AMERICA

 

NLS_CURRENCY

$

 

 

NAME

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

VALUE$

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

NLS_ISO_CURRENCY

AMERICA

 

NLS_NUMERIC_CHARACTERS

.,

 

NLS_CHARACTERSET

WE8ISO8859P1

 

 

NAME

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

VALUE$

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

NLS_CALENDAR

GREGORIAN

 

NLS_DATE_FORMAT

DD-MON-RR

 

NLS_DATE_LANGUAGE

AMERICAN

 

 

NAME

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

VALUE$

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

NLS_SORT

BINARY

 

NLS_TIME_FORMAT

HH.MI.SSXFF AM

 

NLS_TIMESTAMP_FORMAT

DD-MON-RR HH.MI.SSXFF AM

 

 

NAME

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

VALUE$

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

NLS_TIME_TZ_FORMAT

HH.MI.SSXFF AM TZR

 

NLS_TIMESTAMP_TZ_FORMAT

DD-MON-RR HH.MI.SSXFF AM TZR

 

NLS_DUAL_CURRENCY

$

 

 

NAME

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

VALUE$

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

NLS_COMP

BINARY

 

NLS_LENGTH_SEMANTICS

BYTE

 

NLS_NCHAR_CONV_EXCP

FALSE

 

 

NAME

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

VALUE$

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

NLS_NCHAR_CHARACTERSET

AL16UTF16

 

NLS_RDBMS_VERSION

9.2.0.1.0

 

 

20 rows selected.

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area 235999352 bytes

Fixed Size 450680 bytes

Variable Size 201326592 bytes

Database Buffers 33554432 bytes

Redo Buffers 667648 bytes

Database mounted.

SQL> alter session set sql_trace=true;

 

Session 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> set linesize 120;

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;  # 使用INTERNAL_USE可以跳过超集的检查,ALTER DATABASE character set INTERNAL_USE 

 

Database altered.

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP

ORACLE instance started.

 

Total System Global Area 235999352 bytes

Fixed Size 450680 bytes

Variable Size 201326592 bytes

Database Buffers 33554432 bytes

Redo Buffers 667648 bytes

Database mounted.

Database opened.

SQL> 

SQL> select name,value$ from props$ where name like '%NLS%';

 

NAME

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

VALUE$

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

NLS_LANGUAGE

AMERICAN

 

NLS_TERRITORY

AMERICA

 

NLS_CURRENCY

$

 

 

NAME

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

VALUE$

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

NLS_ISO_CURRENCY

AMERICA

 

NLS_NUMERIC_CHARACTERS

.,

 

NLS_CHARACTERSET

ZHS16GBK

 

 

NAME

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

VALUE$

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

NLS_CALENDAR

GREGORIAN

 

NLS_DATE_FORMAT

DD-MON-RR

 

NLS_DATE_LANGUAGE

AMERICAN

 

 

NAME

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

VALUE$

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

NLS_SORT

BINARY

 

NLS_TIME_FORMAT

HH.MI.SSXFF AM

 

NLS_TIMESTAMP_FORMAT

DD-MON-RR HH.MI.SSXFF AM

 

 

NAME

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

VALUE$

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

NLS_TIME_TZ_FORMAT

HH.MI.SSXFF AM TZR

 

NLS_TIMESTAMP_TZ_FORMAT

DD-MON-RR HH.MI.SSXFF AM TZR

 

NLS_DUAL_CURRENCY

$

 

 

NAME

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

VALUE$

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

NLS_COMP

BINARY

 

NLS_LENGTH_SEMANTICS

BYTE

 

NLS_NCHAR_CONV_EXCP

FALSE

 

 

NAME

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

VALUE$

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

NLS_NCHAR_CHARACTERSET

AL16UTF16

 

NLS_RDBMS_VERSION

9.2.0.1.0

 

 

20 rows selected.

 

 

SQL> 

 

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