How to recover after accidentally deleting table data in oracle instance parsing delete-Oracle-php.cn

How to recover after accidentally deleting table data in oracle instance parsing delete

WBOY

Release： 2022-07-25 20:18:22

forward

3263 people have browsed it

This article brings you relevant knowledge about Oracle. When using oracle data, the data in the table was accidentally deleted and submitted. The following is about the oracle delete error. I hope this information is helpful to everyone on how to restore table data after deleting it.

How to recover after accidentally deleting table data in oracle instance parsing delete

## Recommended tutorial: "

Oracle Video Tutorial"

Restore based on time

This method requires us to roughly know the time when the delete statement is executed.

Query the current system time:

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

Assume that it is 2022-04- 02 16:27:11 minutes, the delete statement

delete from demo was executed;

At this time, the data cannot be queried in the table. . We know the delete execution time, push forward 1 minute (the delete execution time can be before, the smaller the better, this example takes 1 minute as an example), execute the following statement

select * from DEMO as of timestamp to_timestamp(‘2022-04-02 16:26:11&#39;,‘yyyy-mm-dd hh24:mi:ss&#39;);

Copy after login

You can see that although there is no data in the current demo table, you can query the data from the demo table one minute ago.

Recovery 1: At this time, you can export the sql file through the export query result function of the plsql tool, and then re-execute the insert statement in the sql file for data recovery.

Recovery 2: Execute the following sql for data recovery:

flashback table DEMO to timestamp to_timestamp(‘2022-04-02 16:26:11&#39;,‘yyyy-mm-dd hh24:mi:ss&#39;);

Copy after login

If an error is reported ORA-08189: The row movement function is not enabled and the table cannot be flashed back

Execute:

alter table DEMO enable row movement;

Copy after login

After adding the table row movement function, execute the flashback statement for recovery

If an error is reported: ORA-08194: Flashback table operations are not allowed on materialized views; then create a new temporary as described below Table recovery.

Recovery 3 (new temporary table):

Create a new demo1 table and insert the data that needs to be restored

create table DEMO1 as select * from DEMO as of timestamp to_timestamp(‘2022-04-02 16:30:11&#39;,‘yyyy-mm-dd hh24:mi:ss&#39;);

Copy after login

Restore the data of the demo1 table to the demo table

insert into DEMO select * from DEMO1 where not exists (select * from DEMO where DEMO.id=DEMO1.id);

Copy after login

Recovery 4 (restore based on scn):

Query the current scn number

select current_scn from v$database;

Copy after login

Reduce the scn number by a certain amount and execute the following statement until it can Check until we delete the data

select * from DEMO as of scn 166937913;

Copy after login

Use the appropriate scn number to execute the sql statement for data recovery

flashback table DEMO to scn 166937913;

Copy after login