One of our customers requested logging of DDL statements happening in their databases. There’s a lot to find on the internet about this. Oracle itself has a few (licensed) options but it’s quite easy to do this yourself with just the help of a trigger, a table… and perhaps a job to clean up the log once in a while.
The customer just wanted to see which user touched what object so I didn’t even start to look in to logging the sql_text but just the simple alter/create/drop statements and the objects that are involved.
First I started with creating a table.
CREATE TABLE DDL_AUDIT
Now the table is done where logging is going to happen we just have to create the trigger that fills it.
CREATE OR REPLACE TRIGGER DDL_AUDIT_TRG
AFTER CREATE OR DROP OR ALTER
INSERT INTO DDL_AUDIT (DATE,
SYS_CONTEXT ('USERENV', 'SESSION_USER'),
SYS_CONTEXT ('USERENV', 'OS_USER'),
SYS_CONTEXT ('USERENV', 'HOST'),
SYS_CONTEXT ('USERENV', 'MODULE'),
And we are done!