Shulou(Shulou.com)06/01 Report--

When learning the database, we only consider how to use the database command statement with a learning attitude, and do not think that at work, if we misoperate it, it may lead to irreparable losses. When I really encountered these problems at work, I began to look for answers.

Today, we mainly take the oracle database as an example to introduce the solution to delete the data in the table. (do not consider full library backup and utilization of archive logs)

There are three ways to delete data from a table:

Delete (delete a record)

Drop or truncate delete data from a table

The solution of erroneous deletion of 1.delete

Principle: 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 the deleted data is not overwritten), you can use the flashback method to retrieve the deleted data directly.

The specific steps are:

* determine the time to delete the data (before the data is deleted, but preferably at the point in time to delete the data)

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

* insert the deleted data back into the original table:

Insert into table name (select * from table name as of timestamp to_timestamp ('delete point in time', 'yyyy-mm-dd hh34:mi:ss')); make sure that the primary key is not duplicated.

If the table structure has not changed, you can also directly use flashback of the entire table to recover the data.

The specific steps are:

Table flashback requires that the user must have flash any table permission

Alter table table name enable row movement

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

The solution of erroneous deletion of 2.drop

Principle: because oracle does not directly empty the blocks occupied by the table when deleting the table, oracle puts the information of these deleted tables into a virtual container "Recycle Bin", but only marks the data blocks of the table that can be overwritten, so the blocks can be restored before they are reused.

Specific steps:

* 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 name has been renamed. The field table_name or object_name is the name of the table stored in the Recycle Bin after deletion.

* if you can still remember the table name, you can restore it directly with the following statement:

Flashback table original table name to before drop

If you can't remember, you can also directly use the table name of the Recycle Bin for recovery, and then rename it. Refer to the following statement:

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

In addition to the above basic functions, oracle's flashback feature can also flash back the entire database:

Using the database flashback feature, you can return the database to a certain state in the past. The syntax is as follows:

SQL > alter database flashback on

SQL > flashback database to scn SCNNO

SQL > flashback database to timestamp to_timestamp ('2007-2-12 12-12-12-00-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-5-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12)

Summary:

Oracle provides the above mechanism to ensure safe operation, but it also brings another problem, that is, space occupancy. Due to the operation of the above mechanism, space will not be automatically reclaimed after using drop a table or delete data. For some tables that are determined not to be used, the space should be reclaimed at the same time. There are two ways to delete:

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

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

This option also serves the following purposes:

You can also permanently delete the table by deleting the recyclebin region, and 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

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.