Three methods to quickly recover accidentally deleted Oracle database

This article brings you relevant knowledge about Oracle, which mainly introduces how to quickly delete data in the Oracle database without considering the full database backup and archive log utilization. Let’s take a look at the related issues of recovering data. I hope it will be helpful to everyone.

Recommended tutorial: "Oracle Video Tutorial"

Deleting data in the oracle database by mistake without considering all How to quickly restore data when using database backup and archive logs?

The following are 3 methods.

1. Use the flashback method provided by Oracle for data recovery, which is suitable for delete deletion method

First obtain the time point of deleted data:

select * from v$sql where sql_text like '%table_name%' ;
1

According to the sql_text content in the result, find the delete time point corresponding to the delete execution statement, and execute the following statement to query the deleted data.

select * from table_name as of timestamp to_timestamp(‘删除时间点’,‘yyyy-mm-dd hh24:mi:ss’)
where (删除时的条件)
12

After checking that the data is correct, perform the following operations to insert the data back into the original table. Note that the primary key is not repeated

insert into table_name
select * from from table_name as of timestamp to_timestamp(‘删除时间点’,‘yyyy-mm-dd hh24:mi:ss’)
where (删除时的条件)
123

2. Use the oracle virtual recycle bin function

The principle is because the oracle database will delete the information when deleting the table It is stored in a virtual recycle bin instead of being emptied directly. In this state, the database marks the table as can be copied, so the block has not been reused. Data can still be recovered before . This method is mostly used for drop deletion.

First 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;
12

Note at this time, object_name and original_name It is the name of the table stored in the recycle bin and the name of the originally deleted table. If the table name has not been renamed, you can restore it through the following statement:

flashback table original_name to before drop;
1

If you do not know the source table name, or you need to rename the new If the data is stored in the table name of the recycle bin, it can be restored through object_name in the recycle bin. The command is as follows:

flashback table object_name to before drop new_table_name;
1

3. Use the flashback function of the oracle database to restore the database to A certain state in the past

Note that this is whole database recovery, the specific syntax is as follows:

SQL>alter database flashback on
SQL>flashback database to scn SCNNO;
SQL>flashback database to timestamp to_timestamp(‘frombyte 2021-09-02 23:59:59’,‘yyyy-mm-dd hh24:mi:ss’);
123

4. Completely delete data

If we determine the data that needs to be deleted and do not want to occupy space unnecessarily, we can use the following three methods:

1. Truncate using the truncate method. (Data recovery is not possible)
2. Add the purge option when dropping: drop table table_name purge
3. Make it permanent by deleting the recyclebin area Delete the table,drop table table_name cascade constraints purge table table_name;

##5. About emptying the recycle bin

1. Delete the current user’s recycle bin

   purge recyclebin;

2. Delete all users’ data in the recycle bin

   purge dba_recyclebin;

## Recommended tutorial: "

"

The above is the detailed content of Three methods to quickly recover accidentally deleted Oracle database. For more information, please follow other related articles on the PHP Chinese website!