Thanks for the question, Raghava.
Asked: February 13, 2020 - 9:30 am UTC
Last updated: February 17, 2020 - 2:53 am UTC
Version: Oracle 18c
Viewed 1000+ times
You Asked
Hi Team,
I have a DB, where a table is dropped from the schema accidentally. We are trying to find whether it got dropped due to manual execution of DROP query and by whom?
Is there any way that we can find that the DROP query which is executed previously from the history?
I have Log Miner and Audit, both are disabled on my database.
Regards
Raghava K
and Connor said...
Firstly - check the recyclebin. You might be able to get details from there.
If not, then you can try a flashback query on dba_tables - keep going back until the table definition re-appears.
Other than that, you'd probably to start looking at restoring older backups to see when it was dropped and determine its object_id, and then possibly starting digging around into archive logs with Log Miner to look for deletes from SYS.OBJ$ for that object number.
But of course, the bottom line is ... either
a) if people shouldn't be dropping objects, then don't give them the privilege to do so.
b) if you want to know if people do it...then make sure you enable auditing
Is this answer out of date? If it is, please let us know via a Comment