This discussion is archived
9 Replies Latest reply: Feb 21, 2013 5:08 AM by Rob_J RSS

ora - 1555 error with one query

user10719327 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi

    So Multiple commits may help to sort ou this issue?



    Thanks
    PV
  • 3. Re: ora - 1555 error with one query
    sb92075 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points