Is there any way to rollback a committed transaction in oracle 11g
I have made a delete from table in db and committed it, now I want to rollback the committed change. Is there any way to do it?
You cannot rollback what has already been commited. What you can do, in this particular situation, as one of the quickest options, is to issue a flashback query against a table you've deleted row(s) from and insert them back. Here is a simple example:
Note: Success of this operation depends on the value(default 900 seconds) of
undo_retention parameter - period of time(can be reduced automatically) during which undo information is retained in undo tablespace.
/* our test table */ create table test_tb( col number ); /* populate test table with some sample data */ insert into test_tb(col) select level from dual connect by level <= 2; select * from test_tb; COL ---------- 1 2 /* delete everything from the test table */ delete from test_tb; select * from test_tb; no rows selected
Insert deleted rows back:
/* flashback query to see contents of the test table as of specific point in time in the past */ select * /* specify past time */ from test_tb as of timestamp timestamp '2013-11-08 10:54:00' COL ---------- 1 2 /* insert deleted rows */ insert into test_tb select * /* specify past time */ from test_tb as of timestamp timestamp '2013-11-08 10:54:00' minus select * from test_tb select * from test_tb; COL ---------- 1 2