Problem

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!

Solution

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!