Tracking All DDL Changes: Logging Schema Modifications in Oracle Database

Sometimes at work, two developers end up editing the same package almost at the same time. One of them had been working on it since early afternoon, compiling along the way. The other opened the package body in the morning, made some changes at the end of the day, and compiled it later that evening. There was no CI/CD process in place, and — of course — one of them lost their changes.
Guess who lost all their work? Yep — me.

To avoid going through that again, we decided to adopt a simple approach: we created a trigger that audits all changes made in the database schema and stores them in a table. This gives us much better control and autonomy without having to rely on the DBA to restore previous versions. Another advantage is that it makes it easy to see who made changes to each object, providing a straightforward way to track every modification in the code or schema structure.

The method described below was tested on Oracle Database Standard Edition 19c, and the commands must be executed by the OWNER of the SCHEMA you want to audit.

  CREATE TABLE DDL_LOG
       ("DDL_LOG_ID" NUMBER, 
        "DDL_OPERATION" VARCHAR2(30 BYTE), 
        "OBJECT_TYPE" VARCHAR2(30 BYTE), 
        "OBJECT_NAME" VARCHAR2(128 BYTE), 
        "APP_USER_NAME" VARCHAR2(30 BYTE), 
        "EVENT_TIMESTAMP" TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP, 
        "DDL_SQL_TEXT" CLOB, 
        "DDL_SQL_TEXT_2" CLOB,
        "CLIENT_IP_ADDRESS" VARCHAR2(150 BYTE), 
        "OBJECT_OWNER" VARCHAR2(30 BYTE), 
        "DB_USER_NAME" VARCHAR2(30 BYTE), 
        "OS_USER_NAME" VARCHAR2(50 BYTE), 
        "CLIENT_HOST_NAME" VARCHAR2(50 BYTE), 
        "NOTES" VARCHAR2(4000 BYTE), 
         CONSTRAINT "DDL_LOG_PK" PRIMARY KEY ("DDL_LOG_ID") ) ;
/

CREATE SEQUENCE SEQ_DDL_LOG_ID
   START WITH 1
   INCREMENT BY 1
   NOCACHE
   NOCYCLE;
/

--If this snippet doesn’t run when inserting through the browser, you can use EXECUTE IMMEDIATE, since there’s no way to use DEFINE OFF in APEX.
  CREATE OR REPLACE EDITIONABLE TRIGGER TRG_DDL_LOG_SEQ
BEFORE INSERT ON DDL_LOG FOR EACH ROW 
BEGIN
  BEGIN
    IF INSERTING AND :NEW.DDL_LOG_ID IS NULL THEN
      SELECT SEQ_DDL_LOG_ID.NEXTVAL INTO :NEW.DDL_LOG_ID FROM SYS.DUAL;
    END IF;
  END COLUMN_SEQUENCES;
END;
/
ALTER TRIGGER TRG_DDL_LOG_SEQ ENABLE;
/

Now you must create the trigger that will perform the auditing. Remember to use the OWNER of the SCHEMA you intend to audit.

create or replace TRIGGER ddl_audit_trigger AFTER
CREATE OR ALTER OR DROP ON SCHEMA

DECLARE
  v_sql_text  CLOB;
  v_text_item ora_name_list_t;

BEGIN

-- Handles how the DDL will be stored in the CLOB;
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', TRUE);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', TRUE);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', FALSE);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'STORAGE', FALSE);      
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'EMIT_SCHEMA', FALSE);



-- Captures the executed SQL (actual DDL)  
  v_sql_text := NULL;
  FOR i IN 1 .. ora_sql_txt(v_text_item) LOOP
    v_sql_text := v_sql_text || v_text_item(i);
  END LOOP;


-- Ignores changes made to the auditing-related objects themselves  
        IF ora_dict_obj_name IN ('DDL_LOG', 'DDL_AUDIT_TRIGGER') THEN 
            NULL;
            RETURN;

        ELSE

            INSERT INTO ddl_log (ddl_operation,                     
                                 object_type,
                                 object_name,
                                 app_user_name,          
                                 ddl_sql_text,
                                 ddl_sql_text_2,
                                 client_ip_address,
                                 db_user_name,
                                 object_owner,              
                                 os_user_name,
                                 client_host_name
                                )    
            VALUES              (ora_sysevent,
                                 REPLACE(ora_dict_obj_type, ' ', '_'),
                                 ora_dict_obj_name,
                                 NVL(V('APP_USER') ,SYS_CONTEXT('USERENV','CURRENT_USER')),
                                 dbms_metadata.get_ddl(REPLACE(UPPER(ora_dict_obj_type), ' ', '_'), UPPER(ora_dict_obj_name)),
                                 v_sql_text,
                                SYS_CONTEXT('USERENV', 'IP_ADDRESS'),   
                                ora_login_user,
                                ora_dict_obj_owner, 
                                SYS_CONTEXT('USERENV', 'OS_USER'),  
                                SYS_CONTEXT('USERENV', 'HOST')             
            );
        END IF;
EXCEPTION WHEN OTHERS THEN
      NULL;  -- Don’t raise an error, just skip. Consider creating an error logging routine.  
      RETURN;
END;
/

From now on, every time a table or other object is modified, the command will be saved in the DDL_LOG table, as shown below.

There are other ways to implement this, but this was the most practical solution I found without relying on the DBA. If you have any suggestions for improvement, feel free to share them on comments.