track DDL changes

Why is my package being invalidated? This is the question I asked myself a few times those days. In order to find out what is happening in my schema, I created this simple DDL trigger which tracks all DDL changes in my schema.


CREATE TABLE AUDIT_DDL (
d date,
OSUSER varchar2(255),
CURRENT_USER varchar2(255),
HOST varchar2(255),
TERMINAL varchar2(255),
owner varchar2(30),
type varchar2(30),
name varchar2(30),
sysevent varchar2(30),
sql_txt varchar2(4000));

create or replace trigger audit_ddl_trg after ddl on schema
declare
sql_text ora_name_list_t;
stmt VARCHAR2(4000) := '';
n number;
begin
if (ora_sysevent='TRUNCATE')
then
null;
else
n:=ora_sql_txt(sql_text);
for i in 1..n
loop
stmt:=substr(stmt||sql_text(i),1,4000);
end loop;
insert into audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent,sql_txt)
values(
sysdate,
sys_context('USERENV','OS_USER') ,
sys_context('USERENV','CURRENT_USER') ,
sys_context('USERENV','HOST') ,
sys_context('USERENV','TERMINAL') ,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_dict_obj_name,
ora_sysevent,
stmt
);
end if;
end;
/

Thanks Yas for your comment, I added the SQL text

16 thoughts on “track DDL changes”

  1. Yas

    DDL triggers are life savers, I have them in place for all our databases. I also get the sql text for specific commands using ora_sql_txt.

  2. Sokrates

    DDL triggers can be very dangerous.
    I usually start them with
    if ora_dict_obj_name like ‘%AUDIT_DDL%’ then return; end if;
    or similar.

    Just tried your version on
    SQL> select * from v$version where rownum=1;

    BANNER
    —————————————————————-
    Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – 64bi

    and observed (the following steps just after create or replace trigger audit_ddl_trg…):

    SQL> alter table audit_ddl add(test int);
    alter table audit_ddl add(test int)
    *
    FEHLER in Zeile 1:
    ORA-30512: cannot modify SOKRATES.AUDIT_DDL more than once in a transaction

    SQL> drop table audit_ddl;
    drop table audit_ddl
    *
    FEHLER in Zeile 1:
    ORA-04045: errors during recompilation/revalidation of SOKRATES.AUDIT_DDL_TRG
    ORA-00600: internal error code, arguments: [16221], [], [], [], [], [], [], []

    SQL> alter table audit_ddl add(test int);
    alter table audit_ddl add(test int)
    *
    FEHLER in Zeile 1:
    ORA-00942: table or view does not exist

    SQL> CREATE TABLE AUDIT_DDL (
    d date,
    OSUSER varchar2(255),
    CURRENT_USER varchar2(255),
    HOST varchar2(255),
    TERMINAL varchar2(255),
    owner varchar2(30),
    type varchar2(30),
    name varchar2(30),
    sysevent varchar2(30),
    sql_txt varchar2(4000));
    2 3 4 5 6 7 8 9 10 11 CREATE TABLE AUDIT_DDL (
    *
    FEHLER in Zeile 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-00001: unique constraint (SYS.I_OBJ2) violated

    QL> desc audit_ddl
    Name Null? Type
    —————————————– ——– —————————-
    D DATE
    OSUSER VARCHAR2(255)
    CURRENT_USER VARCHAR2(255)
    HOST VARCHAR2(255)
    TERMINAL VARCHAR2(255)
    OWNER VARCHAR2(30)
    TYPE VARCHAR2(30)
    NAME VARCHAR2(30)
    SYSEVENT VARCHAR2(30)
    SQL_TXT VARCHAR2(4000)

    Habe I just corrupted my DB ?
    Thank god it’s not a production …

  3. Laurent Schneider Post author

    @Sokrates
    if (ora_dict_obj_name like ‘%AUDIT_DDL%’) then null seems insufficiant. If I have an index on that table and rebuild that index, I am still getting ORA-600 🙁

    Thanks for the info, I need to have a deeper look

  4. Laurent Schneider Post author

    Ok, to improve the stability, I made it a “BEFORE DDL” trigger, and I raise an application error on any change to any AUDIT_DDL dependent object or index…

  5. Sokrates

    @Laurent Schneider
    interesting, I get ORA-30512:

    SQL> alter trigger audit_ddl_trg disable;

    Trigger wurde ge�ndert.

    SQL> create index aaaa on audit_ddl(d);

    Index wurde erstellt.

    SQL> alter trigger audit_ddl_trg enable;

    Trigger wurde ge�ndert.

    SQL> alter index aaaa rebuild;
    alter index aaaa rebuild
    *
    FEHLER in Zeile 1:
    ORA-30512: cannot modify SOKRATES.AUDIT_DDL more than once in a transaction

  6. Sokrates

    @Laurent Schneider
    yes, “BEFORE DDL” seems to be more stable.
    Could you post your code ?
    I can’t reproduce any exception when declaring it as an “BEFORE DDL” and starting with
    “begin
    if (ora_dict_obj_name like ‘%AUDIT_DDL%’) then return ; end if;
    …
    “

  8. carrie b

    Do you all use the ddl trigger because you do not have a locked down
    production schemas?

  12. Dominic Delmolino

    I like doing this in Development so I can track what needs to be assembled for check in to change control for packaging into QA / Prod. I then like it in QA / Prod to make sure what I sent actually got deployed. I prefer auditing over DDL triggers because I think it’s pretty robust out of the box, but as Laurent notes, it requires system privileges. I did a whole presentation on database release management at Hotsos 2008 — unfortunately it’s not a very sexy topic 🙂

  13. Stuart

    Hi Laurent,

    On our primary database, we have over 30 schema’s, so I tested with ‘on database’ rather than ‘on schema’, as I don’t want to create the same trigger for each schema.

    Your script works brilliantly but it doesn’t like any user trying to change their password using the ‘password’ command (see below). Running an ‘alter user xxx identified by yyy;’ statement works fine.

    Changing password for SURE0061
    Old password:
    New password:
    Retype new password:
    ERROR:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at line 11

    I couldn’t see a problem with your code. Maybe there is some kind of recursive call occurring within the ‘password’ command which causes this?

    What are your thoughts on auditing ddl at the database (as opposed to schema)? It seems that I just have to be prudent to disable the trigger around:
    – upgrades
    – patches
    – granting access on the audit table (i.e. it generates an ORA-600)

  15. Stuart

    Hi Laurent,

    ALTER USER works fine… that is not the problem.

    The issue is that the ‘password’ command returns null, so the following line gives an error:
    for i in 1..n

    So,… just add in something like the following to fix:
    if ( n is null ) then
    v_stmnt := ‘null statement (password change?)’;
    else
    for i in 1..n loop
    …
    …
    end loop;
    end if;

  16. Pingback: Laurent Schneider » track ddl change (part 2)

Comments are closed.