SQL> conn internal
Connected.
---回滚段usn,回滚段头class为11+2n,回滚段块class为12+2n,---
SQL>select class,count(*) from x$bh where class>10 group by class;
CLASS COUNT(*)
---------- ----------
11
1
12
2
13
1
14 14
15
1
16
1
17
1
18
1
19
1
20
1
21
1
CLASS COUNT(*)
---------- ----------
22
1
23
1
24
3
25
1
26
1
16 rows selected.
SQL> select segment_id,segment_name from dba_rollback_segs;
SEGMENT_ID SEGMENT_NAME
---------- ------------------------------
0 SYSTEM
1 RBS0
2 RBS1
3 RBS2
4 RBS3
5 RBS4
6 RBS5
7 RBS6
8 rows selected.
---指定这个transaction使用rollback segment rbs3---
SQL> set transaction use rollback segment rbs3;
Transaction set.
---打开一个游标，但是暂不获取数据---
SQL>variable x refcursor
SQL>begin
2 open :x for select * from t where rownum=1;
3end;
4/
PL/SQL procedure successfully completed.
---update游标中查询的第一条记录，并且commit---
SQL> update t set object_id=1 where OBJECT_ID=7781;
1 row updated.
SQL> commit;
Commit complete.
---同样指定使用rollback segment rbs3，进行大量的update操作，
使得上面update操作在回滚段中的前影被覆盖（因为回滚段是循环使用的）。---
SQL> begin
2for i in 1..1000 loop
3set transaction use rollback segment rbs3;
4update t set object_id=i where rownum print x
ERROR:
ORA-01555: snapshot too old: rollback segment number 4 with name "RBS3" too
small
no rows selected
SQL>select class,count(*) from x$bh where class>10 group by class;
CLASS COUNT(*)
---------- ----------
11
1
12
2
13
1
14 14
15
1
16
1
17
1
18
1
19
2
20511-- RBS3使用的块
21
1
CLASS COUNT(*)
---------- ----------
22
1
23
1
24
3
25
1
26
1
16 rows selected.
SQL>
|
