DDL触发器

 CREATE TABLE ddl_log (
operation   VARCHAR2(30),
obj_owner   VARCHAR2(30),
object_name VARCHAR2(30),
sql_text    VARCHAR2(64),
attempt_by  VARCHAR2(30),
attempt_dt  DATE);

CREATE OR REPLACE TRIGGER ddl_trigger
BEFORE CREATE OR ALTER OR DROP
ON SCHEMA

DECLARE
 oper ddl_log.operation%TYPE;
 sql_text ora_name_list_t;
 i        PLS_INTEGER;
BEGIN
  SELECT ora_sysevent
  INTO oper
  FROM dual;

  i := sql_txt(sql_text);

  IF oper IN ('CREATE', 'DROP') THEN
    INSERT INTO ddl_log
    SELECT ora_sysevent, ora_dict_obj_owner,
    ora_dict_obj_name, sql_text(1), USER, SYSDATE
    FROM dual;
  ELSIF oper = 'ALTER' THEN
    INSERT INTO ddl_log
    SELECT ora_sysevent, ora_dict_obj_owner,
    ora_dict_obj_name, sql_text(1), USER, SYSDATE
    FROM sys.gv_$sqltext
    WHERE UPPER(sql_text) LIKE 'ALTER%'
    AND UPPER(sql_text) LIKE '%NEW_TABLE%';
  END IF;
END ddl_trigger;

 

--TEST

CREATE TABLE new_table (
charcol VARCHAR(20));