exp导出oracle数据库因为字符设置报错，解决方法。

 由于在安装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
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

   确实在数据库的列属性中仍然存在着多个字符集的设定，这是导致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.

最后

以上就是俊逸凉面为你收集整理的exp导出oracle数据库因为字符设置报错，解决方法。的全部内容，希望文章能够帮你解决exp导出oracle数据库因为字符设置报错，解决方法。所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。