OracleDBA之路Undo Data（三）

最新推荐文章于 2021-04-14 12:47:15 发布

iteye_12954

最新推荐文章于 2021-04-14 12:47:15 发布

阅读量113

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/iteye_12954/article/details/82140106

版权

Oracle 专栏收录该内容

45 篇文章 0 订阅

订阅专栏

SQL> select addr,used_ublk from v$transaction;

no rows selected

SQL> select begin_time,end_time,undoblks from v$undostat;

BEGIN_TIM END_TIME    UNDOBLKS
--------- --------- ----------
19-SEP-11 19-SEP-11          0
19-SEP-11 19-SEP-11         80
19-SEP-11 19-SEP-11         16

以 houzh/houzh登录
向t表中插入10000条记录，不提交
SQL> begin
2 for i in 1 .. 10000
3 loop
4 insert into t values(i,'houzhh');
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.
插入100W条数据时查看有多少undo blocks 使用

start_time --> start_time
start_scnb --> 开始的scn

used_urec --> undo 记录的行数

used_ublk --> 占用的undo block

在另一个客户端同样以houzh/houzh登录

插入

SQL> begin
2 for i in 10000 .. 100000
3 loop
4 insert into t values(i,'houzhh');
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select addr, START_TIME,START_SCNB,used_ublk,used_urec from v$transaction;

ADDR     START_TIME           START_SCNB USED_UBLK USED_UREC
-------- -------------------- ---------- ---------- ----------
37F6072C 09/19/11 20:57:20       1328853         88      10000
37F7C0D0 09/19/11 20:59:32       1328933        777      90001

SQL> select begin_time,end_time,undoblks from v$undostat;

BEGIN_TIM END_TIME    UNDOBLKS
--------- --------- ----------
19-SEP-11 19-SEP-11      10223
19-SEP-11 19-SEP-11         80
19-SEP-11 19-SEP-11         16
SQL> commit;

Commit complete.

SQL> select addr,used_ublk from v$transaction;

no rows selected