熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

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

2013-11-13 22:21:02  來源: Oracle 

  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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.