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?
Once SQL Server commits a transaction, you cannot run the ROLLBACK statement.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With