Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 867
Varun Avatar asked Nov 08 '13 06:11

Varun


People also ask

Can you rollback a transaction after commit in SQL Server?

Once SQL Server commits a transaction, you cannot run the ROLLBACK statement.

How do I rollback a transaction?

You can see that the syntax of the rollback SQL statement is simple. You just have to write the statement ROLLBACK TRANSACTION, followed by the name of the transaction that you want to rollback.

How do I rollback a commit in SQL?

The ROLLBACK command is used to undo a group of transactions. The syntax for rolling back to a SAVEPOINT is as shown below. ROLLBACK TO SAVEPOINT_NAME; Following is an example where you plan to delete the three different records from the CUSTOMERS table.


1 Answers

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
like image 129
Nick Krasnov Avatar answered Oct 17 '22 06:10

Nick Krasnov