large Oracle SQL transactions creating large archive logs and filling space on the DB server.

Document ID : KB000004911
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

Although no deployments are running currently, there are some large DB transactions running.

These are creating large archive Logs on the DB server and memory usage on DB server reaching about 90%.

One of the SQLs running might be:

 

DELETE FROM step_events WHERE job_id = :1

 

 

Environment:
Release Automation 5.0 or higher running on Oracle database
Cause:

At the same time the problem is seen,  nolio_purging.log starts logging errors like: 

2016-12-06 12:56:47,563 [JobToOffline-1] ERROR (com.nolio.platform.server.dataservices.services.purging.ActiveJobToOfflineMonitor:124) - Failed to verify old processes in database 
java.lang.RuntimeException: org.hibernate.exception.GenericJDBCException: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO' 

This points to the UNDO tablespace being full:

Oracle doc explains in http://ora-30036.ora-code.com/ the ORA-30036: 

ORA-30036: 
unable to extend segment by string in undo tablespace 'string' 
Cause: the specified undo tablespace has no more space available. 
Action: Add more space to the undo tablespace before retrying the operation. An alternative is to wait until active transactions to commit. 

Resolution:

add a datafile to the UNDO tablespace with autoextend or a fixed size if autoextend is not permitted.

Googling for ORA-30036 results in lots of hits on how to overcome, e.g. http://www.dba-oracle.com/sf_ora_30036_unable_to_extend_segment_by_string_in_undo_tablespace_string.htm

Additional Information:

More information about how purge operations may cause the UNDO tablespace to fill up quickly can be found on https://www.ca.com/us/services-support/ca-support/ca-support-online/knowledge-base-articles.tec1605915.html

 The article was written for MSSQL, but the transaction log in MSSQL is comparable with the UNDO tablespace in Oracle.