在Oracle 11g中,有没有一种方法可以回滚已提交的事务?
我在数据库中执行了delete from table
操作并将其提交,现在我想要回滚已提交的更改。有没有办法做到这一点?
已经提交的内容无法回滚。在这种情况下,你可以采用一个较快的方法:对于删除了行的表,可以使用闪回查询将它们重新插入。以下是一个简单的示例:
注意:此操作的成功取决于undo_retention
参数的值(默认为900秒)- 保留撤消信息的时间段(可以自动缩短)。
/* 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
SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
然后将其插入到删除表中
INSERT INTO employee (SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS'));