AFTER SUSPEND ON SCHEMA : Schema Trigger « Trigger « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Trigger
3. Schema Trigger

SQL>
SQL> CREATE OR REPLACE TRIGGER after_suspend
  2  AFTER SUSPEND ON SCHEMA
  3  DECLARE
  4    CURSOR curs_get_extents IS
  5    SELECT max_extents + 1
  6      FROM user_tables
  7     WHERE table_name = 'MONTHLY_SUMMARY';
  8    v_new_max NUMBER;
  9  BEGIN
 10    OPEN curs_get_extents;
 11    FETCH curs_get_extents INTO v_new_max;
 12    CLOSE curs_get_extents;
 13    EXECUTE IMMEDIATE 'ALTER TABLE MONTHLY_SUMMARY ' ||
 14                      'STORAGE ( MAXEXTENTS '        ||
 15                      v_new_max                      || ')';
 16    DBMS_OUTPUT.PUT_LINE('Incremented MAXEXTENTS to ' || v_new_max);
 17  END;
 18  /

Trigger created.

SQL> SHO ERR
No errors.
SQL>
SQL>
SQL>
SQL> drop trigger after_suspend;

Trigger dropped.

SQL>