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

由于在安装oracle9i时不慎选错了字符集，然后手动update props$表修改了字符集（手动修改props$是oracle7的招数，

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

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

[oracle@TestAs4 ~]$ exp system/XXXXXX wner=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
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 bytes
Fixed Size                   451704 bytes
Variable Size             218103808 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database 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 CHARSET
SQL>SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
ZHS16CGB231280

SQL>COL VALUE NEW_VALUE NCHARSET
SQL>SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL16UTF16

SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;
old   1: ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET
new   1: ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16CGB231280

Database altered.

SQL>ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;
old   1: ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET
new   1: ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16

Database altered.

SQL>SHUTDOWN IMMEDIATE;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>STARTUP;
ORACLE instance started.

Total System Global Area  252777592 bytes
Fixed Size                   451704 bytes
Variable Size             218103808 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL>SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>STARTUP;
ORACLE instance started.

Total System Global Area  252777592 bytes
Fixed Size                   451704 bytes
Variable Size             218103808 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL>select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16CGB231280

SQL> 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                                NCHAR
AL16UTF16                                NCLOB
AL16UTF16                                NVARCHAR2
ZHS16CGB231280                           CHAR
ZHS16CGB231280                           CLOB
ZHS16CGB231280                           VARCHAR2

6 rows selected.

SQL>exit

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

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

Export: Release 9.2.0.4.0 - Production on Thu Dec 27 21:29:03 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
. exporting private type synonyms
. exporting object type definitions for user CWM
About 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 statistics
Export terminated successfully without warnings.