下面一段示例说明了oracle的基本操作insert、update、delete产生的undo的大小和记录条数。
--insert操作无论是1条或者多条，一次产生一条undo数据
SQL> create table a (b number, c varchar2(30));
Table created
SQL> insert into a values (1,'zhangsan');
1 row inserted
SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;
USED_UBLK USED_UREC
---------- ----------
1 1
SQL> insert into a select rownum,rownum from dual connect by rownum<=50;
50 rows inserted
SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;
USED_UBLK USED_UREC
---------- ----------
1 2
--delete操作产生的undo条数取决于delete操作本身影响的记录数SQL> delete from a;
51 rows deleted
SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;
USED_UBLK USED_UREC
---------- ----------
1 53
SQL> insert into a select rownum,rownum from dual connect by rownum<=50;
50 rows inserted
SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;
USED_UBLK USED_UREC
---------- ----------
1 54
--update操作产生的undo条数取决于被update的数据的原值和新值存在差异的记录数量
SQL> update a set c=rownum;
50 rows updated
SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;
USED_UBLK USED_UREC
---------- ----------
2 57
SQL> update a set c=rownum+1000;
50 rows updated
SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;
USED_UBLK USED_UREC
---------- ----------
2 107
SQL> update a set c=rownum;
50 rows updated
SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;
USED_UBLK USED_UREC
---------- ----------
3 157
SQL> update a set c=rownum;
责任编辑：小草
您现在的位置： 