DDL Logging in Oracle 12C

Oracle 12C logs the DDL statements if enabled. This can be done by enabling one parameter at session/instance level.

SQL> show parameter ddl_logging

NAME TYPE VALUE
———————————— ———– ——————————
enable_ddl_logging boolean FALSE

By default DDL logging is disabled.

SQL> alter session set enable_ddl_logging=TRUE;

Session altered.

Now, Lets run few DDL commands.

SQL> desc test_tbl;
Name Null? Type
—————————————– ——– —————————-
NAME VARCHAR2(100 CHAR)
AGE NUMBER(38)
EMP_CODE (INVISIBLE) NUMBER(38)

SQL> alter table test_tbl add dept_id number;

Table altered.

SQL> truncate table test_tbl;

Table truncated.

SQL> drop table test_tbl;

Table dropped.

SQL> alter session set enable_ddl_logging=FALSE;

Session altered.

Lets check the contents of DDL log, which is created in text as well as xml formats.

[oracle@ora-c7 log]$ pwd
/u01/app/oracle/diag/rdbms/orcl/orcl/log

[oracle@ora-c7 log]$ cat ddl_orcl.log
Thu Oct 20 21:52:32 2016
diag_adl:alter table test_tbl add dept_id number
diag_adl:truncate table test_tbl
diag_adl:drop table test_tbl
[oracle@ora-c7 log]$

This could be really useful if you want to monitor the DDL operations in your oracle database.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s