Home » SQL & PL/SQL » SQL & PL/SQL » NOT urgent. ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$"
NOT urgent. ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$" [message #243917] Mon, 11 June 2007 00:59 Go to next message
karikal84
Messages: 4
Registered: June 2007
Junior Member
Hi Experts,

I'm using the following PL/SQL to create a temp table with few fields from a major table with about 300 Million entries. I'm getting "ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$" too small" error too often. Could you please let me know how to overcome this?

==========================
DECLARE

CURSOR get_range_cur
IS
SELECT *
FROM temp_3823358_range;


CURSOR get_stop_ind_cur
IS
SELECT stop_ind
FROM temp_3823358_range;


CURSOR get_prs_id_cur ( x_start_prs_id NUMBER, x_end_prs_id NUMBER )
IS
SELECT prs_id,
h_phone_formt_num,
o_phone_formt_num,
prs_source_sys_upd_dt,
prs_type_cd,
o_addr_country_cd,
prs_active_ind
FROM table_x
WHERE prs_id >= x_start_prs_id
AND prs_id < x_end_prs_id
ORDER BY prs_id;

v_counter NUMBER;
tmp_rec temp_3823358_range%ROWTYPE;
v_prs_id NUMBER;
v_stop_ind NUMBER;

BEGIN

OPEN get_range_cur;
FETCH get_range_cur INTO tmp_rec;
CLOSE get_range_cur;

v_prs_id := tmp_rec.end_prs_id;
v_counter := 0;

FOR prs_id_rec IN get_prs_id_cur ( tmp_rec.start_prs_id, tmp_rec.end_prs_id )
LOOP

BEGIN -- BEGIN INSERTING VALUES INTO TEMP_3823358 TABLE

IF (prs_id_rec.prs_type_cd in (8, 10, 14, 97, 98, 99))
THEN

IF (prs_id_rec.o_addr_country_cd = 'USA' OR prs_id_rec.o_addr_country_cd = 'CAN')
THEN

IF ((prs_id_rec.h_phone_formt_num IS NOT NULL) OR (prs_id_rec.o_phone_formt_num IS NOT NULL))
THEN

INSERT INTO TEMP_3823358 VALUES (prs_id_rec.prs_id, prs_id_rec.h_phone_formt_num, prs_id_rec.o_phone_formt_num, prs_id_rec.prs_source_sys_upd_dt, prs_id_rec.prs_active_ind);

END IF;

END IF;

END IF;

v_counter := v_counter + 1;

IF ( MOD( v_counter, 100 ) = 0 )
THEN

v_counter := 0;

OPEN get_stop_ind_cur;
FETCH get_stop_ind_cur INTO v_stop_ind;
CLOSE get_stop_ind_cur;

UPDATE temp_3823358_range
SET START_PRS_ID = prs_id_rec.prs_id;

IF v_stop_ind = 1
THEN

UPDATE temp_3823358_range
SET START_PRS_ID = prs_id_rec.prs_id;

COMMIT;
RETURN;

END IF; -- END STOP IND CHECK

COMMIT;

END IF; -- END IF( MOD() )

EXCEPTION
WHEN OTHERS THEN
RAISE;

END;

v_prs_id := prs_id_rec.prs_id;

END LOOP; -- END FOR

UPDATE temp_3823358_range -- Keeping track of the last prs_id processed
SET start_prs_id = v_prs_id;

COMMIT;

EXCEPTION
WHEN OTHERS THEN
RAISE;

END;
/
sho err;
==========================

Also, please let me know if i could speed up the process, as only around 50 Million records were traced in about 70 hrs. Will increasing a 'where' clause in the cursor definition and decreasing it from the IF statement within the procedure, help to increase the speed of the process?

Thanks,
Amar

[Updated on: Tue, 12 June 2007 08:36] by Moderator

Report message to a moderator

Re: Urgency : ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$" [message #243921 is a reply to message #243917] Mon, 11 June 2007 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post your Oracle version (4 decimals).
Please read and apply How to format your posts.
As it your procedure is unreadable, edit your post and format it. Indent the lines, break them to at most 80-100 characters (a line with 200 char can't be read)...

"When others then raise" is silly, if you just raise then let it raise, why do you catch it?
You commit inside a loop, this is the best way to get an ORA-1555. And committing every 100 rows is a good way to slow down your performances.

Regards
Michel

[Updated on: Mon, 11 June 2007 01:08]

Report message to a moderator

Re: Urgency : ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$" [message #243922 is a reply to message #243917] Mon, 11 June 2007 01:08 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
1st time poster who can not supply basic information requested in STICKY posts at top of forum
You're On Your OWN (YOYO)!
Previous Topic: Optimize the query
Next Topic: UPDATE USING CURSOR FLAT FILE PROBLEM!
Goto Forum:
  


Current Time: Sun Dec 04 17:05:32 CST 2016

Total time taken to generate the page: 0.11415 seconds