Skip to Main Content
  • Questions

  • Finding when someone dropped an object

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

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 10K+ times! This question is

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



We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

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