Oracle Recovery method for accidental deletion of dataLearning the database, we are just learning attitude, consider how to use the database command statements, did not think about the work, if the wrong operation, can lead to irreparable loss. When I really met these questions at work, I started looking for answers. today, we mainly take Oracle database as an example to introduce the solution to data deletion in the table. (regardless of full-library backup and utilization of archive logs)There are three ways to delete data in a table:Delete (delete a record)Drop or truncate delete data from a table1.delete Solution for accidental deletionprinciple:using the Flashback method provided by Oracle, if you have not done a lot of operations after deleting the data (as long as the block of deleted data is not overwritten), you can retrieve the deleted data directly using the Flashback mode .the specific steps are:* Determine when the data is deleted (the time before the data is deleted, but preferably the point in time when the data is deleted)* Use the following statement to find the deleted data: SELECT * from table name as of timestamp to_timestamp (' delete point ', ' Yyyy-mm-dd hh24:mi:ss ')* Reinsert the deleted data into the original table:insert INTO table name (SELECT * from table name as of timestamp to_timestamp (' Delete point in time ', ' yyyy-mm-dd hh24:mi:ss ')); Note to ensure that the primary key is not duplicated. If the table structure does not change, you can also restore the data directly using the way the entire table was flashed back. the specific steps are:table flashback requires that the user must have flash any table permission--turn on the line movement functionALTER TABLE name enable row movement--Recover table DataFlashback table name to timestamp to_timestamp (delete time point ', ' Yyyy-mm-dd hh24:mi:ss ')--Turn off the line movement function ( don't forget ) ALTER TABLE name disable row movement2.drop Solution for accidental deletionprinciple: Since Oracle does not directly empty the blocks of the table when it deletes the table, Oracle places the information for these deleted tables in a virtual container "Recycle Bin", but only makes the data blocks of the table writable, so it can be resumed before the blocks are reused. Specific steps:* Query the Recycle Bin or query the user_table view to find the table that has been deleted:Select table_name,dropped from User_tablesSelect Object_name,original_name,type,droptime from User_recyclebinIn the above information, the table name has been renamed, the field table_name or object_name is deleted in the Recycle Bin in the storage table names* If you can remember the table name, you can restore it directly using the following statement:Flashback table name to before dropIf you do not remember, you can also directly use the Recycle Bin table name for recovery, and then rename, reference the following statement:Flashback table name in the Recycle Bin (for example: bin$dsbdfd4rdfdfdfegdfsf==$0) to before drop rename to new table nameIn addition to the basic features above, Oracle's flashback functionality can be flashed back to the entire database:using the database Flashback feature, you can return a database to a state in the past with the following syntax:Sql>alter Database Flashback onsql>flashback database to SCN Scnno;sql>flashback Database to timestamp to_timestamp (' 2007-2-12 12:00:00 ', ' yyyy-mm-dd hh24:mi:ss ');Summary:Oracle provides these mechanisms to ensure safe operation, but at the same time comes another problem, is the space occupancy, due to the operation of the above mechanism, using the drop of a table or delete data, the space will not be self-recycle, for some of the tables that are determined not to be used, when removing the space at the same time, there are 2 ways to do this:1, the use of Truncate method for truncation. (But data recovery is not possible)2. Add purge option at drop: drop table name purgeThis option also has the following uses:You can also delete the table permanently by deleting the recyclebin zone, and the original delete table is dropped in the form EMP cascade constraintspurge table emp;to delete the current user's Recycle Bin:purge RecycleBin;Delete data for all users in the Recycle Bin:Purge Dba_recyclebin
Oracle recovery method for accidental deletion of data
