回滚已提交的事务 [英] Rollback a committed transaction
问题描述
有什么方法可以在oracle 11g
我已经在db中创建了delete from table
并提交了它,现在我想rollback
提交更改.有什么办法吗?
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:
注意:此操作的成功取决于undo_retention
参数的值(默认为900秒)-一段时间(可以自动减少),在此期间将撤消信息保留在撤消表空间中.
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
向后插入已删除的行:
/* 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
这篇关于回滚已提交的事务的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!