Oracle DDL audit - made simple

ddl_audit.sql

CREATE TABLE ddl_log (
  operation  VARCHAR2(30),
  obj_owner  VARCHAR2(30),
  obj_name   VARCHAR2(30),
  obj_type   VARCHAR2(30),
  sql_text   CLOB,
  attempt_by VARCHAR2(30),
  attempt_dt DATE,
  user_name  VARCHAR2(50),
  user_host  VARCHAR2(50)
);

CREATE TABLE ddl_ignore (
  operation VARCHAR2(30),
  obj_owner VARCHAR2(30),
  obj_name  VARCHAR2(30),
  obj_type  VARCHAR2(30)
);

CREATE OR REPLACE TRIGGER ddl_trigger
BEFORE DDL
ON SCHEMA

  DECLARE
    stmt     CLOB;
    sql_text ora_name_list_t;
  BEGIN
    FOR i IN 1 .. ora_sql_txt(sql_text)
    LOOP
      -- put all statement lines in 1 field
      stmt := stmt || sql_text(i);
    END LOOP;

    IF ora_sysevent <> 'TRUNCATE'
    THEN
      INSERT INTO ddl_log (operation, obj_owner, obj_name, obj_type, sql_text, attempt_by, attempt_dt, user_name, user_host)
        SELECT
          ora_sysevent,
          ora_dict_obj_owner,
          ora_dict_obj_name,
          ora_dict_obj_type,
          stmt,
          USER,
          SYSDATE,
          SYS_CONTEXT('USERENV', 'OS_USER'),
          SYS_CONTEXT('USERENV', 'HOST')
        FROM dual d
          LEFT OUTER JOIN ddl_ignore i
            ON ora_sysevent LIKE i.operation
               AND ora_dict_obj_owner LIKE i.obj_owner
               AND ora_dict_obj_name LIKE i.obj_name
               AND ora_dict_obj_type LIKE i.obj_type
        WHERE i.rowid IS NULL;
    END IF;
  END ddl_trigger;
/

I made up this script after some minutes crawling on the web. It's a "intellingent merge" of some script I found, sadly I don't remember the sources.

Update to revision #2.

• avoid TRUNCATE statement logging (they're not useful for the audit purposes)
• Now there is also an ignore table.

The following insert tells to avoid logging for every object with the name like 'TMP_%'

INSERT INTO ddl_ignore (operation, obj_owner, obj_name, obj_type)
VALUES ('%', '%', 'TMP_%', '%');

Update to revision #3.

Added two more information: user name and host of the requester. This is useful when you want to understand who made the change and why!