Tuesday, June 26, 2012


There are always new features that may not be as big as for example an active duplicate is but sometimes
are very handy.
One of those in Oracle 11G  is the DDL ALERT LOGGING. Which do exactly what it says.
So if you create ,drop of truncate a table it will be shown in the alert file or in de log.xml which
contains more detail information than the alert file. It come in handy when you want to know the
exacte time you drop a table for the flashback
You must the follwoing parameter to TRUE :  default is FALSE

SQL> alter system set enable_ddl_logging=TRUE scope=both;


cd /oracle/hone/diag/rdbms/sfd451t/SFD451T/
view log.xml

<msg time='2012-06-26T12:03:29.632+02:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:3961:4222364190' client_id='' type='NOTIFICATION'
 group='schema_ddl' level='16' host_id='srv5011'
 host_addr='' module='SQL*Plus' pid='14870'>
 <txt>drop table aap

You of course can also it in the alert file

Tue Jun 26 12:03:29 2012
drop table aap
Tue Jun 26 12:04:16 2012
Thats nice and handy

No comments:

Post a Comment