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





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>










28.13.Schema Trigger
28.13.1.Schema trigger
28.13.2.AFTER CREATE ON SCHEMA
28.13.3.AFTER DDL ON SCHEMA
28.13.4.AFTER SUSPEND ON SCHEMA
28.13.5.use event attributes to provide more info
28.13.6.ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME