9 Replies Latest reply: Feb 21, 2013 7:08 AM by Rob_J RSS

    ora - 1555 error with one query

    user10719327
      Hi


      We are getting ora-1555 error for particularly one query.I am providing the details

      1.Database version : 9.2.0.8

      2.Database size : 1354 gb

      3.Undo tablespace size : 118 GB

      4.Undo retention is 18000 secs
      SELECT
      distinct poi.company_code po_comp_code, poi.customer_number po_customer_number, poi.purchase_order_number, poi.purchase_order_line_item, poi.purchase_order_sub_item_1, poi.purchase_order_sub_item_2, poi.material_status, dt.company_code del_company_code, dt.site_code del_site_code, dt.delivery_number FROM PURGEWORKPO pw JOIN PurchaseOrderItem poi ON (poi.company_code = pw.purchase_order_company_code AND poi.customer_number = pw.purchase_order_customer_number AND poi.purchase_order_number = pw.purchase_order_number ) JOIN MaterialReceipt mr ON (mr.purchase_order_company_code = pw.purchase_order_company_code AND mr.purchase_order_customer_no = pw.purchase_order_customer_number AND mr.purchase_order_number = pw.purchase_order_number) JOIN ReceivingItem ri ON (ri.company_code = mr.company_code AND ri.site_code = mr.site_code AND ri.receiving_number = mr.receiving_number AND ri.package_number = mr.package_number) JOIN DeliveryTicket dt ON (dt.company_code = ri.del_company_code AND dt.site_code = ri.del_site_code AND dt.delivery_number = ri.delivery_number) WHERE pw.purge_session_id = ? AND (( poi.material_status <> 7) OR (DT.CONFIRMED_DATE NOT between ? AND
      ?)) 
      I am providing some part of the query which is used for purging the data.This query is failing with ora -1555 after Please let us know what action need to be taken.since undo tablespace size and retention time has enough values



      Thanks
      PV
        • 1. Re: ora - 1555 error with one query
          sb92075
          user10719327 wrote:
          Hi


          We are getting ora-1555 error for particularly one query.I am providing the details

          1.Database version : 9.2.0.8

          2.Database size : 1354 gb

          3.Undo tablespace size : 118 GB

          4.Undo retention is 18000 secs
          SELECT
          distinct poi.company_code po_comp_code, poi.customer_number po_customer_number, poi.purchase_order_number, poi.purchase_order_line_item, poi.purchase_order_sub_item_1, poi.purchase_order_sub_item_2, poi.material_status, dt.company_code del_company_code, dt.site_code del_site_code, dt.delivery_number FROM PURGEWORKPO pw JOIN PurchaseOrderItem poi ON (poi.company_code = pw.purchase_order_company_code AND poi.customer_number = pw.purchase_order_customer_number AND poi.purchase_order_number = pw.purchase_order_number ) JOIN MaterialReceipt mr ON (mr.purchase_order_company_code = pw.purchase_order_company_code AND mr.purchase_order_customer_no = pw.purchase_order_customer_number AND mr.purchase_order_number = pw.purchase_order_number) JOIN ReceivingItem ri ON (ri.company_code = mr.company_code AND ri.site_code = mr.site_code AND ri.receiving_number = mr.receiving_number AND ri.package_number = mr.package_number) JOIN DeliveryTicket dt ON (dt.company_code = ri.del_company_code AND dt.site_code = ri.del_site_code AND dt.delivery_number = ri.delivery_number) WHERE pw.purge_session_id = ? AND (( poi.material_status <> 7) OR (DT.CONFIRMED_DATE NOT between ? AND
          ?)) 
          I am providing some part of the query which is used for purging the data.This query is failing with ora -1555 after Please let us know what action need to be taken.since undo tablespace size and retention time has enough values
          do NOT have COMMIT inside LOOP!
          only have single COMMIT after all rows have been DELETED
          • 2. Re: ora - 1555 error with one query
            user10719327
            Hi

            So Multiple commits may help to sort ou this issue?



            Thanks
            PV
            • 3. Re: ora - 1555 error with one query
              sb92075
              user10719327 wrote:
              Hi

              So Multiple commits may help to sort ou this issue?
              only have single COMMIT after all rows have been DELETED
              • 4. Re: ora - 1555 error with one query
                Osama_Mustafa
                Also you can Refer to
                Master Note for ORA-1555 Errors [ID 1307334.1]
                ORA 01555 "snapshot too old: rollback segment number %s with name \"%s\" too small" [ID 1555.1]
                • 5. Re: ora - 1555 error with one query
                  Rob_J
                  Hi,

                  Here are some notes on managing UNDO and the [url http://www.ora00600.com/articles/oracle-undo-tablespace.html]ORA-01555 snaphot too old error message (a little further down the page).

                  Hope that helps,
                  Rob
                  • 6. Re: ora - 1555 error with one query
                    rahulras
                    Basic issue is, one transaction (or query) is running (for long time), while table(s) used in that query are changed massively by another process.
                    Can you find out in your case, while your process (which fails with ora-1555) is running, which other process(es) are changing the data used in the first process ??
                    It need not be exactly same data rows, as Oracle works at data block level when it comes to space management (and in turn undo management).
                    Is it something which use to work nicely before and failing now? or its a new code?
                    • 7. Re: ora - 1555 error with one query
                      user10719327
                      yes, I agree with your answer and I am trying to capture that information and could not be successful.Please kindly provide me some tips to capture the tables that are being updated by other process.I am just checking only current runniing sqls for the past 2 hrs and trying to find insert,update and other commands that are affecting these tables.But I am not able to capture that information.Please help.


                      Thanks
                      PV
                      • 8. Re: ora - 1555 error with one query
                        user10719327
                        Hi


                        In the above query,for table PURCHASEORDERITEM,indexPOITM_IX_1 is not getting picked up.Can anybody give some hint on the query


                        Thanks
                        Veera
                        • 9. Re: ora - 1555 error with one query
                          Rob_J
                          Hi,

                          You need to provide more information, and I'd suggest opening a new thread if it's not really related to the snapshot too old error.

                          You should generate an explain plan and provide the output.

                          If you want to force a query to use an index then you can use an index hint.

                          Rob