Setting Up for the AFTER SUSPEND Trigger

Thankfully, an AFTER SUSPEND trigger can eliminate the dark circles under both Don's and Batch's eyes. Here is how they work through the situation.

Batch discovers a particular point in his code that encounters the error most frequently. It is an otherwise innocuous INSERT statement at the end of a program that takes hours to run:

INSERT INTO monthly_summary ( acct_no, trx_count, total_in, total_out)VALUES ( v_acct, v_trx_count, v_total_in, v_total_out);

What makes this most maddening is that the values take hours to calculate, only to be immediately lost when the final INSERT statement fails. At the very least, Batch wants the program to suspend itself while he contacts Don to get more space allocated. He discovers that this can be done with a simple ALTER SESSION statement.

ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 NAME 'Monthly Summary';

This means that whenever this Oracle session encounters an out-of-space error, it will go into a suspended (and potentially resumable) state for 3600 seconds (1 hour). This provides enough time for Totally's monitoring system to page Batch, for Batch to phone Don, and Don to allocate more space. It's not a perfect system, but at least the hours spent calculating the data are no longer wasted.

Another problem faced by Batch and Don is that when they try to diagnose the situation in the middle of the night, they are both so tired and grumpy that time is wasted on misunderstandings. Thankfully, the need for explanations can be alleviated by another feature of suspended/resumable statements: the DBA_RESUMABLE view. This shows all sessions that have registered for resumable statements with the ALTER SESSION command shown above.

The RESUMABLE system privilege must be granted to users before they can enable the resumable option.

Now, whenever Batch's programs go into the suspended state he only has to phone Don and mumble "Check the resumable view." Don then queries it from his DBA account to see what is going on.

SQL> run 1 SELECT session_id, 2 name, 3 status, 4 error_number 5* FROM dba_resumable SESSION_ID NAME STATUS ERROR_NUMBER---------- -------------------- --------- ------------ 8 Monthly Summary SUSPENDED 1536 1 row selected.

This shows that session 8 is suspended because of ORA-01536: space quota exceeded for tablespace `tablespace_name'. From past experience, Don knows which schema and tablespace are involved, so he corrects the problem and mumbles into the phone "It's fixed." The suspended statement in Batch's code immediately resumes, and both Don and Batch can go back to sleep in their own beds.