OracleDBA之路Undo Data（三）

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

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

 

在另一个客户端同样以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