Enable DDL Auditing in Oracle

 Enable DDL logging in Oracle can be done in several ways.

1. Enable the parameter ENABLE_DDL_LOGGING in Oracle 12c

2. Enable the audit of the database and enable DDL auditing on objects level.

3. Make a DDL trigger on database.

Enable the Parameter ENABLE_DDL_LOGGING

This feature introduced in Oracle 12c, Log generated at particular diag location: %ORACLE_BASE%/diag/rdbms/DBNAME/SID/log/ddl.

It can also make an entry of DDL command in alert log file.

Note: ENABLE_DDL_LOGGING is licensed under Oracle Change Management Pack when set to TRUE. By default parameter value if FALSE.

--Enable the DDL logging
alter system set enable_ddl_logging=TRUE scope=both;

--Check the log generated at location:
%ORACLE_BASE%/diag/rdbms/DBNAME/SID/log/ddl

Enable the audit of the database

Enable audit by setting AUDIT_TRAIL parameter.

alter system set AUDIT_TRAIL='db,extended' scope=spfile;
--restart the database

--Enable Auditing for table
audit table;

--Check the log generated with AUD$ table.
select sqltext from aud$;

Make a DDL trigger for the Database

For making the DDL trigger you need to use the user environment and pick user value and need to create a table to save these values.

Please use the link for system event reference used in trigger:

https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg14evt.htm

-- Create table for auditing the DDL statements
CREATE TABLE DDL_AUDIT
(
OBJECT_OWNER VARCHAR2 (30),
OSUSER VARCHAR2 (255),
SESSION_USER VARCHAR2 (255),
HOST VARCHAR2 (255),
TERMINAL VARCHAR2 (255),
MODULE VARCHAR2 (255),
TYPE VARCHAR2 (30),
Executed_date DATE,
OBJECT_NAME VARCHAR2 (30),
SQL_STATEMENT VARCHAR2 (2000)
);

-- Create trigger for DDL operation on database or on schema as you need.
create or replace trigger audit_ddl_trg after ddl on database
declare
v_sql_text ora_name_list_t;
v_stmt VARCHAR2(2000) := '';
v_n number;
begin
v_n:=ora_sql_txt(v_sql_text);
for i in 1..v_n
loop
v_stmt:=substr(v_stmt||v_sql_text(i),1,2000);
end loop;

insert into DDL_AUDIT(object_owner,osuser,session_user,host,terminal,module,type,executed_date,Object_name,sql_statement)
values(
ora_dict_obj_owner,
sys_context('USERENV','OS_USER') ,
sys_context('USERENV','SESSION_USER') ,
sys_context('USERENV','HOST') ,
sys_context('USERENV','TERMINAL') ,
sys_context('USERENV','MODULE') ,
ora_dict_obj_type,
sysdate,
ora_dict_obj_name,
v_stmt
);
end;
/