Browse DevX
Sign up for e-mail newsletters from DevX


The ORA-01555 Error: A PL/SQL Developer Solution-2 : Page 2




Building the Right Environment to Support AI, Machine Learning and Deep Learning

How the Error Occurs
Recently I've been working on optimizing the DeleteClassification stored procedure. It works mainly with two tables—ValidTransaction (parent) and Classification (child)—which are in a one-to-many relationship with the TransactionId foreign key.

The purpose of this stored procedure is to delete Classification records for all ValidTransactions that fall within a specified period/month. Using the TransactionDate field from the ValidTransaction table selects records in the date range. In my case, ValidTransaction contains data for 12 periods (one year) with 1.2 million rows total (100,000 rows per month on average). The Classification table has 10 rows on average for one row in the ValidTransaction table, with 12 million rows total. Cleaning up the Classification data for a specified period/month means deleting 1 million rows from the Classification table.

This presents two major problems:

  • Execution is slow. It takes about 30 minutes to delete 1 million Classification records from a total of 12 million records.

  • The "ORA-01555: snapshot too old" error keeps cropping up. Although it happens only occasionally, whenever it does so it always occurs during the last six minutes of the run. The error occurs on the line where data is fetched from the cursor (also shown in red in Listing 1):

    fetch TransactToReset into v_TransactionId;

Listing 1 shows a simplified version of the original DeleteClassification stored procedure. TransactionId values are selected into the TransactToReset cursor for all rows in the ValidTransaction table within a specified month. Then these TransactionIds are fetched into the v_TransactionId variable and are deleted one by one. The v_LoopIndex variable is used to count deleted records and commit every one-hundred of them.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date