Is there any way we can find who dropped a table in a database with out having auditing enabled on the database. Does log miner show
this information? or is there any third party tool which can be used to investigate who dropped the table.
Any information is greatly appeciated.
doesnt alert log document that?
Check out http://www.dbasupport.com/forums/sho...?threadid=5933
To prevent this in the future, look into system level triggers...
Log Miner cannot undo DDL. It can only undo DML. You should consider revoking drop/truncate table from the users.
I encountered same problem not long ago. I think the best I can do now is to create a trigger on drop the database, By doing that you will find out who and when dropped the table and start from here to track down the user do did drop. Then I create another trigger on certain important tables to monitor the DMLs done on those tables. I am still searching for the better alternative.
I have not used it, but the 'data mining' function of RMAN in 8.1.6 and later is supposed to be able to recreate the transactions up to a point in time so the dba can generate corrective scripts, etc. You may be able to use this to identify the user.