Oracle table data recovery

1. Create a table

-- Create Table
Create Table Darcy
(
ID number,
Info nvarchar2 (32)
)
Tablespace data_sgpm
Pctfree 10
Initrans 1
Maxtrans 255
Storage
(
Initial 64 K
Minextents 1
Maxextents Unlimited
);

2. Insert data

Insert into "SGPM". "Darcy" ("ID", "info") values ('1', 'aaa ');

Insert into "SGPM". "Darcy" ("ID", "info") values ('2', 'bbb ');

Insert into "SGPM". "Darcy" ("ID", "info") values ('3', 'ccc ');

3. delete data

SQL> select * from Darcy;

ID Info
------------------------------------------------------------------------------------------
1 aaa
2 bbb
3 ccc

SQL> Delete from Darcy where id = 1;

1 row deleted

SQL> commit;

Commit complete

SQL> select * from Darcy;

ID Info
------------------------------------------------------------------------------------------
2 bbb
3 ccc

4. Restore data

Method 1:

Query the latest system change number

SQL> select dbms_flashback.get_system_change_number from dual;

Get_system_change_number
------------------------
18144344

View the table record after this change

SQL> select * from Darcy as of SCN 18144344;

ID Info
------------------------------------------------------------------------------------------
2 bbb
3 ccc

This indicates that this is the table record after the data is deleted. We only need to find an SCN, that is, the SCN before the table record is deleted,

The record returned to this SCN.

SQL> select * from Darcy as of SCN 18144252;

ID Info
------------------------------------------------------------------------------------------
1 aaa
2 bbb
3 ccc

Then execute the insert statement directly.

Method 2:

SQL> select * From flashback_transaction_query where table_name = 'darcy ';

Xid start_scn start_timestamp commit_scn commit_timestamp logon_user undo_change # operation table_name table_owner row_id undo_ SQL
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
07001500ab280000 18144149 2010-9-9 10: 14: 18144281 2010-9-9 SGPM 1 Delete Darcy SGPM aaayqwaacaaakk2aaa insert into "SGPM ". "Darcy" ("ID", "info") values ('1', 'aaa ');
Listen 18144244 10: 16: 18144252 SGPM 1 insert Darcy SGPM aaayqwaacaaakk2aac Delete from "SGPM". "Darcy" where rowid = 'aaayqwaacaaakk2aac ';
080018005f370000 18144244 2010-9-9 10: 16: 18144252 SGPM 2 insert Darcy SGPM into Delete from "SGPM". "Darcy" where rowid = 'aaayqwaacaaakk2aab ';
Listen 18144244 10: 16: 18144252 SGPM 3 insert Darcy SGPM aaayqwaacaaakk2aaa Delete from "SGPM". "Darcy" where rowid = 'aaayqwaacaaakk2aaa ';

Run undo_ SQL, that is, insert into "SGPM". "Darcy" ("ID", "info") values ('1', 'aaa'); to restore data.

Or run:

SQL> flashback table Darcy to timestamp to_timestamp ('2017-9-9 ', 'yyyy-mm-dd hh24: MI: ss ');

Flashback table Darcy to timestamp to_timestamp ('2017-9-9 ', 'yyyy-mm-dd hh24: MI: ss ')

ORA-08189: Unable to flash back the table because row movement is not enabled

SQL> ALTER TABLE Darcy enable row movement;

Table altered

SQL> flashback table Darcy to timestamp to_timestamp (, 'yyyy-mm-dd hh24: MI: ss ');

Done

SQL> select * from Darcy;

ID Info
------------------------------------------------------------------------------------------
1 aaa
2 bbb
3 ccc

5. Restore after drop table

SQL> drop table Darcy;

Table dropped

SQL> select * from Darcy;

Select * from Darcy

ORA-00942: Table or view does not exist

SQL> select * From recyclebin;

Object_name original_name operation type ts_name createtime droptime dropscn partition_name can_undrop can_purge related base_object purge_object Space
Certificate certificate ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bin $ crbffp0nrtwzetramvbd + A ===$ 0 Darcy drop table data_sgpm

SQL> select * From user_recyclebin;

Object_name original_name operation type ts_name createtime droptime dropscn partition_name can_undrop can_purge related base_object purge_object Space
Certificate certificate ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bin $ crbffp0nrtwzetramvbd + A ==$ 0 Darcy drop table data_sgpm 2010-09-09: 10: 15: 50 2010-09-09: 11: 12: 04 18154031 Yes 99376 99376 99376 8

SQL> flashback table Darcy to before drop;

Done

SQL> select * from Darcy;

ID Info
------------------------------------------------------------------------------------------
1 aaa
2 bbb
3 ccc