修改oracle9i數據庫字符集的方法

　　SQL> select namevalue$ from props$ where name like %NLS%;

　　SQL> alter database character set zhsgbk;
alter database character set zhsgbk
*
ERROR at line :
ORA: new character set must be a superset of old character set

　　SQL> ALTER DATABASE character set INTERNAL_USE zhsgbk;

　　SQL> select value from nls_database_parameters where parameter=NLS_CHARACTERSET;

　　oracle數據庫在導入不同字符集的數據時經常會出現以下類似問題:

　　IMP: following statement failed with ORACLE error :
ALTER TABLE TMPUSERINFO ADD UNIQUE (MDN) USING INDEX PCTFREE INITRA
NS MAXTRANS STORAGE(INITIAL FREELISTS FREELIST GROUPS ) TABL
ESPACE JLTGAME LOGGING ENABLE
IMP: ORACLE error encountered
ORA: cannot validate (JLTGAMESYS_C) duplicate keys found
IMP: following statement failed with ORACLE error :
ALTER TABLE TMPUSERINFO ADD UNIQUE (USERNAME) USING INDEX PCTFREE I
NITRANS MAXTRANS STORAGE(INITIAL FREELISTS FREELIST GROUPS )
TABLESPACE JLTGAME LOGGING ENABLE
IMP: ORACLE error encountered
ORA: cannot validate (JLTGAMESYS_C) duplicate keys found

　　解決辦法:修改數據庫字符集

　　SQL> connect sys/test@szdb as sysdba;
Connected to an idle instance
SQL> startup
ORACLE instance started

　　Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
Database mounted
Database opened
SQL> select namevalue$ 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
WEISOP

　　NAME

VALUE$

NLS_CALENDAR
GREGORIAN

　　NLS_DATE_FORMAT
DDMONRR

　　NLS_DATE_LANGUAGE
AMERICAN

　　NAME

VALUE$

NLS_SORT
BINARY

　　NLS_TIME_FORMAT
HHMISSXFF AM

　　NLS_TIMESTAMP_FORMAT
DDMONRR HHMISSXFF AM

　　NAME

VALUE$

NLS_TIME_TZ_FORMAT
HHMISSXFF AM TZR

　　NLS_TIMESTAMP_TZ_FORMAT
DDMONRR HHMISSXFF AM TZR

　　NLS_DUAL_CURRENCY
$

　　NAME

VALUE$

NLS_COMP
BINARY

　　NLS_LENGTH_SEMANTICS
BYTE

　　NLS_NCHAR_CONV_EXCP
FALSE

　　NAME

VALUE$

NLS_NCHAR_CHARACTERSET
ALUTF

　　NLS_RDBMS_VERSION

　　 rows selected

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

　　Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers 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=;

　　System altered

　　SQL> alter system set aq_tm_processes=;

　　System altered

　　SQL> alter database open;

　　Database altered

　　SQL> set linesize ;
SQL> alter database character set zhsgbk;
alter database character set zhsgbk
*
ERROR at line :
ORA: new character set must be a superset of old character set

　　SQL> ALTER DATABASE character set INTERNAL_USE zhsgbk; # 使用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 bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
Database mounted
Database opened
SQL>
SQL> select namevalue$ 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
ZHSGBK

　　NAME

VALUE$

NLS_CALENDAR
GREGORIAN

　　NLS_DATE_FORMAT
DDMONRR

　　NLS_DATE_LANGUAGE
AMERICAN

　　NAME

VALUE$

NLS_SORT
BINARY

　　NLS_TIME_FORMAT
HHMISSXFF AM

　　NLS_TIMESTAMP_FORMAT
DDMONRR HHMISSXFF AM

　　NAME

VALUE$

NLS_TIME_TZ_FORMAT
HHMISSXFF AM TZR

　　NLS_TIMESTAMP_TZ_FORMAT
DDMONRR HHMISSXFF AM TZR

　　NLS_DUAL_CURRENCY
$

　　NAME

VALUE$

NLS_COMP
BINARY

　　NLS_LENGTH_SEMANTICS
BYTE

　　NLS_NCHAR_CONV_EXCP
FALSE

　　NAME

VALUE$

NLS_NCHAR_CHARACTERSET
ALUTF

　　NLS_RDBMS_VERSION

　　 rows selected

　　SQL>

　　參考: %B%B%Bcharacter%B%B%Bset

From:http://tw.wingwit.com/Article/program/Oracle/201311/18844.html