Enable DDL logging in Oracle can be done in several ways.
-
Enable the parameter ENABLE_DDL_LOGGING in Oracle 12c
-
Enable the audit of the database and enable DDL auditing on objects level.
-
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;
/