修改oracle9i資料庫字符集的方法(轉)
修改oracle9i資料庫字符集的方法(轉)[@more@]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
解決辦法:修改資料庫字符集
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>
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
解決辦法:修改資料庫字符集
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>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10617542/viewspace-961495/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 修改資料庫字符集(轉）資料庫
- 修改資料庫字符集資料庫
- 修改Oracle資料庫字符集Oracle資料庫
- 修改Oracle資料庫字符集(zt)Oracle資料庫
- 怎樣修改資料庫字符集資料庫
- 「Oracle」資料庫字符集編碼修改Oracle資料庫
- 在不同字符集的資料庫之間匯入資料的方法(轉)資料庫
- oracle資料庫字符集的轉換Oracle資料庫
- 資料庫字符集的選擇（轉）資料庫
- ORA-12899 修改資料庫字符集資料庫
- Oracle修改資料字符集Oracle
- 不同字符集倒庫的方法 (轉)
- MySQL修改字符集(mysqldump轉換全庫)MySql
- 如何在10g中修改資料庫字符集資料庫
- MySQL資料庫中修改密碼方法總結(轉)MySql資料庫密碼
- 修改資料庫db_name的方法資料庫
- ORACLE 修改資料庫的字符集編碼為UTF-8Oracle資料庫
- 巧妙轉換ORACLE資料庫字符集Oracle資料庫
- MySQL表、欄位、庫的字符集修改方法介紹MySql
- linux下mysql的預設字符集修改和預設資料庫引擎的修改LinuxMySql資料庫
- 轉載如何修改oracle資料庫字符集_及如何繞過超集報錯Oracle資料庫
- psycopg2 修改資料庫客戶端字符集資料庫客戶端
- 手動建立 Oracle9i 資料庫(轉載)Oracle資料庫
- Oracle資料庫字符集問題解析（轉）Oracle資料庫
- oracle9i 更改字符集步驟方法Oracle
- 資料庫字符集修改函式function nls_charset_id_name資料庫函式Function
- MySQL DB字符集修改方法MySql
- 修改mysql預設字符集的方法MySql
- 使用Oracle9i資料庫的九大注意事項(轉)Oracle資料庫
- (轉載)Oracle資料庫字符集問題解析Oracle資料庫
- mysql資料庫修改新增Date格式列的方法MySql資料庫
- PostgreSQL資料庫修改sql表的方法彙總SQL資料庫
- oracle資料庫的字符集更改Oracle資料庫
- 【轉】修改表的欄位資料型別的方法資料型別
- 在單機上建立物理的Oracle9i standby資料庫(轉)Oracle資料庫
- 資料庫修改資料資料庫
- Oracle9i 資料庫管理實務講座(一) (轉)Oracle資料庫
- MySQL檢視和修改字符集的方法MySql