Audit DDL Statements from alert.log file from Oracle Database

Audit of DDL

Every time a user attempts anything in the database where audit is enabled the Oracle kernel checks to see if an audit record should be created or updated (in the case or a session record) and generates the record in a table all changes are present in alert.log file of database.

Example-----

In 11g you can record you ddl operations by setting the parameter enable_ddl_logging to true:

ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;

 Example:
I have tried with create and drop command .. you can also do it with alter,rename,comment and truncate and you will get all results in alert*.log file

1. I have connected to debashis schema.
2. Enabling the ddl_looging by using the above command.
3. Create a table debashiskhatai
4. Drop the table
5. Disable the ddl_logging

 

Now if you go and check the alert log file you will find two 4 DDL commands over there

                                                                                                                                         inalert.logfile__________________________________________________________________________

ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;

 create table debashiskhatai(regid number);

drop table debashiskhatai;

ALTER SYSTEM SET enable_ddl_logging=false SCOPE=both;
__________________________________________________________________________________

 Path of alertlog file databse (use the below command and whatever the path in background_dump_dest is ..actually the path of alert*.log file)

SQL>show parameter background

 
 Now when I opened the alert_orcl1.log file I found all those 4 DDL statements in my alert.log file.

 

Done !! yipeee...

Hope you enjoy it ..... :)