Snapshot too old

From: TRFMO-ISD <fmohidin_at_truworths.co.za>
Date: 1998/02/26
Message-ID: <01bd4291$c8681f40$210710ac@trfmo-is>#1/1

Hi there

While running the following procedure, I get the following Oracle Error Message:

ORA-01555: snapshot too old: rollback segment number 3 with name "R02" too small

The size of the Rollback segments are: Initial 1M, Next 1M, Optimal 20M, Maxextents 99.

The amount of Rows being selected into the cursor is +- 1.4 million and the size of the source table is 80M.

Here is the procedure:

PROCEDURE LOAD3_RAG_SKUS_ST_HIST_FM    IS

       Cursor RSkusCursor is SELECT *
		       FROM tru_stg_rag_skus_st_hist;


--		        where rownum < 10000;

    RSkusRecord RSkusCursor%ROWTYPE;
    v_trans_cnt 	    number (5):=0;
    v_total		    number := 0;

BEGIN
open RskusCursor;
fetch RskusCursor into RSkusRecord;
while RSKUsCursor%FOUND LOOP
-- Update the Retek table

     update RAG_SKUS_ST_HIST
        	set SALES = 1
        	where SKU   = RSkusRecord.sku and
            	    STORE     = RSkusRecord.from_branch and
            	    EOW_DATE  = RSkusRecord.transaction_date and
		    SALES_TYPE = 'R';
    If SQL%NOTFOUND then
  	insert into RAG_SKUS_ST_HIST (sku, style, store, eow_date, sales_type)
  	values
    	       (RSkusRecord.sku, RSkusRecord.type||RSkusRecord.style,
    	         RSkusRecord.from_branch, RSkusRecord.transaction_date, 'R');
    End if;
        v_trans_cnt:= v_trans_cnt + 1;
        If v_trans_cnt >= 500 then
    		commit;
    		v_total:=v_total + v_trans_cnt;


--    		dbms_output.put_line ('Committed ' || v_trans_cnt);

--    		dbms_output.put_line ('Total Committed ' || v_total);

    		v_trans_cnt:=0;
        End if;

fetch RskusCursor into RSkusRecord;
END LOOP;
close RSkusCursor;
--COMMIT;

END;
--

Faizel Mohidin

           Oracle DBA
           Truworths International
           Cape Town, South Africa
           Tel: +27 21 460-7561            
           Fax:  +27 21 461-7641
           E-mail: fmohidin_at_truworths.co.za

Received on Thu Feb 26 1998 - 00:00:00 CST