Schedule Purge Audit Data Cleanup - Not Working - SQLException:ORA-01555, Undo error( ORA-30036: unable to extend segment by 128 in undo tablespace 'UNDOTBS2)

Products

• CA Identity Manager
• CA Identity Governance
• CA Identity Portal
• CA Identity Suite

Issue/Introduction

The audit purge job fails with:

Undo error( ORA-30036: unable to extend segment by 128 in undo tablespace 'UNDOTBS2

The audit purge job is reporting that there is not enough undo tablespace to perform the operation.

 

Environment

Release:
Component: IDMAS

Cause

In the case above there were 641 million rows in the imsauditobjectattributes12 table alone because of a failure to maintain the audit tables' sizes. The audit tables have grown out of control. This is why the purge job runs endlessly without success.
 

Resolution

For the undo space error, your DBA can increase the amount of undo tablespace, however, when there are millions of rows in the audit tables CA recommends purging the audit tables followed by regular maintenance cleanups.

Work with your DBA to (archive or backup if required, then) truncate the following tables:

IMSAUDITEVENT12
IMSAUDITEVENTOBJECT12
IMSAUDITOBJECTATTRIBUTES12
IMSAUDITOBJECTRELATIONSHIP12
IMSAUDITTASKSESSION12