Skip to Main Content
  • Questions
  • Finding when someone dropped an object


Question and Answer

Connor McDonald

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

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.

Raghava K

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

More to Explore


All of the vital components for a secure database are covered in the Security guide.