oracle导出字符编码修改,更改oracle字符集导致exp命令出错(解决方法)

由于在安装oracle9i时不慎选错了字符集，然后手动update

props$表修改了字符集(手动修改props$是oracle7的招数，

在oracle8和oracle9中虽然也能修改，但是会留下修改不干净的隐患)

首先，确认字符集是否修改的不彻底。用exp导出会出现错误如：

[oracle@TestAs4 ~]$  exp system/XXXXXX owner=cwm file=1227.dmp

Export: Release 9.2.0.4.0 - Production on Thu Dec 27 21:25:25 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses ZHS16CGB231280 character set (possible charset conversion)

About to export specified users ...

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user CWM

. exporting PUBLIC type synonyms

EXP-00008: ORACLE error 6552 encountered

ORA-06552: PL/SQL: Compilation unit analysis terminated

ORA-06553: PLS-553: character set name is not recognized

EXP-00000: Export terminated unsuccessfully

用这个语句查看字集

SELECT DISTINCT (NLS_CHARSET_NAME(CHARSETID)) CHARACTERSET,DECODE(TYPE#,1,DECODE(CHARSETFORM,1,'VARCHAR2',2,'NVARCHAR2','UNKOWN')

,9,DECODE(CHARSETFORM,1,'VARCHAR',2,'NCHARVARYING','UNKOWN'),96,DECODE(CHARSETFORM,

1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),112,DECODE(CHARSETFORM, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN FROM SYS.COL$ WHERE CHARSETFORM IN (1, 2) AND TYPE# IN

(1, 9, 96, 112);

CHARACTERSET                             TYPES_USED_IN

---------------------------------------- -------------

AL16UTF16                                NCHAR

AL16UTF16                                NCLOB

AL16UTF16                                NVARCHAR2

ZHS16GBK                                 CHAR

ZHS16GBK                                 CLOB

ZHS16GBK                                 VARCHAR2

6 rows selected.

但当前字符集却为：ZHS16CGB231280

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')

----------------------------------------------------

AMERICAN_AMERICA.ZHS16CGB231280

确实在数据库的列属性中仍然存在着多个字符集的设定，这是导致exp失败的原因。下面解决这个问题。

解决方法：

SQL> SHUTDOWN IMMEDIATE;Database closed.Database dismounted.ORACLE instance shut down.SQL> STARTUP MOUNT;ORACLE instance started.Total System Global Area  252777592 bytesFixed Size                   451704 bytesVariable Size             218103808 bytesDatabase Buffers           33554432 bytesRedo Buffers                 667648 bytesDatabase mounted.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> COL VALUE NEW_VALUE CHARSETSQL> SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';VALUE--------------------------------------------------------------------------------ZHS16CGB231280SQL> COL VALUE NEW_VALUE NCHARSETSQL> SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';VALUE--------------------------------------------------------------------------------AL16UTF16SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;old   1: ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSETnew   1: ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16CGB231280Database altered.SQL>ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;old   1: ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSETnew   1: ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16Database altered.SQL> SHUTDOWN IMMEDIATE;Database closed.Database dismounted.ORACLE instance shut down.SQL> STARTUP;ORACLE instance started.Total System Global Area  252777592 bytesFixed Size                   451704 bytesVariable Size             218103808 bytesDatabase Buffers           33554432 bytesRedo Buffers                 667648 bytesDatabase mounted.Database opened.SQL> SHUTDOWN IMMEDIATE;Database closed.Database dismounted.ORACLE instance shut down.SQL> STARTUP;ORACLE instance started.Total System Global Area  252777592 bytesFixed Size                   451704 bytesVariable Size             218103808 bytesDatabase Buffers           33554432 bytesRedo Buffers                 667648 bytesDatabase mounted.Database opened.SQL> select userenv('language') from dual;USERENV('LANGUAGE')----------------------------------------------------AMERICAN_AMERICA.ZHS16CGB231280SQL>  SELECT DISTINCT (NLS_CHARSET_NAME(CHARSETID)) CHARACTERSET,DECODE(TYPE#,1,DECODE(CHARSETFORM,1,'VARCHAR2',2,'NVARCHAR2'

,'UNKOWN'),9,DECODE(CHARSETFORM,1,'VARCHAR',2,'NCHAR

VARYING','UNKOWN'),96,DECODE(CHARSETFORM, 1, 'CHAR', 2, 'NCHAR',

'UNKOWN'),112,DECODE(CHARSETFORM, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN'))

TYPES_USED_IN FROM SYS.COL$ WHERE CHARSETFORM IN (1, 2) AND TYPE# IN

(1, 9, 96, 112);CHARACTERSET                             TYPES_USED_IN---------------------------------------- -------------AL16UTF16                                NCHARAL16UTF16                                NCLOBAL16UTF16                                NVARCHAR2ZHS16CGB231280                           CHARZHS16CGB231280                           CLOBZHS16CGB231280                           VARCHAR26 rows selected.SQL> exit

干净了，再次运行exp，大功告成。

[oracle@TestAs4 ~]$  exp system/XXXXXX owner=cwm  file=1227.dmpExport: Release 9.2.0.4.0 - Production on Thu Dec 27 21:29:03 2007Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionExport done in US7ASCII character set and AL16UTF16 NCHAR character setserver uses ZHS16CGB231280 character set (possible charset conversion)About to export specified users .... exporting pre-schema procedural objects and actions. exporting foreign function library names for user CWM. exporting PUBLIC type synonyms. exporting private type synonyms. exporting object type definitions for user CWMAbout to export CWM's objects .... exporting database links. exporting sequence numbers. exporting cluster definitions. about to export CWM's tables via Conventional Path .... . exporting table                            CWM          0 rows exported. . exporting table                 PARTITION_TEST. . exporting partition                           ID01          0 rows exported. . exporting partition                           ID02          0 rows exported. . exporting partition                           ID03          0 rows exported. exporting synonyms. exporting views. exporting stored procedures. exporting operators. exporting referential integrity constraints. exporting triggers. exporting indextypes. exporting bitmap, functional and extensible indexes. exporting posttables actions. exporting materialized views. exporting snapshot logs. exporting job queues. exporting refresh groups and children. exporting dimensions. exporting post-schema procedural objects and actions. exporting statisticsExport terminated successfully without warnings.