Section 19.5.  AFTER SUSPEND Triggers

19.5. 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): CREATE [OR REPLACE] TRIGGER trigger_name AFTER SUSPEND ON {DATABASE | SCHEMA} BEGIN ... code ... 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 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. 19.5.1. 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 3,600 seconds (1 hour). This provides enough time for Totally's monitoring system to page Batch, for Batch to phone Don, and for 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> 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. Invalid DDL Operation in System Triggers AFTER SUSPEND triggers are not allowed to actually perform certain DDL (ALTER USER and ALTER TABLESPACE) to fix the problems they diagnose. They will simply raise the error ORA-30511: Invalid DDL operation in system triggers. One way to work around this situation is as follows: Have the AFTER SUSPEND trigger write the SQL statement necessary to fix a problem in a table. Create a PL/SQL package that reads SQL statements from the table and executes them. Submit the PL/SQL package to DBMS_JOB to run every minute or so. 19.5.2. Looking at the Actual Trigger After a few weeks, both Don and Batch are tired of their repetitive, albeit abbreviated late-night conversations, so Don sets out to automate things with an AFTER SUSPEND trigger. Here's a snippet of what he cooks up and installs in the DBA account: /* File on web: smart_space_quota.sql */ CREATE OR REPLACE TRIGGER after_suspend AFTER SUSPEND ON DATABASE DECLARE ... BEGIN -- if this is a space related error... IF ORA_SPACE_ERROR_INFO ( error_type => v_error_type, object_type => v_object_type, object_owner => v_object_owner, table_space_name => v_tbspc_name, object_name => v_object_name, sub_object_name => v_subobject_name ) THEN -- if the error is a tablespace quota being exceeded... IF v_error_type = 'SPACE QUOTA EXCEEDED' AND v_object_type = 'TABLE SPACE' THEN -- get the username OPEN curs_get_username; FETCH curs_get_username INTO v_username; CLOSE curs_get_username; -- get the current quota for the username and tablespace OPEN curs_get_ts_quota(v_object_name,v_username); FETCH curs_get_ts_quota INTO v_old_quota; CLOSE curs_get_ts_quota; -- create an ALTER USER statement and send it off to -- the fixer job because if we try it here we will raise -- ORA-30511: invalid DDL operation in system triggers v_new_quota := v_old_quota + 40960; v_sql := 'ALTER USER ' || v_username || ' ' || 'QUOTA ' || v_new_quota || ' ' || 'ON ' || v_object_name; fixer.fix_this(v_sql); END IF; -- tablespace quota exceeded END IF; -- space related error END; This creates a trigger that fires whenever a statement enters a suspended state and attempts to fix the problem. (Note that this particular example handles only tablespace quotas being exceeded.) Now when Batch's programs encounter the tablespace quota problem, the database-wide AFTER SUSPEND trigger fires and puts a SQL entry in the "stuff to fix" table via the fixer package. In the background, a fixer job is running; it picks the SQL statement out of the table and executes it, thus alleviating the quota problem without requiring anyone to pick up the phone. A complete AFTER_SUSPEND trigger and fixer package are available in the fixer.sql file on the book's web site. 19.5.3. The ORA_SPACE_ERROR_INFO Function Information on the cause of the statement suspension may be garnered using the ORA_SPACE_ERROR_INFO function shown in earlier examples. Now let's look at the syntax for specifying this function; the parameters are defined as shown in Table 19-3. Table 19-3. ORA_SPACE_ERROR_INFO parameters Parameter Description ERROR_TYPE The type of space error; will be one of the following: SPACE QUOTA EXCEEDED: if a user has exceeded his quota for a tablespace MAX EXTENTS REACHED: if an object attempts to go beyond its maximum extents specification NO MORE SPACE: if there is not enough space in a tablespace to store the new information OBJECT_TYPE The type of object encountering the space error OBJECT_OWNER The owner of the object encountering the space error TABLE_SPACE_NAME The tablespace encountering the space error OBJECT_NAME The name of the object encountering the space error SUB_OBJECT_NAME The name of the subobject encountering the space error The function returns a Boolean value of TRUE if the suspension occurs because of one of the errors shown in the table, and FALSE if not. The ORA_SPACE_ERROR_INFO function does not actually fix whatever space problems occur in your system; its role is simply to provide the information you need to take further action. In the earlier example, we saw how the quota error was addressed. Here are two additional examples of SQL you might supply to fix space problems diagnosed by the ORA_SPACE_ERROR_INFO function: Specify the following when your table or index has achieved its maximum extents and no more extents are available: ALTER object_type object_owner.object_name STORAGE (MAXEXTENTS UNLIMITED); Specify the following when your tablespace is completely out of space: /* Assume Oracle Managed Files (Oracle9i Database and later) being used so explicit datafile declaration not required */ ALTER TABLESPACE table_space_name ADD DATAFILE; 19.5.4. The DBMS_RESUMABLE Package If the ORA_SPACE_ERROR_INFO function returns FALSE, then the situation causing the suspended statement cannot be fixed. Thus, there is no rational reason for remaining suspended. Unfixable statements can be aborted from within the AFTER_SUSPEND trigger using the ABORT procedure in the DBMS_RESUMABLE package . The following provides an example of issuing this procedure: /* File on web: local_abort.sql */ CREATE OR REPLACE TRIGGER after_suspend AFTER SUSPEND ON SCHEMA DECLARE CURSOR curs_get_sid IS SELECT sid FROM v$session WHERE audsid = SYS_CONTEXT('USERENV','SESSIONID'); v_sid NUMBER; v_error_type VARCHAR2(30); ... BEGIN IF ORA_SPACE_ERROR_INFO(... ...try to fix things... ELSE -- cant fix the situation OPEN curs_get_sid; FETCH curs_get_sid INTO v_sid; CLOSE curs_get_sid; DBMS_RESUMABLE.ABORT(v_sid); END IF; END; The ABORT procedure takes a single argument, the ID of the session to abort. This allows ABORT to be called from a DATABASE- or SCHEMA-level AFTER SUSPEND trigger. The aborted session receives this error: ORA-01013: user requested cancel of current operation After all, the cancellation was requested by a user, but exactly which user is unclear. In addition to the ABORT procedure, the DBMS_RESUMABLE package contains functions and procedures to get and set timeout values: GET_SESSION_TIMEOUT Returns the timeout value of the suspended session by session ID: FUNCTION DBMS_RESUMABLE.GET_SESSION_TIMEOUT (sessionid IN NUMBER) RETURN NUMBER; SET_SESSION_TIMEOUT Sets the timeout value of the suspended session by session ID: PROCEDURE DBMS_RESUMABLE.SET_SESSION_TIMEOUT ( sessionid IN NUMBER, TIMEOUT IN NUMBER); GET_TIMEOUT Returns the timeout value of the current session: FUNCTION DBMS_RESUMABLE.GET_TIMEOUT RETURN NUMBER; SET_SESSION_TIMEOUT Sets the timeout value of the current session: PROCEDURE DBMS_REUSABLE.SET_TIMEOUT (TIMEOUT IN NUMBER); New timeout values take effect immediately but do not reset the counter to zero. 19.5.5. 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_suspend AFTER SUSPEND ON SCHEMA DECLARE -- 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 2 Incremented MAXEXTENTS to 3 Incremented MAXEXTENTS to 4 PL/SQL procedure successfully completed. 19.5.6. 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 over-diligent DBAs may see this situation as nirvana, it does have its down sides: Intermittent pauses Suspended statement pauses may wreak havoc with high-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.