undo只是逻辑还原

最新推荐文章于 2025-12-01 02:11:37 发布

最新推荐文章于 2025-12-01 02:11:37 发布 · 175 阅读

文章标签：

#数据库

本文通过实验演示了数据库中undo操作的逻辑恢复特性而非物理恢复。通过创建表、插入大量数据并回滚，验证了即使执行回滚操作，表的高水位线依然存在，数据块并未真正恢复到初始状态。

undo只是逻辑还原数据库而不是物理的还原：

测试：
1.新建一个空表

create table t1
(
sid int not null primary key,
sname varchar2(10)
)tablespace test;

create table t2
(
sid int not null primary key,
sname varchar2(10)
)tablespace test;

select * from t2;

2.做全表扫描并查看I/O数量

set autotrace traceonly statistics;

select * from t2;

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
399 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

set autotrace off;

--循环导入数据
declare
maxrecords constant int:=20000;
i int :=1;
begin
for i in 1..maxrecords loop
insert into t1 values(i,'ocpyang');
end loop;
dbms_output.put_line(' 成功录入数据！ ');
commit;
end;
/

insert into t2 select * from t1;

rollback;

set autotrace traceonly statistics;

select * from t2;

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
53 consistent gets
0 physical reads
0 redo size
399 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

虽然数据rollback但是insert的操作还是导致表的高水位线，这些数据块并
没有因为rollback而回滚.

验证了数据库并没有物理完全恢复，仅仅是逻辑恢复！