cancel
Showing results for 
Search instead for 
Did you mean: 

How to avoid ORA-01555 errors?

suman_pr
Active Participant
0 Kudos
495

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.

View Entire Topic
stefan_koehler
Active Contributor
0 Kudos

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
Active Participant
0 Kudos

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
Active Contributor
0 Kudos

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
Active Participant
0 Kudos

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
0 Kudos

Hi Suman,

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

Regards

Leopoldo Capasso

suman_pr
Active Participant
0 Kudos

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