This content has been marked as final. Show 9 replies
user10719327 wrote:do NOT have COMMIT inside LOOP!
We are getting ora-1555 error for particularly one query.I am providing the details
1.Database version : 220.127.116.11
2.Database size : 1354 gb
3.Undo tablespace size : 118 GB
4.Undo retention is 18000 secs
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
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 ?))
only have single COMMIT after all rows have been DELETED
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?
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.