user rollbacks :每当用户发出rollback,user rollbacks计数将会增加1。
user commits: 每当事务提交user commits 计数增加1，对于没有事务的commit,计数不变。
C:\>sqlplus yansp/yansp
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 2月 15 17:09:28 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
> col name format a30
> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');
NAME VALUE
------------------------------ ----------
user commits 0
user rollbacks 0
> rollback;
回退已完成。
> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');
NAME VALUE
------------------------------ ----------
user commits 0
user rollbacks 1
可以看到user rollbacks 计数从0变为1。
> rollback;
回退已完成。
> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');
NAME VALUE
------------------------------ ----------
user commits 0
user rollbacks 2
再次执行rollback,user rollbacks 计数从1变为2。
> begin
2 for i in 1..100 loop
3 rollback;
4 end loop;
5 end;
6 /
PL/SQL 过程已成功完成。
> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');
NAME VALUE
------------------------------ ----------
user commits 0
user rollbacks 102
循环100次rollback,user rollback计数将增加100。
> commit;
提交完成。
> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');
NAME VALUE
------------------------------ ----------
user commits 0
user rollbacks 102
> commit;
提交完成。
> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');
NAME VALUE
------------------------------ ----------
user commits 0
user rollbacks 102
对于没有事务的commit,user commits不会增加。
> insert into t1 values(1);
insert into t1 values(1)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (TEST.SYS_C005716)
> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');
NAME VALUE
------------------------------ ----------
user commits 1
user rollbacks 102
> insert into t values(999);
insert into t values(999)
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');
NAME VALUE
------------------------------ ----------
user commits 2
user rollbacks 102
不过如果语句执行失败，将会导致user commits计数增加。
> commit;
提交完成。
> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');
NAME VALUE
------------------------------ ----------
user commits 2
user rollbacks 102
随后的commit都不会导致user commits 增加，除非有事务存在。
> insert into t1 values(9999);
已创建 1 行。
> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');
NAME VALUE
------------------------------ ----------
user commits 2
user rollbacks 102
> commit;
提交完成。
> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');
NAME VALUE
------------------------------ ----------
user commits 3
user rollbacks 102
> begin
2 for i in 1..100 loop
3 commit;
4 end loop;
5 end;
6 /
PL/SQL 过程已成功完成。
> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');
NAME VALUE
------------------------------ ----------
user commits 3
user rollbacks 102
> begin
2 for i in 1..100 loop
3 insert into t1 values(i+100000);
4 commit;
5 end loop;
6 end;
7 /
PL/SQL 过程已成功完成。
> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');
NAME VALUE
------------------------------ ----------
user commits 103
user rollbacks 102
从上面可以看出，通过user commits计数可以大体估算事务的提交次数。而user rollbacks计数却不能估算出事务回滚的次数。因为只要用户执行过rollback,
不管事务是否回滚，都会导致user rollbacks计数的增加。事务的回滚需要通过通过transaction rollback计数才估算。
看如下一个例子：
> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user rollbacks','transaction rollbacks') and b.sid=userenv('sid
');
NAME VALUE
---------------------------------------- ----------
user rollbacks 102
transaction rollbacks 1
> rollback;
回退已完成。
> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user rollbacks','transaction rollbacks') and b.sid=userenv('sid
');
NAME VALUE
---------------------------------------- ----------
user rollbacks 103
transaction rollbacks 1
> insert into t1 values(888);
已创建 1 行。
> rollback;
回退已完成。
> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user rollbacks','transaction rollbacks') and b.sid=userenv('sid
');
NAME VALUE
---------------------------------------- ----------
user rollbacks 104
transaction rollbacks 2
> begin
2 for i in 1..100 loop
3 rollback;
4 end loop;
5 end;
6 /
PL/SQL 过程已成功完成。
> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user rollbacks','transaction rollbacks') and b.sid=userenv('sid
');
NAME VALUE
---------------------------------------- ----------
user rollbacks 204
transaction rollbacks 2
> begin
2 for i in 1..100 loop
3 update t1 set c1=c1 where rownum=1;
4 rollback;
5 end loop;
6 end;
7 /
PL/SQL 过程已成功完成。
> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user rollbacks','transaction rollbacks') and b.sid=userenv('sid
');
NAME VALUE
---------------------------------------- ----------
user rollbacks 304
transaction rollbacks 102
