Skip to content

Instantly share code, notes, and snippets.

@greenkey

greenkey/ddl_audit.sql

Last active July 4, 2024 11:55
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save greenkey/424e390b789e2f261f7c to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save greenkey/424e390b789e2f261f7c to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. greenkey revised this gist Feb 24, 2016. 1 changed file with 8 additions and 4 deletions.
    12 changes: 8 additions & 4 deletions ddl_audit.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -6,7 +6,9 @@ CREATE TABLE ddl_log (
    obj_type VARCHAR2(30),
    sql_text CLOB,
    attempt_by VARCHAR2(30),
    attempt_dt DATE
    attempt_dt DATE,
    user_name VARCHAR2(50),
    user_host VARCHAR2(50)
    );

    CREATE TABLE ddl_ignore (
    @@ -32,15 +34,17 @@ ON SCHEMA

    IF ora_sysevent <> 'TRUNCATE'
    THEN
    INSERT INTO ddl_log (operation, obj_owner, obj_name, obj_type, sql_text, attempt_by, attempt_dt)
    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
    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
    @@ -50,4 +54,4 @@ ON SCHEMA
    WHERE i.rowid IS NULL;
    END IF;
    END ddl_trigger;
    /
    /
  2. greenkey revised this gist Feb 12, 2016. 1 changed file with 26 additions and 10 deletions.
    36 changes: 26 additions & 10 deletions ddl_audit.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -9,6 +9,13 @@ CREATE TABLE ddl_log (
    attempt_dt DATE
    );

    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
    @@ -23,15 +30,24 @@ ON SCHEMA
    stmt := stmt || sql_text(i);
    END LOOP;

    INSERT INTO ddl_log (operation, obj_owner, obj_name, obj_type, sql_text, attempt_by, attempt_dt)
    SELECT
    ora_sysevent,
    ora_dict_obj_owner,
    ora_dict_obj_name,
    ora_dict_obj_type,
    stmt,
    USER,
    SYSDATE
    FROM dual;
    IF ora_sysevent <> 'TRUNCATE'
    THEN
    INSERT INTO ddl_log (operation, obj_owner, obj_name, obj_type, sql_text, attempt_by, attempt_dt)
    SELECT
    ora_sysevent,
    ora_dict_obj_owner,
    ora_dict_obj_name,
    ora_dict_obj_type,
    stmt,
    USER,
    SYSDATE
    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;
    /
  3. greenkey created this gist Feb 12, 2016.
    37 changes: 37 additions & 0 deletions ddl_audit.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,37 @@

    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
    );

    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;

    INSERT INTO ddl_log (operation, obj_owner, obj_name, obj_type, sql_text, attempt_by, attempt_dt)
    SELECT
    ora_sysevent,
    ora_dict_obj_owner,
    ora_dict_obj_name,
    ora_dict_obj_type,
    stmt,
    USER,
    SYSDATE
    FROM dual;
    END ddl_trigger;
    /