CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Varun
Varun

Reputation: 5061

Rollback a committed transaction

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?

Upvotes: 18

Views: 74759

Answers (2)

KR93
KR93

Reputation: 1318

Use this query

SELECT * FROM employee AS OF TIMESTAMP 
   TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')

then insert to the delete table as

INSERT  INTO  employee (SELECT * FROM employee AS OF TIMESTAMP 
   TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS'));

Upvotes: 2

Nick Krasnov
Nick Krasnov

Reputation: 27251

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

Upvotes: 38

Related Questions