Compose error

[Tool_Tip]

Dear all,

Task if failed due to below error :

Terminated: sqlstate 72000, errorcode 1555, message ORA-01555: snapshot too old: rollback segment number 3 with name "_SYSSMU3_268784675$" too small Cause:

Regards,

Tool_Tip

[Dana_Baldwin]

Hi @Tool_Tip 

This is a common Oracle error indicating that the rollback segments are not large enough to hold a read consistent view of the data you are querying before they are overwritten.

"

ORA-01555

snapshot too old: rollback segment number string with name "string" too small

---

Cause

rollback records needed by a reader for consistent read are overwritten by other writers

---

Action

If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments"

copied from: ORA-01555 - Database Error Messages

Thanks,

Dana

[Tool_Tip]

Dear Dana,

Thank you for your response. Hopefully this will fix our issue. We will ask our DBA team to check and take provided action.

[Tool_Tip]

Dear Dana, 

We have taken the given action but it seems same issue.
During its run following statement is running  and when we try to run it separately on database directly this is what we are getting :

Just want to double check if its something wrong with statements or FD columns ?

[Dana_Baldwin]

Hi @Tool_Tip 

When Compose runs the query a value is passed into that variable. Please substitute a value for &&5 to run the query directly in Oracle.

FYI, the ORA-01555 error is more likely when the database is busy - but increasing the undo tablespace and rollback segments should resolve it.

Thanks,

Dana

[Tool_Tip]

Dear Dana

Can you help to know where exactly we should substitute a value for &&5 in oracle?

[Tool_Tip]

Also Dana,

We have taken action you have suggested and then restarted the task in compose but it is almost more than 12 hr and the task is in running status as below :

[Dana_Baldwin]

@Tool_Tip within the single quotes where &&5 appears

[Dana_Baldwin]

@Tool_Tip How much data is being processed?

[Tool_Tip]

Almost 5.5M records... each record with go through package and after calculation returning respective result and then final result will get store into the table