In today’s article, we will be discussing how to recover deleted data in Oracle after the commit is pressed.
With this method, we will not only recover accidentally deleted data, but also will not lose any subsequent data.
1. A table is created for testing.
|
1 |
create table oardahanli.test as select * from dba_audit_trail; |
2. The data in the table is queried.
|
1 2 3 4 5 |
SQL> select count(*) from oardahanli.test; COUNT(*) ---------- 8469 |
3. The data to be deleted is selected.
|
1 2 3 4 5 |
SQL> select count(*) from oardahanli.test where username not in ('DBSNMP'); COUNT(*) ---------- 362 |
4. Data is deleted and committed.
|
1 2 3 4 5 6 7 |
SQL> delete oardahanli.test where username not in ('DBSNMP'); 362 rows deleted. SQL> commit; Commit complete. |
5. The table is queried by another user and the data is seen to be deleted.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
[root@primary1 ~]# su - oracle [oracle@primary1 ~]$ sqlplus oardahanli/Passw0rd1 SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 23 14:51:26 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> show user USER is "OARDAHANLI" SQL> select count(*) from oardahanli.test; COUNT(*) ---------- 8107 |
6. The data before Commit is fetched as follows.
|
1 2 3 4 5 |
SQL> SELECT COUNT (*) from oardahanli.test as of timestamp to_timestamp ('23-02-2016 14:50:00','DD-MM-YYYY HH24:MI:SS') WHERE username not in ('DBSNMP'); COUNT(*) ---------- 362 |
7. The data is inserted.
|
1 2 3 4 5 6 7 |
SQL> insert into oardahanli.test select * from oardahanli.test as of timestamp to_timestamp ('23-02-2016 14:50:00','DD-MM-YYYY HH24:MI:SS') WHERE username not in ('DBSNMP'); 362 rows created. SQL> commit; Commit complete. |
8. Data is checked.
|
1 2 3 4 5 |
SQL> select count(*) from oardahanli.test; COUNT(*) ---------- 8469 |
