Issue: Trigger data purging job and the activity logs are not getting purged.

Issue

Trigger data purging job and the activity logs are not getting purged.

2022-03-02 13:55:48.726 [release-trigger-pool-1-thread-13] {} ERROR org.quartz.core.JobRunShell
   - Job TRIGGERS_DATA_PURGE.TRIGGERS_DATA_PURGE_JOB threw an unhandled Exception:

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQL
   Exception for SQL [DELETE FROM XLR_ACTIVITY_LOGS WHERE ACTIVITY_TYPE IN (?, ?, ?, ?)

AND EVENT_TIME < ?]; SQL state [99999]; error code [30036]; ORA-30036: unable to extend segment
    by 8 in undo tablespace 'UNDOTBS1'

; nested exception is java.sql.SQLException: ORA-30036: unable to extend segment by 8 in undo
    tablespace 'UNDOTBS1'

at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1542) 

Affected Products & Versions

Release with Trigger data purging options (10.1 and above)

Cause

The error is a common database error indicating that the specified tablespace has no more space available. There may be too many records that need to be removed.

Workaround/Resolution Action

Two solutions:

1. Contact your database administration team and increase the tablespace and see if the issue persists. But this may still cause errors if the data to be removed is huge. Hence, the second and recommended option
2. Change the retention period from the default (30 days) to a higher value (depending on the data you have) to reduce the result set for the list of records to be deleted. If you have the trigger activity logs for more than a year, we recommend you to set Purge trigger data older than to 365 days, and once it is working (check the logs to verify), reduce it by 30 days and let the purging service run with the revised period. Repeat this until you reach the retention period you are looking for and then it should be working fine as expected.

Considerations

See How to: understand Trigger data purging for more information.