I wrote about tracking ddl changes with a trigger there : track ddl changes
Another option is to use auditing.
A new and cool alternative is to use enable_ddl_logging (11gR2). This will track all ddl’s in the alert log
ALTER SYSTEM SET enable_ddl_logging=TRUE
Then later you issue
create table t(x number)
and you see in the alertLSC01.log
Tue Apr 05 14:43:32 2011 create table t(x number)
Wait, that’s not really verbose !?
Remember the alert log is just there for backward compatibility, it is time you start looking in the xml file 🙂
<msg time='2011-04-05T14:43:42.210+02:00' org_id='oracle' comp_id='rdbms' msg_id='opiexe:3937:4222333111' client_id='' type='NOTIFICATION' group='schema_ddl' level='16' host_id='srv01' host_addr='192.168.0.141' module='TOAD Beta 22.214.171.124' pid='2777799'> <txt>create table t(x number) </txt> </msg>
There is not really much more there but the module, which indeed reveals someone is using TOAD to access my database !