imp导入时出现imp-00017 ora-06550的解决办法

时间：2022-08-03 07:46:23

imp导入时出现imp-00017 ora-06550的解决办法


将exp逻辑备份文件导入到备库中时出现了错误，相关错误信息如下：

  1. IMP-00017: 由于 Oracle 错误 6550, 以下语句失败:  
  2.  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '00730068006F0075006"  
  3.  "C0075'; SREC.MAXVAL := '9F9A00200020514B'; SREC.EAVS := 4; SREC.CHVALS := D"  
  4.  "BMS_STATS.CHARARRAY(utl_raw.cast_to_varchar2('00730068006F0075006C0075'),'N"  
  5.  "fS[',utl_raw.cast_to_varchar2('4E5400200020664B'),utl_raw.cast_to_varchar2"  
  6.  "('4ED8002000204FCA'),'N貍*',utl_raw.cast_to_varchar2('4EFB002000205170'),'N"  
  7.  "鸒','O  
  8. ……  
  9. IMP-00003: 遇到 ORACLE 错误 6550  
  10. ORA-06550: 第 1 行, 第 4836 列:   
  11. PLS-00103: 出现符号 "朒ag?"在需要下列之一时：  
  12.  ( - + case mod new not  
  13.    null others <an identifier>  
  14.    <a double-quoted delimited-identifier> <a bind variable> avg  
  15.    count current exists max min prior sql stddev sum variance  
  16.    execute forall merge time timestamp interval date  
  17.    <a string literal with character set specification>  
  18.    <a number> <a single-quoted SQL string> pipe  
  19.    <一个带有字符集说明的可带引号的字符串文字>  
  20.    <一个可带引号的 SQL 字符串>  

    这是个很常见的导入警告，通常与数据库/客户端版本有关。比如这里，源数据库和目标数据库版本都是10.2.0.4，但导出的时候使用了10.2.0.1的客户端：

  1. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production  
  2. With the Partitioning, Real Application Clusters, OLAP, Data Mining  
  3. and Real Application Testing options  
  4.   
  5.   
  6. 经由常规路径由 EXPORT:V10.02.01 创建的导出文件  
    在别的项目中，还遇到过从10.2.0.2数据库中导入到10.2.0.4目标库时出现了同样的问题，不管imp/exp使用了什么版本。


    解决办法，就是在exp或imp时加上一个参数来禁用统计信息的导出/导入：  statistics=none    

    那么，不导入统计信息会有什么影响呢。其实在oracle 10g以后，默认定时采集统计信息，可以在dba_scheduler_jobs视图中查询到该任务：GATHER_STATS_JOB。并且可以在dba_scheduler_job_run_details中看到相应的执行情况。

    手动采集统计信息：在sys用户中执行dbms_scheduler.run_job('GATHER_STATS_JOB') 存储过程（等价于执行dbms_stats.gather_database_stats_job_proc）；使用dbms_stats包中的gather_xxx_stats来采集。停用定时采集统计信息的任务：DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB')

    oracle 10g中修改自动采集统计信息的时间策略：通过sys用户登录执行存储过程dbms_scheduler.set_attribute来修改，例如

dbms_scheduler.set_attribute('GATHER_STATS_JOB','SCHEDULE_NAME','MAINTENANCE_WINDOW_GROUP') 

dbms_scheduler.set_attribute('WEEKEND_WINDOW','DURATION','+000 05:00:00')

查询：

  1. select d.window_name,d.repeat_interval,d.duration  
  2. from DBA_SCHEDULER_JOBS a,DBA_SCHEDULER_WINDOW_GROUPS b,DBA_SCHEDULER_WINGROUP_MEMBERS c,DBA_SCHEDULER_WINDOWS d  
  3. where a.job_name='GATHER_STATS_JOB'  
  4.   and a.schedule_name=b.window_group_name  
  5.   and b.window_group_name=c.window_group_name  
  6.   and c.window_name=d.window_name  
  7. union all  
  8. select d.window_name,d.repeat_interval,d.duration  
  9. from DBA_SCHEDULER_JOBS a,DBA_SCHEDULER_WINDOWS d  
  10. where a.job_name='GATHER_STATS_JOB'  
  11.   and a.schedule_name=d.window_name; 
====================================================================================================================================

IMP-00017: 6550

查了一下METALINK，上面说：
Importing to a database with a different charset, IMP-00017, ora- 6550,   PLS-00103   can be encountered if a procedure or a function contains a newline character within quotes.

Rediscovery Information:
If a procedure or a function contains a newline character within quotes and when importing to a database with a different charset,   if IMP-00017, oracle error 6550, PLS-00103 is encountered, you might be hitting this bug.

看起来像是一个BUG（METALINK上的编号是Bug 3831513 )，
Versions confirmed as being affected：
10.1.0.3
8.1.7.4
9.2.0.5

This issue is fixed in：
9.2.0.7 (Server Patch Set)
10.1.0.4 (Server Patch Set)
10g Release 2 (future version)

你的情况是怎样的？EXP和IMP的字符集是一样的么？

I tried to import using 10.2.0 to 9i database. This shows the error like

IMP-00058: ORACLE error 6550 encountered
ORA-06550: line 1, column 33:
PLS-00302: component 'SET_NO_OUTLINES' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
IMP-00000: Import terminated unsuccessfully.

Solution i found:

This error was encountered when exporting from a 9i database using a 10g client, and then importing into a 9i databse using the 10g client. I had to export and import using the 9i client in the end.

If you are using Windows, type PATH at the command prompt and see what the values are set to. There will most likely be multiple Oracle paths in there, for example D:\Oracle\Ora92 and D:\Oracle\product\10.2.0\db_1. The one which appears first will take priority over the others.

You can modify which appears first on your command prompt session by copying out the path to a text file, re-arranging it so that either the 9i or 10g one appears first and then copy it back into your command prompt window, with a PATH=D:\Oracle\Ora92.....etc, etc.

You can also modify this parameter for the environment by going to START -> right click My Computer -> Advanced -> Environment Variables -> In the System Variables box there is a PATH variable. Change the order of that.

If you are still unsure, you can also specify the full path whenever you invoke SQLPlus or IMP/EXP, etc. For example, my PATH variable has has D:\Oracle\Ora92 at the beginning of it but I want to use the 10g Export command. I would type D:\Oracle\Product\10.2.0\db_1\BIN\EXP to invoke the export command.


参考：

imp ora 6550 statistic=none     百度

imp ora6550 pls-203           百度

标签：

相关文章