oracle 报6553,数据导出报ORA-06553,PLS-00561解决一例

数据导出报ORA-06553,PLS-00561解决一例

【环境】

OS:win2003sp1

DB:oracle817

【问题现象】

数据库EXP导出时报错，具体如下：

Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 -

Production

With the Partitioning option

JServer Release 8.1.7.0.0 - Production

Export done in US7ASCII character set and ZHS16GBK NCHAR character

set

About to export specified users ...

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user DBAUSER

. exporting object type definitions for user DBAUSER

About to export DBAUSER's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export DBAUSER's tables via Conventional Path ...

. . exporting

table AAA_AFFICH 1 rows exported

. . . . . .

. . exporting

table AAA_USER 1 rows exported

. exporting synonyms

EXP-00008: ORACLE error 6553 encountered

ORA-06553: PLS-561: character set mismatch on value for parameter

'SHORTNAME'

EXP-00000: Export terminated unsuccessfully

【问题分析】

查询oracle报错手册：

* ORA-06553 PLS-string: string

* PLS-00561 character set mismatch on value for

parameter 'string'

Cause: An expression was used

that contains an incorrect character set. The actual argument has a

character set conflict. If a default argument value is being used,

it might be in conflict with some actual argument that must have

the same character set.

Action: Adjust the expression,

using TRANSLATE(... USING ...) or change the character set.

分析后可能结果：

1. 估计和Java Option有点关系，以下是metalink给出的解释：

Problem Description ------------------- Trying to get full or user export it fails with the following

errors: ORA-6553 PLS-561 character set mismatch on value for

parameter ''SHORTNAME'' Check

if Java option is installed: SELECT count(*) FROM dba_objects WHERE

object_type LIKE ''JAVA%''; -> this select should

return more than 4000 rows Also issue as user

SYS: SELECT dbms_java.longname(name) FROM obj$ WHERE type# = 29 and

status != 1; -> this normally should return 0 rows,

otherwise will fail with error ORA-6553

PLS-561. The problem seems to

be that Java Option was not installed properly. Even if removing

Java Option by using script $ORACLE_HOME/javavm/install/rmjvm.sql Problem will persist.

Solution Description

-------------------- If you do not need Java Option, first remove it by script:

$ORACLE_HOME/javavm/install/rmjvm.sql (Notice that in 8.1.6

rmjvm.sql is wrong. Before running it you should change line: "call

rmjvm.run(true)" to "execute rmjvm.run(true)" This is due to

[BUG:1179670]. Be careful for having large rollback segments when

running it. Then login from sqlplus as SYS user

and issue the following commands: SQL> drop package

dbms_java; SQL> drop public synonym

dbms_java; If you need the Java Option check

[NOTE:105472.1] for correct Java

installation.

Explanation ----------- Export tests whether the Synonym ''DBMS_JAVA'' is defined. If it is

defined, export assumes java has been installed and it uses

dbms_java.longname to get the long version of synonym

names. Otherwise, it assumes it can''t use

dbms_java and just takes the name out of sys.syn$. It looks like

DBMS_JAVA was defined but initjvm didn''t run (or

failed somewhere).

2.与字符集CHARACTER SET有关，

数据库安装完成后，导入初始数据前，手工执行

update props$ set value$='US7ASCII' where

name='NLS_CHARACTERSET'

and

name='NLS_NCHAR_CHARACTERSET' 修改字符集.

【解决步骤】

1.Remove Java Option by run script rmjvm.sql.

SQL> @

D:\oracle\ora81\javavm\install\rmjvm.sql;

.....

2.将props$中NLS_NCHAR_CHARACTERSET的值改回ZHS16GBK。

SQL>update props$ set value$='ZHS16GBK' where

name='NLS_NCHAR_CHARACTERSET';

SQL>COMMIT;

SQL> Select * From props$ Where Name Like

'%_CHARACTERSET%';

NAME VALUE$ COMMENT$

-------------------------------------------------------------------------------

NLS_CHARACTERSET US7ASCII Character set

NLS_NCHAR_CHARACTERSET ZHS16GBK NCHAR Character set

3.重启数据库，执行EXP导出.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 -

Production

With the Partitioning option

JServer Release 8.1.7.0.0 - Production

Export done in US7ASCII character set and ZHS16GBK NCHAR character

set

About to export specified users ...

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user DBAUSER

. exporting object type definitions for user DBAUSER

About to export DBAUSER's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export DBAUSER's tables via Conventional Path ...

. . exporting

table AAA_AFFICH 1 rows exported

. . . . . .

. . exporting

table AAA_USER 1 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 snapshots

. 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.