Enable DDL Logging
One can monitor or audit all DB changes by viewing log file.
One can set enable_ddl_logging=true to enable DDL logging.
Oracle will start recording all DDL statements in log.xml file. This file has the same format and basic behavior as the alert log, but it only contains the DDL statements.
The DDL log file is created in $ADR_HOME/log/ddl directory; it contains DDL statements that are extracted from alertl log file.
Log file Path
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/log/ddl/log.xml
NOTE:- Oracle license "Oracle Change Management Pack" is require to use this feature.
Parameter enable_ddl_logging is licensed as part of the Change Management Pack.
Following DDL statements are written to the log:
• ALTER/CREATE/DROP/TRUNCATE CLUSTER
• ALTER/CREATE/DROP FUNCTION
• ALTER/CREATE/DROP INDEX
• ALTER/CREATE/DROP OUTLINE
• ALTER/CREATE/DROP PACKAGE
• ALTER/CREATE/DROP PACKAGE BODY
• ALTER/CREATE/DROP PROCEDURE
• ALTER/CREATE/DROP PROFILE
• ALTER/CREATE/DROP SEQUENCE
• CREATE/DROP SYNONYM
• ALTER/CREATE/DROP/RENAME/TRUNCATE TABLE
• ALTER/CREATE/DROP TRIGGER
• ALTER/CREATE/DROP TYPE
• ALTER/CREATE/DROP TYPE BODY
• DROP USER
• ALTER/CREATE/DROP VIEW
Following is the example of DDL logging,
VRTEST$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 11 04:41:39 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
VB_SQL>
VB_SQL> show parameter enable_ddl_logging
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging boolean FALSE
VB_SQL> alter system set enable_ddl_logging=true;
System altered.
VB_SQL>
VB_SQL> show parameter enable_ddl_logging;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging boolean TRUE
VB_SQL> conn VB/VB
Connected.
VB_SQL>
VB_SQL> create table vb_ddltest (id number, name varchar2(50));
Table created.
VB_SQL>
VB_SQL> alter table vb_ddltest add ( address varchar2(100));
Table altered.
VB_SQL>
VB_SQL> insert into vb_ddltest values (1,'CBT','XYZ');
1 row created.
VB_SQL>
VB_SQL> commit;
Commit complete.
VB_SQL> drop table vb_ddltest;
Table dropped.
VB_SQL>
Check the log file for all DDL commands that was run by user.
VRTEST$ pwd
"/opt/oracle/app/orcl/diag/rdbms/VRTEST/VRTEST/log/ddl"
VRTEST$ cat log.xml
msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='OraLinuxNode' host_addr='10.184.150.107'
version='1'>
create table vb_ddltest (id number, name varchar2(50))
msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='OraLinuxNode' host_addr='10.184.150.107'>
alter table vb_ddltest add ( address varchar2(100))
msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='OraLinuxNode' host_addr='10.184.150.107'>
drop table vb_ddltest
VRTEST$
If you want to see clear and readable DDL log file, Just invoke ADRCI utility and issue show log command as shown below,
VRTEST$ adrci
ADRCI: Release 19.0.0.0.0 - Production on Tue Feb 11 04:44:53 2020
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
ADR base = "/opt/oracle/app/orcl"
ADR base = "/mnt/Oracle"
adrci> show log -l ddl
ADR Home = /opt/oracle/app/orcl/diag/rdbms/VRTEST/VRTEST:
*************************************************************************
Output the results to file: /tmp/utsout_19321_13991_1.ado
adrci>
When you issue the command "show log", this will open the log.xml file in edtor (i.e. vi in linux/unix) and show the contents, in following format :
2020-02-11 12:23:22.395000 +05:30
create table vb_ddltest (id number, name varchar2(50))
2020-02-11 12:24:05.301000 +05:30
alter table vb_ddltest add ( address varchar2(100))
2020-02-11 12:25:14.003000 +05:30
drop table vb_ddltest
2020-02-11 12:30:33.272000 +05:30
truncate table wri$_adv_addm_pdbs
Also you can see the debug log using adrci show log command i.e. show log -l debug.
