ORACLE DDL TRIGGER

—创建日志表

CREATE TABLE "SYS"."DDL_AUDIT_LOG" 
   (	"DDL_DATE" DATE, 
	"USER_NAME" VARCHAR2(30), 
	"IP_ADDR" VARCHAR2(30), 
	"OBJ_NAME" VARCHAR2(50), 
	"DDL_TYPE" VARCHAR2(30), 
	"OBJECT_TYPE" VARCHAR2(18), 
	"OWNER" VARCHAR2(30), 
	"SQL_TEXT" VARCHAR2(4000 CHAR)
   ) ;

—创建触发器

CREATE OR REPLACE TRIGGER SYS.DDL_AUDIT
   AFTER CREATE OR ALTER OR DROP OR TRUNCATE OR GRANT OR REVOKE OR RENAME
   ON DATABASE
DECLARE
   ipaddr   VARCHAR2 (20);
   STEXT    VARCHAR2 (2000);
BEGIN
   BEGIN
      SELECT SYS_CONTEXT ('USERENV', 'IP_ADDRESS') INTO ipaddr FROM DUAL;
   EXCEPTION
      WHEN OTHERS
      THEN
         ipaddr := '-';
   END;

   BEGIN
      SELECT SQL_TEXT
        INTO STEXT
        FROM v$open_cursor
       WHERE    UPPER (sql_text) LIKE 'ALTER%'
             OR UPPER (sql_text) LIKE 'DROP%'
             OR UPPER (sql_text) LIKE 'TRUNCATE%'
             OR UPPER (sql_text) LIKE 'GRANT%'
             OR UPPER (sql_text) LIKE 'REVOKE%';
   EXCEPTION
      WHEN OTHERS
      THEN
         STEXT := '-';
   END;

   INSERT INTO SYS.DDL_AUDIT_LOG
       VALUES (SYSDATE,
               USER,
               NVL (ipaddr, '-'),
               NVL (ora_dict_obj_name, '-'),
               NVL (ORA_SYSEVENT, '-'),
               NVL (ora_dict_obj_type, '-'),
               NVL (ora_dict_obj_owner, '-'),
               STEXT);
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;
/