topic Re: How to avoid ORA-01555 errors? in Application Development and Automation Discussions

How to avoid ORA-01555 errors?

suman_pr — Mon, 26 Mar 2012 11:34:22 GMT

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.

Re: How to avoid ORA-01555 errors?

RaymondGiuseppi — Mon, 26 Mar 2012 11:44:00 GMT

Did you check Note 185822 - ora-1555 - cause and action

Regards,
Raymond

Re: How to avoid ORA-01555 errors?

Former Member — Mon, 26 Mar 2012 14:01:59 GMT

Your COMMIT is at the end of the program. Put it after each DELETE.

What I mean is - you are getting no benefit from the PACKAGE SIZE addition without using the additional COMMITS.

Also, why do you use native SQL. This looks to be much more easily done without it (and without the cursor).

Rob

Re: How to avoid ORA-01555 errors?

HermannGahm — Mon, 26 Mar 2012 20:19:51 GMT

Hi,

trace the program with ST12 and provide a trace.

Check your execution plan and check your internal table processing in

the loop. I could be the long run time for the select (fetches, and time between them) cause

the snapshot too old.

Kind regards,

Hermann

Re: How to avoid ORA-01555 errors?

suman_pr — Tue, 27 Mar 2012 04:01:55 GMT

Hello Rob,

Thank you for the reply.

I am using Native SQL since a simple COMMIT WORK closes the opened cursor and leads to an INVALID CURSOR dump in the next iteration.

Let me explain my requirement in a little more detail:

1. I have a table from which some specific data needs to be deleted.

For Example: I need to delete data from Table ABC where the value of a particular field lies in the range 1 - 1000. The rest of the data should be untouched.

2. On using a normal select (with the check if the field value is within the specified range), it enters an endless loop when a table is small and no records meet the criteria. So I created a cursor so that the same records are not fetched again.

Thanks & Best Regards,

Suman

Re: How to avoid ORA-01555 errors?

yuri_ziryukin — Tue, 27 Mar 2012 04:12:40 GMT

I agree with Hermann, we don't know your package size and if the package size is large and the cycle inside the DO statement is taking long time, the extensive parallel activity in the system may cause ORA-1555.