修改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