How to avoid ORA-01555 errors?

[suman_pr]

Dear All,

I have a piece of code for deleting data from a table based on a range specified.

Unfortunately, even though I have limited the package size of the cursor and have a COMMIT WORK within the loop, the undo tablespace just gets filled up and terminates with a ORA-01555 Error after running for 2-3 hours.

The Undo Tablespace size is 30GB with AUTOEXTEND = OFF (this unfortunately cannot be changed).

Kindly take a look at the below code and suggest how to go about resolving this error.

Thanks & Best Regards,

Suman

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

OPEN CURSOR WITH HOLD l_cursor FOR SELECT * FROM (ls_delete_tables-tabname)
WHERE plvar EQ ls_otype_plvar-plvar AND otype EQ ls_otype_plvar-otype ORDER BY PRIMARY KEY.

          DO.
            FETCH NEXT CURSOR l_cursor
            INTO TABLE <fs> PACKAGE SIZE lv_num_of_rows.

*           If no data is found, then exit
            IF sy-subrc <> 0.
              EXIT.
            ENDIF.

*           6.1.2 Read the target range for the given plvar / otype
            READ TABLE lt_targets INTO ls_targets
            WITH KEY otype = ls_otype_plvar-otype
                     plvar = ls_otype_plvar-plvar.

*           6.1.3 Delete data that does not belong to the given target range
            LOOP AT <fs> ASSIGNING <fs_line>.
              ASSIGN COMPONENT 'OBJID'
                     OF STRUCTURE <fs_line>
                     TO <fs_field>.
              IF <fs_field> IS ASSIGNED.
                IF <fs_field> LT ls_targets-objid_from OR
                   <fs_field> GT ls_targets-objid_to.
                  DELETE TABLE <fs> FROM <fs_line>.
                ENDIF.
              ENDIF.
            ENDLOOP.

*           6.1.4 Delete data from the database table
            DELETE (ls_delete_tables-tabname) FROM TABLE <fs>.
            EXEC SQL.
              COMMIT WORK.
            ENDEXEC.
          ENDDO.

[stefan_koehler · Accepted solution]

Hello Suman,

there is some important information missing:

1. What database version are you using?
2. Did you enable guarantee undo retention and what is the defined undo retention?
3. What is causing the high undo usage? Is it really this particular DELETE operation or is it the sum of all undo activities?

Also an important quote from the documentation:

For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.

The allocation algorithm itself is explained in metalink note #413732.1. However it seems more like you are using a single cursor (SELECT) and the undo retention does not meet your requirement to read the data in a consistent state - otherwise you would also face an ORA-30036.

Regards

Stefan

[suman_pr · Accepted solution]

Hello Stefan,

Thank you for the reply and apologies for not putting in all the information.

1. The DB Version is ORACLE (11.2.0.2.0).

2. I am not sure if guaranteed undo retention is enabled but i doubt it is since I did not receive any ORA-30036 errors during the run. The defined Undo Retention = 900 but the Auto Extend is set to NO. So I guess this parameter is being ignored during the run since the undo space is just not released after 15 minutes. Do let me know how I may check if undo retention guarantee is enabled in a system?

3. I am using the above code to delete data from large tables in the system and during the deletion I did not see any other activity running. So it is possible that the deletion of data from the large tables itself is causing this issue. I was under the impression that a commit releases the undo space but unfortunately, it is not which is in turn causing the error after running for about 3 hours.

Do let me know in case you have any inputs as to how to go about reducing the probability of this error.

Thanks &  Best Regards,

Suman

[stefan_koehler · Accepted solution]

Hello Suman,

you can verify your guaranteed undo retention setting by using the following query:

SQL> select RETENTION from DBA_TABLESPACES where TABLESPACE_NAME = 'PSAPUNDO';

As i already quoted from the documentation - if your UNDO table space has a fixed size - then yes the undo retention is handled otherwise.

The problem with your code is (as already mentioned as well), that you are using one single cursor and keep this open through the whole runtime. So your undo retention needs to cover this whole time frame too - and in your case it is not. As you are not allowed to change the database configuration, you need to adjust your code to close the cursor (and reopen it) after you have finished a work package of data.

Regards

Stefan

[suman_pr · Accepted solution]

Hello Stefan,

Thank you for the detailed reply.

Now the concept of UNDO is clear. I will modify the code so that the cursor is closed after a fixed number of iterations so that the UNDO tablespace is not exceeded and reopen it.

Thanks & Best Regards,

Suman

[Former Member · Accepted solution]

Hi Suman,

can you simply ask your DBAs to extend PSAPUNDO tablespace, to 60GBs for istance?

Regards

Leopoldo Capasso

[suman_pr · Accepted solution]

Hi Leopoldo,

Yes, I have already requested that. It has been increased to 30GB from 15GB. Additional extension is under process.

Thanks & Best Regards,

Suman

[Former Member]

Hello Suman,

There is really no solution for the ORA-1555 error just better ways to manage undo.  Moving to AUM is the best option here.  The following notes are useful and explain what you need to do

1035137  - Oracle Database 10g: Automatic Undo Retention,

600141   - Oracle9i: Automatic UNDO Management

Best Regards

Rachel;

[Former Member]

As Stefan said, the problem is that your select tries to do a consistent read of the data that is 2 or 3 hours old. Thus you need to make your overall execution of the delete job faster.

1. tune your SQL if possible

2. instead of building the packages with the cursor and package size use a loop and select with package size rows, delete these rows and do a new select. This can be slower in total, but you can avoid ORA-1555 then.

I hope i am clear enough, feel free to ask for details if needed.

Cheers Michael

[suman_pr]

Hello Rachel,

Thank you for your reply.

Unfortunately, I am not allowed to enable AUM.

Best Regards,

Suman

[stefan_koehler]

Hello Suman,

what?

... you are already using AUM.

Regards

Stefan

[Former Member]

Hi Suman.

Ora-01555 ( Snap shot too old )

1) Re-schedule long-running queries when the system has less DML load.

2) Increasing the size of your rollback segment (undo) size. Set the parameter automatic undo_retention to an higher value then 43200.

3)Don't fetch the report between commits.