Home » RDBMS Server » Performance Tuning » logging DDL statements, etc?
logging DDL statements, etc? [message #64957] Thu, 18 March 2004 00:23 Go to next message
Akira Kenshin
Messages: 1
Registered: March 2004
Junior Member
i just came up with this hypothetical situation (it may, or may not happen in real life). and if it does, i would like how to solve it:

imagine that a table has been deleted (say maliciously by a certain user)...

is there a way (an sql srcipt or something) that i can use to find out when, what DDL statement was used, probably user name, ip address (most imporatnt!), etc.?

oracle just has too many dictionary/views, that im at a lost on how to make sense of the information to get what i need.

thanks in avanced.
Re: logging DDL statements, etc? [message #64961 is a reply to message #64957] Thu, 18 March 2004 04:18 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You can enable auditing, which pretty gives u the information about 'who did wat'?
but why dont u just prevent anyone 'doing any harm'?
something like this
mag@mutation_mutation > truncate table emp;
truncate table emp
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20111: truncate is not allowed .... table is protected by DBA
ORA-06512: at line 7

Re: logging DDL statements, etc? [message #64962 is a reply to message #64957] Thu, 18 March 2004 04:21 Go to previous messageGo to next message
Frank Naude
Messages: 4565
Registered: April 1998
Senior Member

You can write a database or schema level trigger to fire ON DROP and log all the required info (with IP Address) to a separate table.

The scripts section of this site contains some database level trigger examples.

Best regards.

Re: logging DDL statements, etc? [message #64967 is a reply to message #64957] Thu, 18 March 2004 15:36 Go to previous message
Messages: 116
Registered: September 1999
Senior Member
I am assuming that your database is 8i or above and you can do 2 things to get this information.

1) create a logon trigger for the whole database and log the username,machinename , last sql statement issued, logon and logoff time into a table. I am not sure if you can log the IP address.

2) Use logminer to find out the username, DDL statement issued and time issued. With logminer you can undo the changes too.

Once you find when a statement was issued (for e.g delete from x ) from the logminer, you can cross check with the audit table in step 1 and see who logged in at that time and get the machine name.
Previous Topic: performance tuning novice
Next Topic: Memmory
Goto Forum:

Current Time: Thu Oct 01 12:30:46 CDT 2020