How can I recover the accidental deletion of Oracle Database Data ?, Delete oracle by mistake

How can I recover the accidental deletion of Oracle Database Data ?, Delete oracle by mistake

When studying the database, we only consider how to use database command statements with a learning attitude. We did not think about how to use database command statements. If a misoperation occurs at work, it may lead to irreparable losses.

Today, we will take the oracle database as an example to describe how to delete table data. (Full database backup and utilization of archive logs are not considered)

There are three methods to delete table data:

Delete (delete a record)

Drop or truncate Delete table data

1. Solution to accidental deletion of delete

Principle:

With the flash back method provided by oracle, if a large number of operations are not performed after the data is deleted (as long as the block of the deleted data is not overwritten ), you can use the flash back method to directly retrieve the deleted data.

The procedure is as follows:

Determine the time when the data is deleted (the time before the data is deleted, but it is best to delete the data at a time point)

Use the following statement to find the deleted data: select from table name as of timestamp to_timestamp ('delete time point', 'yyyy-mm-dd hh24: mi: ss ')

Insert the deleted data into the original table again:

Insert into Table Name (select from table name as of timestamp to_timestamp ('delete time point', 'yyyy-mm-dd hh24: mi: ss ')); make sure that the primary key is unique.

If the table structure does not change, you can use the flash back to restore the data of the entire table.

The procedure is as follows:

You must have the flash any table permission to flash the table.

Alter table name enable row movement

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

2. Solution to drop accidental deletion

Principle:

Since oracle does not directly clear the blocks occupied by the table When deleting the table, oracle places the deleted table information in a virtual container "recycle bin, the data block of the table can be overwritten, so the block can be restored before it is reused.

Procedure:

-- Query the "recycle bin" or 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

In the above information, the table names are all renamed, And the table_name or object_name field is the name of the table that is stored in the recycle bin after deletion.

-- If you can remember the table name, you can use the following statement to directly restore the table:

Flashback table original table name to before drop

If you cannot remember it, you can use the table name of the recycle bin to restore it and rename it. See the following statement:

Flashback table "table name in the recycle Bin (for example, Bin $ dsbdfd4rdfdfegdfsf = $0)" to before drop rename to new table name

In addition to the above basic functions, the flash back function of oracle can also flash back to the entire database:

The database flashback function enables the database to return 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 ('2017-2-12 12:00:00 ', 'yyyy-mm-dd hh24: mi: ss ');

Summary:

Oracle provides the above mechanism to ensure security operations, but it also generates another problem, that is, space occupation. Due to the operation of the above mechanism, after dropping a table or deleting data, the space is not automatically recycled. For tables that are not determined to be used, the space must be recycled at the same time during deletion. You can use either of the following methods:

1. truncate is used for truncation. (But data cannot be restored)

2. Add the purge option when dropping: drop table name purge

This option has the following purposes:

You can also permanently delete a table by deleting the recyclebin area. The original drop table emp cascade constraints

Purge table emp;

Delete the recycle bin of the current user:

Purge recyclebin;

Delete the data of all users in the recycle bin:

Purge dba_recyclebin