Oracle recovery method for accidental deletion of data

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.