注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

和申的个人主页

专注于java开发,1985wanggang

 
 
 

日志

 
 

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

2010-07-29 16:12:53|  分类: 数据库 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
imp导入分区表的错误处理 IMP-00017 error 20001 (oracle) - 和申 - 和申的个人主页 imp导入分区表的错误处理 IMP-00017 error 20001 (oracle) - 和申 - 和申的个人主页

导入分区表时有时候会出现如下错误:
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;

 

摘自:http://space.itpub.net/559237/viewspace-468158

 imp导入分区表的错误处理 IMP-00017 error 20001 (oracle) - 和申 - 和申的个人主页

org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: - 和申 - 和申的个人主页



  评论这张
 
阅读(2838)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016