EXP错误逐个击溃

on

$ exp username/password file=expdat.dmp tables=tablename

这是一条经历了磨难的语句,然而最终也还是修成正果 🙂

最开始报错:

EXP-00008: ORACLE error 904 encountered
ORA-00904: “DBMS_JAVA”.”LONGNAME”: invalid identifier
Export terminated successfully with warnings.

重新运行了$ORACLE_HOME/rdbms/admin/catexp.sql,无效,错误依旧。

然后运行$ORACLE_HOME/javavm/install/initdbj.sql,重新创建dbms_java包,这个错误消失了。

再次运行exp,报错:

Export done in US7ASCII character set and UTF8 NCHAR character set
server uses UTF8 character set (possible charset conversion)

EXP-00008: ORACLE error 6552 encountered
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-553: character set name is not recognized
Export terminated successfully with warnings.

检查系统环境,发现在props$中字符集是UTF8,但是操作系统环境变量没有设置NLS_LANG,于是:
NLS_LANG=AMERICAN_AMERICA.UTF8;export NLS_LANG

再次运行exp,仍然报错:

Export done in UTF8 character set and UTF8 NCHAR character set

EXP-00008: ORACLE error 6552 encountered
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-553: character set name is not recognized
Export terminated successfully with warnings.

于是询问客户是否有做过字符集的修改,果然,在很久很久以前,他们手动update props$表修改了字符集(手动修改props$是oracle7的招数,在oracle8和oracle9中虽然也能修改,但是会留下修改不干净的隐患)

首先,确认字符集是否修改的不彻底。

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
UTF8 CHAR
UTF8 VARCHAR2
WE8ISO8859P1 CHAR
WE8ISO8859P1 CLOB
WE8ISO8859P1 VARCHAR2

8 rows selected.

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

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;

COL VALUE NEW_VALUE CHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER=’NLS_CHARACTERSET’;
COL VALUE NEW_VALUE NCHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER=’NLS_NCHAR_CHARACTERSET’;

–INTERNAL_USE是没有写在文档中的参数,用以强制完成字符集一致化
ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;

SHUTDOWN IMMEDIATE;
STARTUP;
— 再次启动数据库一遍
SHUTDOWN IMMEDIATE;
STARTUP;

按照上面的步骤,一步一步来,一步不要少,完成以后,重新检索col$表。

CHARACTERSET TYPES_USED_IN
——————– ————-
UTF8 CHAR
UTF8 CLOB
UTF8 NCHAR
UTF8 NCLOB
UTF8 NVARCHAR2
UTF8 VARCHAR2

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

Export terminated successfully without warnings.

19 Comments Add yours

  1. Thomas Zhang says:

    写的很好!
    特别是INTERNAL_USE ,以前就没见到过!^|^

  2. qsxing says:

    我查看了一下我的库,好像也存在多个字符集
    CHARACTERSET TYPES_USED_IN
    —————————————- ————-
    US7ASCII CHAR
    US7ASCII CLOB
    US7ASCII NCHAR
    US7ASCII NCLOB
    US7ASCII NVARCHAR2
    US7ASCII VARCHAR2
    ZHS16GBK CHAR
    ZHS16GBK CLOB
    ZHS16GBK VARCHAR2

    9 rows selected.
    但是导出来的话,好像没有问题啊,请教为什么啊?

  3. kamus says:

    to qsxing
    因为ZHS16GBK是US7ASCII的superset,US7ASCII有的内部编码ZHS16GBK全都有而且都一样,所以你的exp不会出现报错。

  4. Ken says:

    你好,我们刚做完utf8的字符集转换,我想问一下,你上面提供的脚本能否用来检验字符集转换的结果?
    谢谢!

  5. kamus says:

    @Ken
    可以的

  6. Ken says:

    1 AL16UTF16 NCHAR
    2 AL16UTF16 NCLOB
    3 AL16UTF16 NVARCHAR2
    4 UTF8 CHAR
    5 UTF8 CLOB
    6 UTF8 VARCHAR2

    我们的字符集转换后(16gbk–〉utf8),
    帮我看看是不是有问题?

  7. kamus says:

    @ken
    没问题,你的系统现在national字符集是AL16UTF16,本地字符集是UTF8,已经没有zhs16gbk什么事儿了。

  8. kanthony says:

    hi there,

    please check for me whether it is ok to export….

    CHARACTERSET TYPES_USED_IN
    —————————————- ————-
    AL16UTF16 NCHAR
    AL16UTF16 NCLOB
    AL16UTF16 NVARCHAR2
    ZHT16MSWIN950 CHAR
    ZHT16MSWIN950 CLOB
    ZHT16MSWIN950 VARCHAR2

    6 rows selected.

    thanks.

    Kanthony

  9. szr says:

    我编译一个程序包时,报下面这个错,也能用以上方法解决吗?
    错误:
    93/7 PL/SQL: SQL Statement ignored

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

    ORA-06553: PLS-201: identifier ‘UNDEFINED’ must be declared

  10. kamus says:

    @kanthony
    你这个应该是没问题的

    @szr
    你这个问题似乎跟字符集无关。UNDEFINED是你自己命名的什么?

  11. linuxcrazy says:

    帮忙看看我的,我的也导出出问题:
    Export done in UTF8 character set and AL16UTF16 NCHAR character set
    server uses AL32UTF8 character set (possible charset conversion)

    TYPES_USED_IN CHARACTERSET
    —————————————
    AL32UTF8 VARCHAR2
    AL16UTF16 NCLOB
    AL16UTF16 NVARCHAR2
    AL32UTF8 CHAR
    AL32UTF8 CLOB
    AL16UTF16 NCHAR

  12. kamus says:

    @linuxcrazy
    你的导出报什么错误,看你这个输入按理说应该没有什么问题,你导出时候的客户端字符集设置是UTF8?你设置成AL32UTF8试试。

  13. linuxcrazy says:

    NLS_LANG = AMERICAN_AMERICA.UTF8

  14. linuxcrazy says:

    导出时的输出:
    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Produc
    tion
    With the Partitioning, OLAP and Data Mining options
    Enter array fetch buffer size: 4096 >

    Export file: EXPDAT.DMP > d:\a.dmp

    (1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > U

    Export grants (yes/no): yes >

    Export table data (yes/no): yes >

    Compress extents (yes/no): yes >

    Export done in UTF8 character set and AL16UTF16 NCHAR character set
    server uses AL32UTF8 character set (possible charset conversion)

    About to export specified users …
    User to be exported: (RETURN to quit) >

  15. kamus says:

    没有贴全?没看到导出的报错。
    另外Compress extents设置成no

  16. david says:

    太好了,刚刚解决我的一个问题。

    别人装的数据,字符集缺省是 WE8ISO8859P1 ,没有改成 ZHS16GBK.
    当时赶时间,没有重建数据库,直接修改prop$表,将字符集改为ZHS16GBK。
    然后建表、insert数据,一切正常。。。直到今天要exp出数据。

    报错:
    即将导出指定的用户…
    . 正在导出 pre-schema 过程对象和操作
    . 正在导出用户 PLATFORM 的外部函数库名称
    . 导出 PUBLIC 类型同义词
    EXP-00008: 遇到 ORACLE 错误 6552
    ORA-06552: PL/SQL: Compilation unit analysis terminated
    ORA-06553: PLS-553: character set name is not recognized
    EXP-00000: 导出终止失败

    按照楼主的步骤,解决了问题。
    太感谢了!

Leave a Reply to linuxcrazy Cancel reply

Your email address will not be published. Required fields are marked *