1) trigger AFTER SUSPEND for tablspace quota
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 database 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, 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. 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. Note: 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 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 every minute or so. 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: @@@Example: Create a table and a package to capture a suspend info and NDS SYS@ocm> !cat fixer.sql DROP TABLE stuff_to_fix / CREATE TABLE stuff_to_fix ( stuff VARCHAR2(1000) , fixed VARCHAR2(1) ) / CREATE OR REPLACE PACKAGE fixer IS /* pass dynamic sql to this procedure, then save to to a table(stuff_to_fix) */ PROCEDURE fix_this ( p_thing_to_fix_in IN VARCHAR2 ); /* run the dynmaic sql which in stuff_to_fix table */ PROCEDURE fix_stuff; END; / CREATE OR REPLACE PACKAGE BODY fixer IS PROCEDURE fix_this ( p_thing_to_fix_in IN VARCHAR2 ) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO stuff_to_fix( stuff , fixed ) VALUES ( p_thing_to_fix_in , 'N' ); COMMIT; END fix_this; PROCEDURE fix_stuff IS -- update data by rowid after using cursor get the rows CURSOR curs_get_stuff_to_fix IS SELECT stuff, ROWID FROM stuff_to_fix WHERE fixed = 'N'; BEGIN FOR rec IN curs_get_stuff_to_fix LOOP EXECUTE IMMEDIATE rec.stuff; UPDATE stuff_to_fix SET fixed = 'Y' WHERE ROWID = rec.rowid; END LOOP; COMMIT; END; END; / SYS@ocm> @fixer.sql Table dropped. Table created. Package created. Package body created. @@@Create trigger use package above, use sys to create the package only, Note: schema would use local trigger if there are the same after suspend. SYS@ocm> !cat tmp.sql CREATE OR REPLACE TRIGGER after_suspend AFTER SUSPEND ON DATABASE DECLARE -- cursor to get the username for current session CURSOR curs_get_username IS SELECT username --return single row single column FROM v$session WHERE audsid = SYS_CONTEXT('USERENV','SESSIONID'); gv_username VARCHAR2(30); -- cursor to get the quota for the user/tablespace CURSOR curs_get_ts_quota ( cp_tbspc_in IN VARCHAR2 , cp_user_in IN VARCHAR2 ) IS SELECT max_bytes --return single row single column FROM dba_ts_quotas WHERE tablespace_name = cp_tbspc_in AND username = cp_user_in; gv_old_quota NUMBER; gv_new_quota NUMBER; -- hold information form SPACE_ERROR_INFO gv_error_type VARCHAR2(30); gv_object_type VARCHAR2(30); gv_object_owner VARCHAR2(30); gv_tbspc_name VARCHAR2(30); gv_object_name VARCHAR2(30); gv_subobject_name VARCHAR2(30); -- SQL to fix things gv_sql VARCHAR2(1000); BEGIN -- if this is a space related error... IF ORA_SPACE_ERROR_INFO ( error_type => gv_error_type , object_type => gv_object_type , object_owner => gv_object_owner , table_space_name => gv_tbspc_name , object_name => gv_object_name , sub_object_name => gv_subobject_name ) THEN -- if the error is a tablespace quota being exceeded... IF gv_error_type = 'SPACE QUOTA EXCEEDED' AND gv_object_type = 'TABLE SPACE' THEN -- get the username OPEN curs_get_username; FETCH curs_get_username INTO gv_username; CLOSE curs_get_username; -- get the current quota OPEN curs_get_ts_quota( gv_object_name , gv_username ); FETCH curs_get_ts_quota INTO gv_old_quota; CLOSE curs_get_ts_quota; -- create an ALTER USER statement and send if off to the fixer job -- because if we try it here we will raise -- ORA-30511: invalid DDL operation in system triggers gv_new_quota := gv_old_quota + 40960; gv_sql := 'ALTER USER' || gv_username || ' QUOTA ' || gv_new_quota || ' ON ' || gv_object_name; -- run procedure fix_this in the package fixer fixer.fix_this(gv_sql); END IF; END IF; END; / SYS@ocm> @tmp.sql Trigger created. @@@Example: Create problem prepare: SYS@ocm> CREATE TABLESPACE test_suspend datafile '/u01/test.dbf' size 1M; SYS@ocm> ALTER USER hr DEFAULT TABLESPACE test_suspend; SYS@ocm> ALTER USER hr QUOTA 1M ON test_suspend; SYS@ocm> GRANT SELECT ON dba_source TO hr; SYS@ocm> GRANT RESUMABLE TO hr; begin: HR@ocm> ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 NAME 'Monthly Summary'; HR@ocm> CREATE TABLE tab_test_suspend AS SELECT * FROM dba_source; CREATE TABLE tab_test_suspendx AS SELECT * FROM dba_source * ERROR at line 1: ORA-30032: the suspended (resumable) statement has timed out ORA-01536: space quota exceeded for tablespace 'TEST_SUSPEND' YS@ocm> col stuff for a50 SYS@ocm> select * from stuff_to_fix; STUFF F -------------------------------------------------- - ALTER USER HR QUOTA 21012480 ON TEST_SUSPEND N 2@@@@Example02: use DBMS_RESUMABLE.abort to cancel user hanging SYS@ocm> !cat tmp.sql CREATE OR REPLACE TRIGGER after_suspend AFTER SUSPEND ON DATABASE DECLARE -- cursor to get the username for current session CURSOR curs_get_username IS SELECT username, sid --return single row single column FROM v$session WHERE audsid = SYS_CONTEXT('USERENV','SESSIONID'); gv_username VARCHAR2(30); gv_sid NUMBER; -- cursor to get the quota for the user/tablespace CURSOR curs_get_ts_quota ( cp_tbspc_in IN VARCHAR2 , cp_user_in IN VARCHAR2 ) IS SELECT max_bytes --return single row single column FROM dba_ts_quotas WHERE tablespace_name = cp_tbspc_in AND username = cp_user_in; gv_old_quota NUMBER; gv_new_quota NUMBER; -- hold information form SPACE_ERROR_INFO gv_error_type VARCHAR2(30); gv_object_type VARCHAR2(30); gv_object_owner VARCHAR2(30); gv_tbspc_name VARCHAR2(30); gv_object_name VARCHAR2(30); gv_subobject_name VARCHAR2(30); -- SQL to fix things gv_sql VARCHAR2(1000); BEGIN -- if this is a space related error... IF ORA_SPACE_ERROR_INFO ( error_type => gv_error_type , object_type => gv_object_type , object_owner => gv_object_owner , table_space_name => gv_tbspc_name , object_name => gv_object_name , sub_object_name => gv_subobject_name ) THEN -- if the error is a tablespace quota being exceeded... IF gv_error_type = 'SPACE QUOTA EXCEEDED' AND gv_object_type = 'TABLE SPACE' THEN -- get the username OPEN curs_get_username; FETCH curs_get_username INTO gv_username, gv_sid; CLOSE curs_get_username; -- get the current quota OPEN curs_get_ts_quota( gv_object_name , gv_username ); FETCH curs_get_ts_quota INTO gv_old_quota; CLOSE curs_get_ts_quota; -- create an ALTER USER statement and send if off to the fixer job -- because if we try it here we will raise -- ORA-30511: invalid DDL operation in system triggers gv_new_quota := gv_old_quota + 40960; gv_sql := 'ALTER USER '|| gv_username || ' QUOTA ' || gv_new_quota || ' ON ' || gv_object_name; -- run procedure fix_this in the package fixer fixer.fix_this(gv_sql); END IF; END IF; DBMS_RESUMABLE.abort(gv_sid); DBMS_OUTPUT.put_line(chr(10)); DBMS_OUTPUT.put_line(' Your Space Quota Exceeded !!! Asking DBA for space.'); DBMS_OUTPUT.put_line(chr(10)); END; / SYS@ocm> conn hr/hr Connected. HR@ocm> alter session enable resumable timeout 3600 name 'xx'; Session altered. HR@ocm> CREATE TABLE tab_test_suspendx AS SELECT * FROM dba_source; Your Space Quota Exceeded !!! Asking DBA for space. CREATE TABLE tab_test_suspendx AS SELECT * FROM dba_source * ERROR at line 1: ORA-01013: user requested cancel of current operation Supplement: 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 t o zero.