Monitor Code Changes
If your Oracle database contains sensitive information you might want to know who creates, drops or modified an object in it. You could set up a process where only one or two persons are allowed to do these kind of changes but at some point this could get rather annoying.
But there's a little code which could help you!
Recipe #1 - Oracle Trigger to Monitor Code Changes
First, we need a table where we write all the changes, execute the following query to do that:
CREATE TABLE ORACLE_DDL_LOG ( SYS_EVENT VARCHAR2(255), OBJECT_OWNER VARCHAR2(255), OBJECT_NAME VARCHAR2(255), SQL_TEXT CLOB, USER_NAME VARCHAR2(255), ACTION_DATE DATE )
Once you've created the table, you just have to create a trigger, run the following code to create the trigger:
CREATE OR REPLACE TRIGGER oracle_ddl_trigger BEFORE CREATE OR ALTER OR DROP ON SCHEMA DECLARE sql_text ora_name_list_t; i PLS_INTEGER; sql_text_ CLOB; BEGIN i := sql_txt(sql_text); FOR l IN 1..i LOOP sql_text_ := sql_text_ || sql_text(l); END LOOP; INSERT INTO oracle_ddl_log SELECT ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, sql_text_, SYS_CONTEXT('USERENV','OS_USER'), SYSDATE FROM DUAL; END oracle_ddl_trigger;
Now that you've created the trigger, you'll get an entry in ORACLE_DDL_LOG when you create a new object. Try to run this test code to see if an entry appears in ORACLE_DDL_LOG:
CREATE OR REPLACE VIEW test_view AS SELECT 1 a FROM DUAL
If everything works, you created a nice and simple monitoring system!