Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to restore the statement of mistakenly deleted data in Oracle database, which is very detailed and has a certain reference value. Interested friends must finish reading it!

The first data recovery method is to use the flashback method provided by oracle for data recovery, which is suitable for delete deletion (a record):

First of all, you need to know when the deletion operation was carried out, and if you are not sure of the specific time point, choose the time before deleting the data as accurately as possible. And then use

Select * from table name as of timestamp to_timestamp ('delete point in time', 'yyyy-mm-dd hh34:mi:ss')

Statement to retrieve deleted data. Utilization

Insert into table name (select * from table name as of timestamp to_timestamp ('delete point in time', 'yyyy-mm-dd hh34:mi:ss'))

Statement can insert the recovered data into the original table (make sure that the primary key is not duplicated).

Another method can recover mistakenly deleted data by flashing back the entire table, but only if the table structure has not changed and the user has flash any table permission. The statement is as follows:

Alter table table name enable row movement

Flashback table table name to timestamp to_timestamp (delete point in time', 'frombyte yyyy-mm-dd hh34:mi:ss')

The principle of the second data recovery method is that when the oracle database deletes the table, it stores the deleted information in a virtual "recycle bin" instead of emptying it directly, and in this state, the database marked with the table is "rewritable", so the data can still be recovered before the block is reused. This method is mostly used 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 time. Table_name and object_name are the names of the stored tables in the Recycle Bin. If the administrator can specify the name of the original table at this time, you can use the

Flashback table original table name to before drop

Statement for data recovery, if you do not know the original table name, you can recover the data directly according to the table name in the Recycle Bin, and then use the

Flashback table "Table name in the Recycle Bin (e.g. Bin$DSbdfd4rdfdfdfegdfsf==$0)" to before drop rename to new table name

Statement can be renamed.

The third method also uses the flashback feature of the oracle database to restore the database to a previous state. The syntax is as follows:

SQL > alter database flashback on

SQL > flashback database to scn SCNNO

SQL > flashback database to timestamp to_timestamp ('frombyte 2007-2-12 12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12)

Some friends have found a problem here. Oracle database provides a guarantee mechanism to recover data, but it inevitably takes up a lot of space. Space can not be recycled automatically after using drop table or delete data. How to operate if you determine the data that needs to be deleted and do not want to occupy space unnecessarily? We can use the following two ways:

1. Truncate by truncate. (but data recovery cannot be carried out)

2. Add purge option to drop: drop table table name purge

This option can also permanently delete the table by deleting the recyclebin region, the original delete table drop table emp cascade constraints

Purge table emp

Delete the current user's Recycle Bin:

Purge recyclebin

Delete all users' data in the Recycle Bin:

Purge dba_recyclebin

The above is all the contents of the article "how to restore the statement of mistakenly deleted data in Oracle database". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.