IT博客汇 | [原]ORACLE 11g 使用ROWNUM完美解决ORA-00600：内部错误代码

[原]ORACLE 11g 使用ROWNUM完美解决ORA-00600：内部错误代码

mchdba发表于 2017-04-09 22:46:53

1，ORA-00600：内部错误代码

Oracle从11.2.0.1升级到11.2.0.4，开发人员报告说一个job运行失败，调试有报错信息，ORA-00600:内部错误代码，参数：[rwoirw: check ret val]，[],[],[],[]，ORA-06512：…，如下图所示：

C:\pic\oracle\2017040501.png

查看这个2000行的包体，查看到454行代码是一个create table as的很长的sql，如下所示：

/*===========================================================================*/
  /*构建ads_amp_pd_ma_merchant_sale表*/
/*===========================================================================*/
  PROCEDURE ads_amp_pd_ma_merchant_sale AS
    v_createsql varchar2(32767);
  BEGIN
    PKG_COMMON.drop_table('ads_amp_pd_ma_merchant_sale');
 
    v_createsql := '
    create table ads_amp_pd_ma_merchant_sale as
    (select
      --0 as ID,
      PKG_ADS_AMP_PD.PARTNER_ID as PARTNER_ID,
      0 as PROJECT_ID,
      0 as CONT_ID,
      zjwgsals.bis_shop_name as BRAND_NAME,
      zjwgsals.sales_money as SALE_AMOUNT,
      0 as BUDGET_AMOUNT,
      decode(zjwgsals.rent_square, 0, 0, round(zjwgsals.sales_money / zjwgsals.rent_square, 2)) as PERFORMANCE_VALUE,
      decode(zjwgsals.sales_money, 0, 0, round(zjwgsals.must_zj / zjwgsals.sales_money, 2)) as RENT_SALE_RATE,
      decode(zjwgsals.sort_name_root, ''超市'', ''100'', ''影院'', ''200'', ''百货'', ''300'', ''服装'', ''400'', ''儿童'', ''500'', ''餐饮'', ''600'', ''配套'', ''700'', ''800'') as COMMERCIAL_TYPE,
      zjwgsals.sort_name_root as COMMERCIAL_TYPE_NAME,
      zjwgsals.YEAR as YEAR,
      zjwgsals.MONTH as MONTH,
      zjwgsals.qz_year_month as DUTY_MONTH,
      ''0'' as IS_DEL,
      sysdate as CREATE_DATE,
      --null as UPDATE_DATE,
      PKG_ADS_AMP_PD.USER_ID as CREATER_ID,
      --null as UPDATER_ID,
      zjwgsals.bis_project_id as OUT_PROJECT_ID,
      zjwgsals.bis_cont_id as OUT_CONTRACT_ID,
      round(zjwgsals.sales_money * nvl(mustrent.royalty_ratio, 0) / 100 / 30.42, 2) as RENT_PER_DAY,
      round(zjwgsals.sales_money * nvl(mustrent.royalty_ratio, 0) / 100, 2) as RENT_PER_MONTH
    from
      ads_amp_pd_ma_contract cont
    inner join
      (select
        a.*,
        to_number(substr(qz_year_month, 1, 4)) as YEAR,
        to_number(substr(qz_year_month, 6, 2)) as MONTH
      from dws_pd_cont_zjwgsals_1m a
      where dt = PKG_COMMON.YESTERDAY_YYYYMMDD
        and a.status_cd not in (''3'', ''5'')
        and a.sals_data_flg = ''1''
      ) zjwgsals
    on cont.OUT_PROJECT_ID = zjwgsals.bis_project_id and cont.OUT_CONTRACT_ID = zjwgsals.bis_cont_id
    left outer join
      --提成百分比
      (select
        bis_cont_id,
        year,
        royalty_ratio,
        guaranteed_money
      from ods_pd_bis_must_rent
      where dt = PKG_COMMON.YESTERDAY_YYYYMMDD
      ) mustrent
    on zjwgsals.bis_cont_id = mustrent.bis_cont_id and (to_number(zjwgsals.YEAR) - to_number(to_char(cont.START_DATE, ''yyyy'')) + 1) = mustrent.year
    --TODO:
    where zjwgsals.bis_project_id in (''6D7E1C7AFAFB43E986670A81CF444235'', ''402834702db81ec3012dbca135f20c6a'')
    )';
    executeimmediate v_createsql;
    COMMIT;
  END;

2，官方的解决途径不是最佳方案

是动态sql执行的，创建一个表出错的问题，在11.2.0.1的version里面是ok的，到了11.2.0.4就出问题了，google了下，说是11.2.0.4的一个bug。

官方对该bug的描述如下，并提供了2种解决途径：

Refrence：Bug 14275161 - ORA-600 [rwoirw:check ret val] on CTAS with predicate move around (Doc ID 14275161.8)

第一种是升级到12.1：

--》这个刚升级完11.2.0.4再升级到12.1显然不太合适，而且万一到了12.1，又有新的bug出现，那就比较麻烦了。

第二种方法：通过设置隐含参数 _pred_move_around 为false来绕过该错误

--》这个也尝试过了，不行，存储过程的动态sql里面，添加 altersystem set _pred_move_around =false;后报下面的错误出来：

SQL> call PKG_ADS_AMP_PD.build();

call PKG_ADS_AMP_PD.build()

