imp导入分区表的错误处理 IMP-00017 error 20001

导入分区表时有时候会出现如下错误：
IMP-00003: ORACLE error 20001 encountered
ORA-20001: Invalid or inconsistent input values
ORA-06512: at "SYS.DBMS_STATS", line 6053
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 20001:
 "BEGIN  DBMS_STATS.SET_TABLE_STATS(NULL,'"HCL"','"SYS_SUBP5290"'"
 ",NULL,NULL,-1,-1,-1,0); END;"
可能是在导入数据后再导入统计信息时出错误，经过测试发现导出数据量比较小的情况下不会出现该过错，具体原因待查。避免该类错误在导入时加上statistics=none，说明不再导入统计信息：
imp c/c full=n file=H.dmp tables=(hcl) buffer=12800000 statistics=none log=a.log commit=y
对于导入数据很大的情况，如果buffer设置的过小则会出现导入错误：IMP-00032:   SQL   语句超过缓冲区长度  
这样导入的用户中的表或索引没有做统计分析。

如果在导入完成后要对该用户下的所有表和索引进行统计分析，可执行如下过程：
sql>set serverout on
sql>exec dbms_output.enable(2000000) --设置较大的输出缓冲
sql>exec p_anayze_schema(username); --username改成实际的shcema名

统计过程如下：
create or replace procedure p_anayze_schema(as_username in varchar2) is
  /*
  　　该存储过程主要是对表及索引进行分析,
  　　
  　　对于包含有子分区sub-partition的表需要注意一下granularity参数。具体参考：
  　　
  　　 granularity - the granularity of statistics to collect (only pertinent
  　　  if the table is partitioned)
  　　  default' - gather global- and partition-level statistics
  　　  'subpartition' - gather subpartition-level statistics
  　　  'partition' - gather partition-level statistics
  　　  'global' - gather global statistics
  　　  all' - gather all (subpartition, partition, and global) statistics
  */
  vi_per      number(3) := 100;
  vs_username varchar2(64);
  vi_start    number := dbms_utility.get_time;

begin

  vs_username := upper(as_username);
  for vcr_rec in (select segment_name,
                         segment_type,
                         ceil(sum(bytes) / 1024 / 1024) segment_size
                    from user_segments
                   where segment_name not like 'TMP_%'
                     and segment_name not like 'BIN$%'
                   group by segment_name, segment_type) loop
    case
      when vcr_rec.segment_type = 'INDEX' then
        case
          when vcr_rec.segment_size <= 100 then
            vi_per := 100;
          when vcr_rec.segment_size <= 300 then
            vi_per := 50;
          else
            vi_per := 20;
        end case;
     
        begin
          --delete old schema index statistics;
          dbms_stats.delete_index_stats(ownname => vs_username,
                                        indname => vcr_rec.segment_name);
        exception
          when others then
            null;
        end;
     
        begin
          --analyze index compute statistics;
          dbms_stats.gather_index_stats(ownname          => vs_username,
                                        indname          => vcr_rec.segment_name,
                                        estimate_percent => vi_per,
                                        degree           => 2);
        exception
          when others then
            null;
        end;
        dbms_output.put_line(vcr_rec.segment_name||'---size:'||vcr_rec.segment_size||'m, took '||ceil((dbms_utility.get_time - vi_start)/100)||'s');
        vi_start := dbms_utility.get_time;
      when vcr_rec.segment_type = 'TABLE' then
        --
        case
          when vcr_rec.segment_size <= 150 then
            vi_per := 100;
          when vcr_rec.segment_size <= 500 then
            vi_per := 50;
          else
            vi_per := 20;
        end case;
     
        begin
          --delete table analyze statistics
          dbms_stats.delete_table_stats(ownname => vs_username,
                                        tabname => vcr_rec.segment_name);
        exception
          when others then
            null;
        end;
     
        begin
          --analyze table compute statistics;
          dbms_stats.gather_table_stats(ownname          => vs_username,
                                        tabname          => vcr_rec.segment_name,
                                        estimate_percent => vi_per,
                                        cascade          => true,
                                        granularity      => 'all',
                                        degree           => 2,
                                        method_opt       => 'for all indexed columns');
        exception
          when others then
            null;
        end;
        dbms_output.put_line(vcr_rec.segment_name||'---size:'||vcr_rec.segment_size||'m, took '||ceil((dbms_utility.get_time - vi_start)/100)||'s');
        vi_start := dbms_utility.get_time;
     
      when vcr_rec.segment_type = 'TABLE PARTITION' then
        case
          when vcr_rec.segment_size <= 150 then
            vi_per := 100;
          when vcr_rec.segment_size <= 500 then
            vi_per := 50;
          else
            vi_per := 20;
        end case;
        begin
          --delete table analyze statistics
          dbms_stats.delete_table_stats(ownname => vs_username,
                                        tabname => vcr_rec.segment_name);
        exception
          when others then
            null;
        end;
        begin
          --analyze table compute statistics;
          dbms_stats.gather_table_stats(ownname          => vs_username,
                                        tabname          => vcr_rec.segment_name,
                                        estimate_percent => vi_per,
                                        cascade          => true,
                                        granularity      => 'all',
                                        degree           => dbms_stats.default_degree,
                                        method_opt       => 'for all indexed columns');
        exception
          when others then
            null;
        end;
     
      when vcr_rec.segment_type = 'INDEX PARTITION' then
        case
          when vcr_rec.segment_size <= 100 then
            vi_per := 100;
          when vcr_rec.segment_size <= 300 then
            vi_per := 50;
          else
            vi_per := 20;
        end case;
        begin
          --delete old schema index statistics;
          dbms_stats.delete_index_stats(ownname => vs_username,
                                        indname => vcr_rec.segment_name);
        exception
          when others then
            null;
        end;
     
        begin
          --analyze index compute statistics;
          dbms_stats.gather_index_stats(ownname          => vs_username,
                                        indname          => vcr_rec.segment_name,
                                        estimate_percent => vi_per,
                                        degree           => dbms_stats.default_degree);
        exception
          when others then
            null;
        end;
     
        dbms_output.put_line(vcr_rec.segment_name||'---size:'||vcr_rec.segment_size||'m, took '||ceil((dbms_utility.get_time - vi_start)/100)||'s');
        vi_start := dbms_utility.get_time;
        /** when vcr_rec.segment_type = 'lobindex' then
        vi_start := dbms_utility.get_time;
        when vcr_rec.segment_type = 'lobsegment' then
        vi_start := dbms_utility.get_time;**/
     else
         dbms_output.put_line(vcr_rec.segment_type);
    end case;
  end loop;
end;

 

 

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

转载于:http://blog.itpub.net/559237/viewspace-468158/