SQL & PL/SQL

All,

With this project needing to be finished soon and an issue occuring on the local database, I am hopefuly one of you will have the answer or resolution so that I may complete this project soon....

Here is the setup..
10g database (remote)
9i database (local
DB Link from local to remote database

103 materialized views in local database that are refreshed by pulling data from dblink to remote database.

A PL/SQL procedure has been created which sets the v_failures variable = 0 and then performs a check to see if the current job has a failure and if so, inserts that value into the v_failures variable. When that reaches "1", then the procedure does nothing and closes out. If the failures are equal to "0" then it performs a DBMS_MVIEW.REFRESH procedure for each materialized view.

This worked the first time but its continually failing now with the ORA-01555 error (snapshot too old). From what I can tell, the dbms_job duration is 4 seconds and the Last_Exec is 2m 7s after it starts (8:30 PM). With that said, our DBAs working o nthe project have increased the Undo_Retention settings and assure us that shouldn't be the problem. Odd thing is, this never happened in the dev environment when we were developing/testing - only in the production environment once it got migrated.

I am looking for possible causes and possible solutions to the ORA-01555 error. A sample of the code in my procedure is below:

---------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE Ar_Mviews IS
V_FAILURES NUMBER := 0;
BEGIN
BEGIN
SELECT FAILURES INTO V_FAILURES FROM USER_JOBS WHERE SCHEMA_USER = 'CATEBS' AND WHAT LIKE '%DISCO_MVIEWS%';
IF V_FAILURES = 1 THEN NULL;
ELSE
DBMS_MVIEW.REFRESH ('AR_BATCH_RECEIPTS_V', 'C');
DBMS_OUTPUT.PUT_LINE(V_FAILURES); END IF;
END;

BEGIN
SELECT FAILURES INTO V_FAILURES FROM USER_JOBS WHERE SCHEMA_USER = 'CATEBS' AND WHAT LIKE '%DISCO_MVIEWS%';
IF V_FAILURES = 1 THEN NULL;
ELSE
DBMS_MVIEW.REFRESH ('AR_BATCHES_ALL_V', 'C');
DBMS_OUTPUT.PUT_LINE(V_FAILURES); END IF;
END;
END Ar_Mviews;
/

---------------------------------------------------------------------------------------------------------------