Trapped Multiple Times

AFTER SUSPEND triggers fire whenever a statement is suspended. Therefore, they can fire many times during the same statement. For example, suppose that the following hardcoded trigger is implemented:

/* File on web: increment_extents.sql */CREATE OR REPLACE TRIGGER after_suspendAFTER SUSPEND ON SCHEMADECLARE -- get the new max (current plus one) CURSOR curs_get_extents IS SELECT max_extents + 1 FROM user_tables WHERE table_name = 'MONTHLY_SUMMARY'; v_new_max NUMBER; BEGIN - fetch the new maximum extent value OPEN curs_get_extents; FETCH curs_get_extents INTO v_new_max; CLOSE curs_get_extents; -- alter the table to take on the new value for maxextents EXECUTE IMMEDIATE 'ALTER TABLE MONTHLY_SUMMARY ' || 'STORAGE ( MAXEXTENTS ' || v_new_max || ')'; DBMS_OUTPUT.PUT_LINE('Incremented MAXEXTENTS to ' || v_new_max);END;

If you start with an empty table with MAXEXTENTS (maximum number of extents) specified as 1, inserting four extents' worth of data produces this output:

SQL> @test Incremented MAXEXTENTS to 2Incremented MAXEXTENTS to 3Incremented MAXEXTENTS to 4 PL/SQL procedure successfully completed.

18.5.7 To Fix or Not To Fix?

That is the question! The previous examples have shown how "lack of space" errors can be handled on the fly by suspending statements until intervention (human or automated) allows them to continue. Taken to an extreme, this approach allows applications to be installed with minimal tablespace, quota, and extent settings, and then to grow as required. While overdiligent DBAs may see this situation as nirvana, it does have its downsides:

Intermittent pauses

Suspended statement pauses may wreak havoc withhigh-volume online transaction processing (OLTP) applications that require high throughput levels. This will be even more troublesome if the fix takes a long time.

Resource contention

Suspended statements maintain their table locks, which may cause other statements to wait long periods of time or fail needlessly.

Management overhead

The resources required to continuously add extents or datafiles, or increment quotas may wind up overwhelming those required to actually run the application

For these reasons I recommend that AFTER SUSPEND triggers be used judiciously. They are perfect for long-running processes that must be restarted after failure, as well as for incremental processes that require DML to undo their changes before they can be restarted. However, they are not well suited to OLTP applications.