I want to delete a record from a table and no one should ever be able to recover the deleted data. I have done delete operation but the record is still in the undo segments. And one can do flashback and retrive that row.

  • yes, but it does not tell anything about the record in undo and redo segments – Neal Apr 17 '13 at 17:41
  • 6
    Define "ever". Are we allowed to recover deleted data by restoring from a year-old offsite backup tape? – Mike Sherrill 'Cat Recall' Apr 17 '13 at 17:53
  • 4
    Sounds like you're trying to hide something? – Kermit Apr 17 '13 at 20:04

There is likely a more streamlined set of steps, but here is a start.

  • Delete the data out of any materialized views or home grown replication methods using triggers or the like.
  • Delete the data out of any database audit tables.
  • Change the database to noarchivelog.
  • Create a new undo tablespace.
  • Switch log files until all are archived.
  • Shutdown the database.
  • Turn off and purge any storage or virtual machine level snapshot capability.
  • Delete all trace files that may contain the data.
  • Delete all backups sets including archive log backups that could contain the data.
  • Delete all OS audit logs that could contain the data.
  • Visit any computer that may have the data cached or displayed and destroy it there.
  • Visit any other storage devices the data may have been copied to and purge those.
  • Find any hard copies of the data and destroy those.
  • If someone memorized the data, memory erasure is beyond the scope of this site.
  • Is this serious? I think the OP just wants to get rid of the bytes within the database files and logs. Related: forensically deleting/updating data But of course I'm being presumptious – 孔夫子 Apr 17 '13 at 20:28
  • 1
    I would also recommend to move the table to a new tablespace on a new disk drive and procedurally rewrite all the old disks. Else forensic analysis could probably retrieve the old data. Moving a table will only copy logically existing data. – Vincent Malgrat Apr 18 '13 at 9:58
  • @Vincent Malgrat - Indeed, and do the same procedural rewrite for all storage media that were storing backups, trace files, etc. – Leigh Riffel Apr 18 '13 at 12:41
  • It's incredible how Oracle gets in the way at every step to protect data =) – Vincent Malgrat Apr 18 '13 at 12:50
  • my boss is a weirdo. He doesnot know what he wants. I recommended him to move to a new tablespace, but it is not practical to do each and everytime. So i told him to use Virtual Private Database. Thanks all for your time and answers – Neal May 14 '13 at 10:59

Not the answer you're looking for? Browse other questions tagged or ask your own question.