1, ORA-00600: Internal error code
Oracle upgraded from 11.2.0.1 to 11.2.0.4. Developers reported a job failure, debugging error messages, ORA-00600: internal error code, parameters: [rwoirw: check ret val], [], [], [], ORA-06512:... As shown in the following figure:
C:\pic\oracle\2017040501.png
Look at the 2000-line package and see that 454 lines of code are a long sql of create tables, as follows:
|
|
2. The official solution is not the best one.
It's executed by dynamic sql, creating a table error problem. It's ok in version 11.2.0.1, and it's ok in version 11.2.0.4. Gogle said it's a bug in 11.2.0.4.
The official description of the bug is as follows and provides two solutions:
Refrence: Bug 14275161 - ORA-600 [rwoirw:check ret val] on CTAS with predicate move around (Doc ID 14275161.8)
The first is to upgrade to 12.1:
It is obviously inappropriate to upgrade to 12.1 after 11.2.0.4, and it would be more troublesome if there were new bug s at 12.1.
The second method: bypass the error by setting the implicit parameter _pred_move_around to false
This has also been tried, no, add altersystem set_pred_move_around= false in the dynamic sql of stored procedure, and then report the following error:
|
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> |
So the above two methods are not feasible. It is an error to take the create sql out of the dynamic stored procedure and execute it on 11.2.0.4 separately, and the error is exactly the same. Suddenly it comes to mind that there are too many temporary tables. Then immediately try adding rownum < 100; manually executing the create sql statement results in success.
3. Perfect solution with ROWNUM
Think about it, maybe in 11.2.0.4, create tablexx as select... from... The restrictions are stricter (or maybe a real bug?) It means that you don't know the selection behind it. from... If the total number or number has exceeded the default value of oracle, such as 1000, the error of ORA-00600 will be prompted. In accordance with this idea, I inquired out to select ___________. from... total number, add and rownum < 100000; execute the stored procedure again, alas, Skydrop Chery, succeeded.
Then divergent thinking, in case the total number of selects exceeds 100,000, what can we do? We can't always select count(1) from ____________ every time. Then create it. According to the test boundary value theory, a maximum value ok, then I try the minimum value, and rownum > - 1; (because it is possible to select empty records)? After a try, the stored procedure executes successfully, and rownum > - 1. Then the sql in the stored procedure is finally rewritten as follows:
/*===========================================================================*/
/*Building ads_amp_pd_ma_mercha, nt_sale table*/
/*===========================================================================*/
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, ''Supermarket'', ''100'', ''Cinema'', ''200'', ''Department store'', ''300'', ''clothing'', ''400'', ''children'', ''500'', ''Restaurant'', ''600'', ''Matching'', ''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
--Percentage of commission
(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, so far, the problem is solved perfectly, without changing the program code, system parameters, restart db, just add a simple and rownum > - 1 after where condition of the stored procedure create statement.
Reference article: http://blog.csdn.net/wengtf/article/details/25713255