| 本月文章推荐 |
修改oracle9i数据库字符集的方法发表日期：2008-2-9
|
|
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 setSQL> 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> 参考: http://www.oracle.com.cn/viewthread.PHP?tid=52057&highlight=new%2B%2B%2Bcharacter%2B%2B%2Bset |
| 上一篇：SQL*Report 1.1 Error Messages And Codes
人气:748 下一篇：转一个很好的Oracle贴子 人气:654 |
| 浏览全部Oracle教程的内容
Dreamweaver插件下载 网页广告代码 祝你圣诞节快乐 2009年新年快乐 |
