分类： LINUX
2007-12-27 21:47:36
由于在安装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
确实在数据库的列属性中仍然存在着多个字符集的设定，这是导致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 owner=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.