ORA-04068: existing state of packages has been discarded

ORA-04061: existing state of package body "DW.PKG_ADS_AMP_PD" has been invalidated

ORA-04065: not executed, altered or dropped package body "DW.PKG_ADS_AMP_PD"

SQL>

所以说以上两种办法都不太可行，把create sql从动态存储过程里面拿出来，单独在11.2.0.4上面执行，是报错的，而且报错一模一样；突然想到难道是临时表的数量太多了导致的？然后马上尝试后面添加rownum < 100; 手动执行create sql语句结果成功了。

3，使用ROWNUM完美解决

思考了下，也许11.2.0.4里面对create tablexxx as select …. From …的限制比较严格（或者也许就是真的一个bug？），意味着在不知道后面的select … from …的总体数量的情况下或者数量已经超过了oracle的默认值比如1000这样，会提示ORA-00600的错误。按照这个思路我查询出来select … from ..的总数量，在后面加上and rownum<100000;，再次执行存储过程，哎，天降奇瑞，成功了。

然后再发散思维下，万一下回select的总数量超过100000了呢，那咋办，总不能每次都去select count(1) from …然后再create吧。按照测试的边界值理论，一个最大值ok了，那我尝试下最小值呢，用and rownum>-1;（因为有可能select 出来空记录）呢？尝试了下，存储过程执行成功了，and rownum>-1，那么最终改写的存储过程中的sql如下：

/*===========================================================================*/
  /*构建ads_amp_pd_ma_mercha，nt_sale表*/
/*===========================================================================*/
  PROCEDURE ads_amp_pd_ma_merchant_sale AS
    v_createsql varchar2(32767);
  BEGIN
    PKG_COMMON.drop_table('ads_amp_pd_ma_merchant_sale');
 
    v_createsql := '
    create table ads_amp_pd_ma_merchant_sale as
    (select
      --0 as ID,
      PKG_ADS_AMP_PD.PARTNER_ID as PARTNER_ID,
      0 as PROJECT_ID,
      0 as CONT_ID,
      zjwgsals.bis_shop_name as BRAND_NAME,
      zjwgsals.sales_money as SALE_AMOUNT,
      0 as BUDGET_AMOUNT,
      decode(zjwgsals.rent_square, 0, 0, round(zjwgsals.sales_money / zjwgsals.rent_square, 2)) as PERFORMANCE_VALUE,
      decode(zjwgsals.sales_money, 0, 0, round(zjwgsals.must_zj / zjwgsals.sales_money, 2)) as RENT_SALE_RATE,
      decode(zjwgsals.sort_name_root, ''超市'', ''100'', ''影院'', ''200'', ''百货'', ''300'', ''服装'', ''400'', ''儿童'', ''500'', ''餐饮'', ''600'', ''配套'', ''700'', ''800'') as COMMERCIAL_TYPE,
      zjwgsals.sort_name_root as COMMERCIAL_TYPE_NAME,
      zjwgsals.YEAR as YEAR,
      zjwgsals.MONTH as MONTH,
      zjwgsals.qz_year_month as DUTY_MONTH,
      ''0'' as IS_DEL,
      sysdate as CREATE_DATE,
      --null as UPDATE_DATE,
      PKG_ADS_AMP_PD.USER_ID as CREATER_ID,
      --null as UPDATER_ID,
      zjwgsals.bis_project_id as OUT_PROJECT_ID,
      zjwgsals.bis_cont_id as OUT_CONTRACT_ID,
      round(zjwgsals.sales_money * nvl(mustrent.royalty_ratio, 0) / 100 / 30.42, 2) as RENT_PER_DAY,
      round(zjwgsals.sales_money * nvl(mustrent.royalty_ratio, 0) / 100, 2) as RENT_PER_MONTH
    from
      ads_amp_pd_ma_contract cont
    inner join
      (select
        a.*,
        to_number(substr(qz_year_month, 1, 4)) as YEAR,
        to_number(substr(qz_year_month, 6, 2)) as MONTH
      from dws_pd_cont_zjwgsals_1m a
      where dt = PKG_COMMON.YESTERDAY_YYYYMMDD
        and a.status_cd not in (''3'', ''5'')
        and a.sals_data_flg = ''1''
      ) zjwgsals
    on cont.OUT_PROJECT_ID = zjwgsals.bis_project_id and cont.OUT_CONTRACT_ID = zjwgsals.bis_cont_id
    left outer join
      --提成百分比
      (select
        bis_cont_id,
        year,
        royalty_ratio,
        guaranteed_money
      from ods_pd_bis_must_rent
      where dt = PKG_COMMON.YESTERDAY_YYYYMMDD
      ) mustrent
    on zjwgsals.bis_cont_id = mustrent.bis_cont_id and (to_number(zjwgsals.YEAR) - to_number(to_char(cont.START_DATE, ''yyyy'')) + 1) = mustrent.year
    --TODO:
    where zjwgsals.bis_project_id in (''6D7E1C7AFAFB43E986670A81CF444235'', ''402834702db81ec3012dbca135f20c6a'')
    )' and rownum>-1;
    executeimmediate v_createsql;
    COMMIT;
  END;

OK，到此，问题完美解决，不改程序代码，不改系统参数，不用重启db，只需要在存储过程create语句where条件后面加上简单的and rownum>-1就搞定了。

参考文章：http://blog.csdn.net/wengtf/article/details/25713255