IMP导入数据出现imp-00017 ora-06550错误的解决办法

今天在测试oracle数据导入导出的时候出现以下错误 IMP-00017: 由于 ORACLE 错误 6550, 以下语句失败:

IMP-00017: 由于 ORACLE 错误 6550, 以下语句失败:
 "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '0031003300300030003"
 "0006D0041'; SREC.MAXVAL := '5C0F53CC6A21'; SREC.EAVS := 4; SREC.CHVALS := D"
 "BMS_STATS.CHARARRAY(utl_raw.cast_to_varchar2('00310033003000300030006D0041'"
 "),utl_raw.cast_to_varchar2('0032003600300030006D0041'),utl_raw.cast_to_varc"
 "har2('0035003200300030006D0041'),utl_raw.cast_to_varchar2('0037003800300030"
 "006D0041'),utl_raw.cast_to_varchar2('004F00540054002B004100420053002D0053')"
 ",utl_raw.cast_to_varchar2('004F00540054002B004100420053002D005300285B895353"
 "0029'),utl_raw.cast_to_varchar2('004F00540054002B00440054004D0042'),utl_raw"
 ".cast_to_varchar2('004F00540054002B00440054004D004200285B8953530029'),utl_r"
 "aw.cast_to_varchar2('004F00540054002B004400560042002D0043'),utl_raw.cast_to"
 "_varchar2('004F00540054002B004400560042002D004300285B8953530029'),utl_raw.c"
 "ast_to_varchar2('004F00540054002B004400560042002D0053'),utl_raw.cast_to_var"
 "char2('004F00540054002B00440056004200530032'),utl_raw.cast_to_varchar2('004"
 "F0054005457FA78407248'),utl_raw.cast_to_varchar2('004F005400545B8953537248'"
 "),'QvN?,'Y''SUj!','Y''S蘪!','\SUj!','\S蘪!'); SREC.NOVALS := DBMS_STATS."
 "NUMARRAY(993853854541110000000000000000000,10141371925997900000000000000000"
 "00,1074983183470710000000000000000000,1115549859588070000000000000000000,16"
 "02336355825990000000000000000000,1602336355825990000000000000000000,1602336"
 "355825990000000000000000000,1602336355825990000000000000000000,160233635582"
 "5990000000000000000000,1602336355825990000000000000000000,16023363558259900"
 "00000000000000000,1602336355825990000000000000000000,1602336355827610000000"
 "000000000000,1602336355827670000000000000000000,422975615901020000000000000"
 "000000000,462912036756159000000000000000000000,4629120735848750000000000000"
 "00000000,478002149501275000000000000000000000,47800218632999200000000000000"
 "0000000); SREC.BKVALS := DBMS_STATS.NUMARRAY(271,580,916,1188,1485,1784,207"
 "0,2353,2649,2940,3253,3546,3822,4108,4403,4689,4962,5252,5561); SREC.EPC :="
 " 19; DBMS_STATS.SET_COLUMN_STATS(NULL,'"M_SMCPKCBQC"','"SMCPKCBQC03"', NULL"
 " ,NULL,NULL,19,.00000881379105873716,0,srec,15,6); END;"
IMP-00003: 遇到 ORACLE 错误 6550
ORA-06550: 第 1 行, 第 1061 列:
PLS-00103: Encountered the symbol "Y" when expecting one of the following:

   ) , * & = - + < / > at in is mod remainder not rem =>
   <an exponent (**)> <> or != or ~= >= < = <> and or like like2
   like4 likec between || multiset member submultiset
