Thursday, March 7, 2013

Journal Import: Suspended!

Off of our wait event alert, there was a session that just sitting in "statement suspended, wait error to be cleared" which was a new wait event I had not seen before.  Doing some research I found it to be a Journal Import report which was in the process of deleting rows per this statement:

delete from GL_INTERFACE
where (user_je_source_name, nvl(group_id, -1)) in (listing of ledgers) and status = 'PROCESSED'
and request_id = user request
and rownum <= 5000
and (user_je_source_name, nvl(group_id, -1)) IN (SELECT src.user_je_source_name, nvl(group_id,-1) FROM gl_interface_control ic, gl_je_sources src WHERE ic.je_source_name = src.je_source_name
AND   ic.interface_run_id = run id
AND   ic.set_of_books_id = set of books id
AND   ic.status = 'I'
AND   nvl(ic.processed_table_code,'D') != 'S')

I can see in the table dba_outstanding_alerts information about this session as such:

Operation on resumable session Session 1579, Instance 1 session id 1579 suspended because of errors in rollback segment _SYSSMU1260_2942144267$. Error message is ORA-01628: max # extents (32765) reached for rollback segment _SYSSMU1260_2942144267$

Now the error message seems to be straightforward, and My Oracle Support notes ID 761848.1 & 788060.1 give some anecdotal evidence for what is going on, but we weren't able to get the root cause addressed in time to allow the report to complete successfully as it timed out internally and resulted in the report completing in Error.  This means I'm not exactly sure how we could have resolved it, yet I have some suspicion that if we could have expanded the maximum number of extents for this segment we would have seen the session resume and move along.  I'll put this down under as a mystery which needs research, because we need to learn more about it for next time when the report in question is something a lot more important and we can't go back to Oracle with an SR to fix.

No comments:

Post a Comment