By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,016 Members | 2,265 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,016 IT Pros & Developers. It's quick & easy.

ORA-01555: snapshot too old: rollback segment number 24 with name "ROLL24" too small

P: 12
I have the following procedure
CREATE OR REPLACE procedure FOL_PURGE_CASES
as

/* type "SET SERVEROUTPUT ON" in sqlplus to debug !! */

cursor all_cases is
cursor all_cases is
select c.id00_warehouse, c.id00_case_nbr, c.id00_status_flag, c.id00_dlm
from phpick00 a, chcart00 b, idcase00 c
where a.ph00_shipto = 'Telford'
and a.ph00_soldto = 'Buncrana'
and b.ch00_pkt_ctl_nbr = a.ph00_pkt_ctl_nbr
and b.ch00_ref_case_nbr = c.id00_case_nbr
and c.id00_warehouse = 'KL'
and a.ph00_warehouse = 'KL'
and b.ch00_warehouse = 'KL'
and c.id00_status_flag >= '90'
and a.ph00_pkt_stat_flg = 'I'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by c.id00_dlm

union all
select c.id00_warehouse, c.id00_case_nbr, c.id00_status_flag, c.id00_dlm
from phpick00 a, chcart00 b, idcase00 c
where a.ph00_shipto = 'Kaisersl'
and a.ph00_soldto = 'Buncrana'
and b.ch00_pkt_ctl_nbr = a.ph00_pkt_ctl_nbr
and b.ch00_ref_case_nbr = c.id00_case_nbr
and c.id00_warehouse = 'TE'
and a.ph00_warehouse = 'TE'
and b.ch00_warehouse = 'TE'
and c.id00_status_flag >= '90'
and a.ph00_pkt_stat_flg = 'I'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by c.id00_dlm;

/* I have had it once that the users had somehow swapped cases and some of the cases shipped had
escaped my first query. That's why there is a second !! */

cursor all_cases_backup is
select cases.id00_warehouse, cases.id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = 'TE'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by id00_dlm
) cases, chcart00 b, phpick00 c
where b.ch00_case_nbr = cases.id00_reference_carton_nbr
and b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
and c.ph00_shipto = 'Kaisersl'
and c.ph00_soldto >= 'Buncrana' /* to trick the optimizer */
and c.ph00_soldto <= 'Buncrana'
and c.ph00_pkt_stat_flg = 'I'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by cases.id00_dlm
union all
select cases.id00_warehouse, cases.id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = 'KL'
and id00_status_flag = '95'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by id00_dlm
) cases, chcart00 b, phpick00 c
where b.ch00_case_nbr = cases.id00_reference_carton_nbr
and b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
and c.ph00_shipto = 'Telford'
and c.ph00_soldto >= 'Buncrana' /* to trick the optimizer */
and c.ph00_soldto <= 'Buncrana'
and c.ph00_pkt_stat_flg = 'I'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by cases.id00_dlm
UNION ALL
select id00_warehouse, id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = 'KL'
and id00_status_flag = '95'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
) cases,
chcart00 b,
phpick00 c
where b.ch00_case_nbr = id00_reference_carton_nbr
and b.ch00_pkt_ctl_nbr = ph00_pkt_ctl_nbr
and c.ph00_shipto in ('Edingen','EDINGEN','edingen')
and c.ph00_soldto >= 'Buncrana' /* to trick the optimizer */
and c.ph00_soldto <= 'Buncrana'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by cases.id00_dlm;


case_rec all_cases%rowtype;
case_b_rec all_cases_backup%rowtype;

counter integer;
total_recs integer;
total_back integer;

begin
dbms_output.put_line('Starting procedure FOL_PURGE_CASES');
dbms_output.put_line('Starting main loop');
counter := 1;
total_recs := 0;
for case_rec in all_cases
loop
if (case_rec.id00_status_flag < '90') then
exit; -- this should never happen
end if;

delete from idcase00
where id00_case_nbr = case_rec.id00_case_nbr
and id00_warehouse = case_rec.id00_warehouse
and id00_status_flag >= '90'; -- let's play extra safe !

total_recs := total_recs + 1;
counter := counter + 1;
if counter > 1000 then
commit;
counter := 1;
end if;
end loop;
commit;

counter := 1;
total_back := 0;
for case_b_rec in all_cases_backup
loop
delete from idcase00
where id00_case_nbr = case_b_rec.id00_case_nbr
and id00_warehouse = case_b_rec.id00_warehouse
and id00_status_flag >= '90'; -- let's play extra safe !

total_back := total_back + 1;
counter := counter + 1;
if counter > 1000 then
commit;
counter := 1;
end if;
end loop;
commit;

dbms_output.put_line('Normal end of procedure FOL_PURGE_CASES');
dbms_output.put_line(to_char(total_recs)||' records purged !!');
dbms_output.put_line(to_char(total_back)||' records purged ... WITH BACKUP QUERY !');

/* This has actually nothing to do with purging cases but I have to put it somewhere !
It solves a small error in the pkms programs. */

update illocn00
set il00_com_div = ' '
where il00_com_div > ' '
and il00_sku = ' '
and il00_actl_inventory_cases = 0
and il00_directed_putaway_cases = 0;

commit;

/* You shall probably not believe it but here comes another update to solve a problem in
the pkms programs. */

update illocn00
set il00_max_inventory_units = 0,
il00_remn_units_capacity = 0
where il00_max_inventory_units > 0;

commit;

dbms_output.put_line('Locations cleaned too !');


EXCEPTION
WHEN OTHERS THEN -- handles all other errors
dbms_output.put_line('EXCEPTION occurred !!');
dbms_output.put_line(SQLERRM);
ROLLBACK;
end;
/

I get an error message
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Starting procedure FOL_PURGE_CASES
Cleaning locations
Locations cleaned!
-----******-----
No putaway locations bug workaround!
No putaway locations bug workaround - cleaned!
Starting main loop
all_cases cusor complete
EXCEPTION occurred !!
ORA-01555: snapshot too old: rollback segment number 24 with name "ROLL24" too small

PL/SQL procedure successfully completed.

What can i change in the query for it to be more robust,
I know I can alter the rollback segment but it this good practice?
Nov 1 '06 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.