ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 更改oracle字符集导致exp命令出错(解决方法)

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

原创 Linux操作系统 作者:womenshitouzhu 时间:2009-09-28 16:05:45 0 删除 编辑
更改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
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 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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9653957/viewspace-615807/,如需转载,请注明出处,否则将追究法律责任。

下一篇: temp表空间管理
请登录后发表评论 登录
全部评论

注册时间:2009-02-11

  • 博文量
    22
  • 访问量
    53965