Oracle PL/SQL Programming, Third Edition

AFTER SUSPEND Triggers

Oracle9i introduced a new type of trigger that fires whenever a statement is suspended. This might occur as the result of a space issue like exceeding an allocated tablespace quota. This functionality can be used in order to repair the conditions and allow the stalled operation to continue. AFTER SUSPEND triggers are a boon to busy developers tired of being held up by space errors, and to even busier DBAs who constantly have to resolve these errors.

For example, consider the situation faced by Batch Only, the star Oracle developer at Totally Controlled Systems. He is responsible for maintaining hundreds of programs that run overnight, performing lengthy transactions to summarize information and move it between disparate applications. At least twice a week, his pager goes off during the wee hours of the morning because one of his programs has encountered this Oracle error.

ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'

Batch then has the unenviable task of phoning Totally’s Senior DBA, Don T. Planahead, and begging for a space quota increase. Don’s usual question is, “How much do you need?” to which Batch can only feebly reply, “I don’t know because the data load fluctuates so much.” This leaves them both very frustrated, because Don wants control over the space allocation for planning reasons, and Batch doesn’t want his night’s sleep interrupted so often.

Setting Up for the AFTER SUSPEND Trigger

Thankfully, an AFTER SUSPEND trigger ...