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 is what he cooks up and installs in the DBA account:
/* File on web: smart_space_quota.sql */CREATE OR REPLACE TRIGGER after_suspendAFTER SUSPENDON DATABASEDECLARE -- cursor to get the username for the current session CURSOR curs_get_username IS SELECT username FROM v$session WHERE audsid = SYS_CONTEXT('USERENV','SESSIONID'); v_username VARCHAR2(30); -- cursor to get the quota for the user/tablespace CURSOR curs_get_ts_quota (cp_tbspc VARCHAR2, cp_user VARCHAR2) IS SELECT max_bytes FROM dba_ts_quotas WHERE tablespace_name = cp_tbspc AND username = cp_user; v_old_quota NUMBER; v_new_quota NUMBER; -- hold information from SPACE_ERROR_INFO v_error_type VARCHAR2(30); v_object_type VARCHAR2(30); v_object_owner VARCHAR2(30); v_tbspc_name VARCHAR2(30); v_object_name VARCHAR2(30); v_subobject_name VARCHAR2(30); -- SQL to fix things v_sql VARCHAR2(1000); 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 only handles tablespace quotas being exceeded.)
| 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: 1. Have the AFTER SUSPEND trigger write the SQL statement necessary to fix a problem in a table. 2. Create a PL/SQL package that reads SQL statements from the table and executes them. 3. Submit the PL/SQL package to DBMS_JOB to run every minute or so. |
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.
|
