Audit DDL Changes
Here is a way of keeping track of all the changes made to a schema by DDL statements.
DROP TRIGGER DDL_CHANGE_TRACKER
/
DROP TABLE dll_audit_log
/
CREATE TABLE ddl_change_tracker (
stamp DATE,
username VARCHAR2(30),
osuser VARCHAR2(30),
machine VARCHAR2(30),
terminal VARCHAR2(30),
operation VARCHAR2(30),
objtype VARCHAR2(30),
objname VARCHAR2(30))
/
CREATE OR REPLACE TRIGGER audit_ddl_changes
AFTER create OR drop OR alter
ON KIRTAN.SCHEMA
BEGIN
INSERT INTO DDL_CHANGE_TRACKER VALUES
(SYSDATE,
SYS_CONTEXT('USERENV', 'SESSION_USER'),
SYS_CONTEXT('USERENV', 'OS_USER'),
SYS_CONTEXT('USERENV', 'HOST'),
SYS_CONTEXT('USERENV', 'TERMINAL'),
ORA_SYSEVENT,
ORA_DICT_OBJ_TYPE,
ORA_DICT_OBJ_NAME
);
END;
/
show errors
CREATE TABLE my_test (a number)
/
DROP TABLE my_test
/
set pages 50000
SELECT * FROM DDL_CHANGE_TRACKER
/
Comments feed
Hello, what if i wanted to included the SQL text that was issued. How would I included that?