SQL & PL/SQL

Hello,

I have a procedure that loops records in a table, extracts the SQL stored in one of the fields and executes it. In addition, it updates the status of the statement.
Below is the code of the procedure.

PROCEDURE RunStatements(inType IN NUMBER) IS
-- cursor declaration:
CURSOR cur_stmt(vType NUMBER) IS
SELECT stmt_id, status, sql_stmt
FROM Stmts
WHERE stmt_type = vType
ORDER BY stmt_order;
BEGIN

-- loop and execute the statements:
FOR r IN cur_stmt(inType) LOOP
-- updates the status
UPDATE stmt
SET status = 'On'
WHERE stmt_id = r.stmt_id;
COMMIT;
-- executes the SQL
EXECUTE IMMEDIATE r.sql_stmt;
COMMIT;
-- updates the status
UPDATE stmt
SET status = 'Off'
WHERE stmt_id = r.stmt_id;
COMMIT;
END LOOP;

END;

In order to parallelize the execution of the statements, they are divided into two types - type 1 and type 2. The procedure is called through DBMS_JOB twice:

Job 1:
call RunStatements(1)
Job 2:
call RunStatements(2)

Both executions of the procedure are running simultaneously for about 3 hours during the night. Everything was OK until last week when an error started to occur every day.

Here is the problem:

One of the statements is executed very long - around 1,5 hours. It successfully inserts around 20M records in one table. However, after its finish, Oracle raises the following error:
ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7$" too small
The error is raised after Oracle tries to fetch the next record of the cursor!
After that I am executing the rest of the statements manually and there is not any problem.

Somehow, Oracle lost information about the cursor's data!!!

Can anyone help me understand why this is happening? Is it possible that the long running operation consumes the space reserved for the cursor? Is it possible that after some period of time without fetching any records Oracle decides that you no longer need the cursor? Is it possible that the error is caused by using simultaneously two identical cursor?

I have tried to increase the UNDO tablespace as stated in some articles but the error continues to appear.

Any help will be appreciated. We are in production and I have to wake up every morning at 4 a.m. to manually starts the not-executed statements.

Database: Oracle 10gR2
OS: Enterprise Linux RH

Thank you in advance.

Best regards,
Beroetz