IMP-00017: 由于 ORACLE 错误 6550, 以下语句失败:
 "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '0031003300300030003"
 "0006D0041'; SREC.MAXVAL := '5C0F53CC6A21'; SREC.EAVS := 4; SREC.CHVALS := D"
 "BMS_STATS.CHARARRAY(utl_raw.cast_to_varchar2('00310033003000300030006D0041'"
 "),utl_raw.cast_to_varchar2('0032003600300030006D0041'),utl_raw.cast_to_varc"
 "har2('0035003200300030006D0041'),utl_raw.cast_to_varchar2('0037003800300030"
 "006D0041'),utl_raw.cast_to_varchar2('004F00540054002B004100420053002D0053')"
 ",utl_raw.cast_to_varchar2('004F00540054002B004100420053002D005300285B895353"
 "0029'),utl_raw.cast_to_varchar2('004F00540054002B00440054004D0042'),utl_raw"
 ".cast_to_varchar2('004F00540054002B00440054004D004200285B8953530029'),utl_r"
 "aw.cast_to_varchar2('004F00540054002B004400560042002D0043'),utl_raw.cast_to"
 "_varchar2('004F00540054002B004400560042002D004300285B8953530029'),utl_raw.c"
 "ast_to_varchar2('004F00540054002B004400560042002D0053'),utl_raw.cast_to_var"
 "char2('004F00540054002B00440056004200530032'),utl_raw.cast_to_varchar2('004"
 "F0054005457FA78407248'),utl_raw.cast_to_varchar2('004F005400545B8953537248'"
 "),'QvN?,'Y''SUj!','Y''S蘪!','\SUj!','\S蘪!'); SREC.NOVALS := DBMS_STATS."
 "NUMARRAY(993853854541110000000000000000000,10141371925997900000000000000000"
 "00,1074983183470710000000000000000000,1115549859588070000000000000000000,16"
 "02336355825990000000000000000000,1602336355825990000000000000000000,1602336"
 "355825990000000000000000000,1602336355825990000000000000000000,160233635582"
 "5990000000000000000000,1602336355825990000000000000000000,16023363558259900"
 "00000000000000000,1602336355825990000000000000000000,1602336355827610000000"
 "000000000000,1602336355827670000000000000000000,422975615901020000000000000"
 "000000000,462912036756159000000000000000000000,4629120735848750000000000000"
 "00000000,478002149501275000000000000000000000,47800218632999200000000000000"
 "0000000); SREC.BKVALS := DBMS_STATS.NUMARRAY(271,580,916,1188,1485,1784,207"
 "0,2353,2649,2940,3253,3546,3822,4108,4403,4689,4962,5252,5561); SREC.EPC :="
 " 19; DBMS_STATS.SET_COLUMN_STATS(NULL,'"M_SMCPKCBQC"','"SMCPKCBQC04"', NULL"
 " ,NULL,NULL,19,.00000881379105873716,0,srec,15,6); END;"
IMP-00003: 遇到 ORACLE 错误 6550
ORA-06550: 第 1 行, 第 1061 列:
PLS-00103: Encountered the symbol "Y" when expecting one of the following:

   ) , * & = - + < / > at in is mod remainder not rem =>
   </an><an exponent (**)> <> or != or ~= >= < = <> and or like like2
   like4 likec between || multiset member submultiset
</an>

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

分析：这是个很常见的导入警告，通常与数据库/客户端版本有关。比如这里，源数据库和目标数据库版本都是10.2.0.4，但导出的时候使用了10.2.0.1的客户端：
在别的项目中，还遇到过从10.2.0.2数据库中导入到10.2.0.4目标库时出现了同样的问题，不管imp/exp使用了什么版本。

那么，不导入统计信息会有什么影响呢。其实在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′)

查询：

select d.window_name,d.repeat_interval,d.duration  
from DBA_SCHEDULER_JOBS a,DBA_SCHEDULER_WINDOW_GROUPS b,DBA_SCHEDULER_WINGROUP_MEMBERS c,DBA_SCHEDULER_WINDOWS d  
where a.job_name='GATHER_STATS_JOB'  
  and a.schedule_name=b.window_group_name  
  and b.window_group_name=c.window_group_name  
  and c.window_name=d.window_name  
union all  
select d.window_name,d.repeat_interval,d.duration  
from DBA_SCHEDULER_JOBS a,DBA_SCHEDULER_WINDOWS d  
where a.job_name='GATHER_STATS_JOB'  
  and a.schedule_name=d.window_name;  

未经允许不得转载：SRE空间 » IMP导入数据出现imp-00017 ora-06550错误的解决办法