Thursday, June 11, 2015

DDL Logging in Oracle

Starting Oracle 12c, DDL (Data Definition Language) command are logged in a log file which is located under <diag_location>/rdbms/<db_unique_name>/<sid>/log. In my case, it is located under c:\app\oracle\diag\rdbms\db12c\db12c\log directory, and log file name is ddl_<db_name>.log. XML version of this DDL log file is stored under
<diag_location>/rdbms/<db_unique_name>/<sid>/log/ddl. In my case is is located under c:\app\oracle\diag\rdbms\db12c\db12c\log\ddl, and file name is log.xml.
DDL logging is enabled by setting init parameter enable_ddl_logging, which is same in previous oracle releases. Following is an example of enabling and viewing the DDL logging.

Enable DDL logging from root container
SQL> alter system set enable_ddl_logging=true;

System altered.

Executing DDLs (from root container any PDB)
SQL> create table test as select * from user_tables;

Table created.

SQL> alter table test add (time_stamp date);

Table altered.
SQL> create or replace trigger test_trig after insert on test
    declare
    a number;
    begin
    select count(*) into a from test;
    end;
    /

Trigger created.

SQL> drop table test;

Table dropped.
SQL> conn salman/salman@pdb1
Connected.
SQL> create table test as select * from user_tables;

Table created.

SQL> conn salman/salman@pdb1
Connected.
SQL> drop table test;

Table dropped.

In above example, I did a re-login to show that in the DDL log file; it will record timestamp of only first DDL executed in a session.
Following is what you can see in the DDL log file.
Thu Jun 11 12:24:21 2015
diag_adl:create table test as select * from user_tables
diag_adl:alter table test add (time_stamp date)
diag_adl:create or replace trigger test_trig after insert on test

declare

a number;

begin

select count(*) into a from test;

end;
diag_adl:drop table test
Thu Jun 11 12:27:42 2015
diag_adl:create table test as select * from user_tables
Thu Jun 11 12:27:59 2015
diag_adl:drop table test

As mentioned in official document from oracle, following DDLs are recorded in DDL log file

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

No comments:

Popular Posts - All Times