Oracle PL/SQL Programming, 5th Edition

AFTER SUSPEND Triggers

Oracle9i Database Release 1 introduced a new type of trigger that fires whenever a statement is suspended. This might occur as the result of a space issue such as exceeding an allocated tablespace quota. This functionality can be used to address the problem 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.

The syntax used to create an AFTER SUSPEND trigger follows the same format as DDL and database event triggers. It declares the firing event (SUSPEND), the timing (AFTER), and the scope (DATABASE or SCHEMA):

1 CREATE [OR REPLACE] TRIGGER trigger_name
2 AFTER SUSPEND
3 ON {DATABASE | SCHEMA}
4 BEGIN
5 ... code...
6 END;

Let’s take a closer look at AFTER SUSPEND, starting with an example of a scenario that would call for creation of this type of trigger.

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 ...