I am running the query given below.
SELECT /*+RULE */
TRUNC (a.trans_dt) trans_dt, a.store_id,
NVL (NVL (c.cust_id, d.cust_id), 0) cust_id,
b.region_id || LPAD (a.prod_grp, 3, 0) prod_grp, a.amount
FROM edeka.trans e,
WHERE a.store_id = b.store_id
AND a.card_id = c.card_id(+)
AND a.card_id = d.antragsnummer(+)
AND a.trans_id = e.trans_id
ORDER BY 3, 4, 2
This is a long running query. Usually this query ends up in 6-7 hours.
But from past few runs this is ending up with Ora-1555 Error.
There are no insert,update, delete running, only few other sql queries are running against the database along with the above query.
My undo_retention setting is set to 900.
Is it possible that this might occur because of some temporary segment conflict as my query is using lot of temporary space approx. 10 GB?