How To Trace DDL Sentences

Last day someone drop a import table in a database. The app get crazy and nothing work fine. The solution was recreate the table but the problem was who deleted it?.

In this case there weren’t any audit. You can activate audit (it is powerfull) but in this case I want talk about trace only DDL SQL (Data Definition Language) by using the enable_ddl_logging parameter.

 

How to check;

BBDD*ANR> show parameter enable_ddl_logging

NAME                                 TYPE                             VALUE
____________________________________ ________________________________ _________________________
enable_ddl_logging                   boolean                          FALSE

How to enable;

ALTER SYSTEM SET ENABLE_DDL_LOGGING=TRUE;

Hot to disable;

ALTER SYSTEM SET ENABLE_DDL_LOGGING=FALSE;

The audit will be write to (version 12c) a file with format show below into the diagnostic_dest;

ddl_${ORACLE_SID}.log

 

HTH – Antonio NAVARRO

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s