Thanks for the question, Raghava.
Asked: February 13, 2020 - 9:30 am UTC
Answered by: Connor McDonald - Last updated: February 17, 2020 - 2:53 am UTC
Category: PL/SQL - Version: Oracle 18c
Viewed 100+ times
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.
and we 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