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.

@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;
…
“

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 🙂

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.

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)