设为首页 - 加入收藏 站长在线 - 常用服务器软件 - 在线站长工具 - 在线伪原创工具
您的当前位置:主页 > 网络教程 > Mysql > 正文

ORACLE 11g 使用ROWNUM完美解决ORA

来源:ZzWww 编辑:ZzWww 时间:2017-04-19

 

 

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;

 

 


          --》这个也尝试过了,不行,存储过程的动态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语句结果成功了。

 

 

 

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

 

尝试了下,存储过程执行成功了,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就搞定了。

 

 

参考文章:

 

 


TAG标签:完美 解决 使用 ORACLE ROWNUM

网友评论:

文章右边250
Top