A lot of friends have encountered in the operation of the database mistakenly delete some important data, if the database is not backed up and the data is very important how to do to retrieve the data mistakenly deleted? I am here to introduce several methods for recovering important data in a database that are mistakenly deleted (regardless of the full library backup and the use of archive logs)
First KindThe method of data recovery is to use the Flash back method provided by Oracle for data recovery, which is suitable for delete delete (one record) mode:
The first thing you need to know is what time it takes to delete the operation, and if you can't determine the exact point in time, select the time before you can delete the data as accurately as possible. Then use
Select
From table name as of timestamp to_timestamp (' Delete point in time ', ' yyyy-mm-dd hh24:mi:ss ')
Statement to retrieve the deleted data. Use
Insert into table name (selectFrom table name as of timestamp to_timestamp (' Delete point in time ', ' yyyy-mm-dd hh24:mi:ss '));
Statement to insert the recovered data into the original table (note that the primary key is not duplicated).
Another method can recover the data that was mistakenly deleted by flashing back the entire table, but only if the table structure does not change and the user has flash any table permission, the statement is as follows:
ALTER TABLE name enable row movement
Flashback table name to timestamp to_timestamp (delete point in time ', ' frombyte yyyy-mm-dd hh24:mi:ss ')
The second kindThe principle of the data recovery method is that because the Oracle database deletes the deleted information in a virtual "Recycle Bin" instead of emptying the table, the database marking the table in this state is "can be replicated", so the data can still be recovered before the block is reused. This method is used more for drop deletion.
First you need to query the user_table view to find the deleted table:
· Select table_name,dropped from User_tables
· Select Object_name,original_name,type,droptime from User_recyclebin
Note that the table name has been renamed at this point, table_name and object_name are the name of the table in the Recycle Bin, and if the administrator can now clarify that the name of the original table can be
Flashback table name to before drop
Statement for data recovery, if you do not know the name of the original table, you can restore the data directly to the table name in the Recycle Bin, and then
Flashback table name in the Recycle Bin (for example: bin$dsbdfd4rdfdfdfegdfsf==$0) to before drop rename to new table name
The statement can be renamed.
Third KindThe method also leverages the flashback capability of the Oracle database to restore a database to a previous state with the following syntax:
Sql>alter Database Flashback on
Sql>flashback database to SCN Scnno;
Sql>flashback database to timestamp to_timestamp (' Frombyte 2007-2-12 12:00:00 ', ' yyyy-mm-dd hh24:mi:ss ');
Introduction to HereA friend found the problem, Oracle database provides a safeguard mechanism to recover data, but also inevitably occupy a lot of space, using the drop of a table or delete data after the space is not automatically recycled, if you determine the need to delete the data and do not want to take up the unnecessary space how to do? We can use the following two ways:
1, the use of Truncate method for truncation. (But data recovery is not possible)
2. Add purge option at drop: drop table name purge
This option can also delete the table permanently by deleting the recyclebin zone, and the original delete table is dropped in the form EMP cascade constraints
Purge 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 database mistakenly delete Data 3 recovery statements
