在看expert one on one时,对于这段代码不是很理解,希望高手能给详细分析下:tkyte@TKYTE816> create table t as select * from all_objects;
Table created.tkyte@TKYTE816> create index t_idx on t(object_name);
Index created.tkyte@TKYTE816> create rollback segment rbs_small storage (initial 64k
2 next 64k minextents 2 maxextents 4 ) tablespace tools;
Rollback segment created.tkyte@TKYTE816> begin
2 for x in ( select rowid rid, object_name, rownum r
3 from t
4 where object_name > chr(0) )
5 loop
6 update t
7 set object_name = lower(x.object_name)
8 where rowid = x.rid;
9 if ( mod(x.r,100) = 0 ) then
10 commit;
11 end if;
12 end loop;
13 commit;
14 end;
15 /
begin
*
ERROR at line 1:
ORA‐01555: snapshot too old: rollback segment number 10 with name ʺRBS_SMALLʺ too
small
ORA‐06512: at line 2
为什么肯定会造成ORA‐01555错误呢?好像没有需要用到被更新数据的地方啊?
索引在这里的作用是什么呢?
希望高手能给分析下他具体的过程是怎样的?
一头雾水………………
Table created.tkyte@TKYTE816> create index t_idx on t(object_name);
Index created.tkyte@TKYTE816> create rollback segment rbs_small storage (initial 64k
2 next 64k minextents 2 maxextents 4 ) tablespace tools;
Rollback segment created.tkyte@TKYTE816> begin
2 for x in ( select rowid rid, object_name, rownum r
3 from t
4 where object_name > chr(0) )
5 loop
6 update t
7 set object_name = lower(x.object_name)
8 where rowid = x.rid;
9 if ( mod(x.r,100) = 0 ) then
10 commit;
11 end if;
12 end loop;
13 commit;
14 end;
15 /
begin
*
ERROR at line 1:
ORA‐01555: snapshot too old: rollback segment number 10 with name ʺRBS_SMALLʺ too
small
ORA‐06512: at line 2
为什么肯定会造成ORA‐01555错误呢?好像没有需要用到被更新数据的地方啊?
索引在这里的作用是什么呢?
希望高手能给分析下他具体的过程是怎样的?
一头雾水………………
make sure that I was reading the table randomly. The WHERE clause will use the index (I
Expert one-on-one Oracle
188
used the rule‐based optimizer for this). When we process a table via an index, we will tend
to read a block for a single row and then the next row we want will be on a different block.
We will process all of the rows on block 1, just not concurrently. Block 1 might hold, say,
rows A, M, N, Q, and Z. So we would hit the block four times with long time intervals
between each hit. Since we are committing frequently and reusing rollback space, we
eventually revisit a block we can simply no longer reproduce and get the error.
他后面不就说明了么
if ( mod(x.r,100) = 0 ) then
--你这个是修改了100行再提交 提交会清空回滚段
没commit或者rollback之前都会往回滚段里写
而你设置的回滚段太小了如果你把if ( mod(x.r,100) = 0 ) then 去掉也许就不会报这个错误了
small
回滚段大小为10 太小
你创建回滚段的时候 可以改的大一些
create rollback segment rbs_small storage (initial 64k
next 64k minextents 2 maxextents 4 ) tablespace tools;
能不能麻烦说具体一点?
这是在书上看到的,就是为了演示回滚段很小而且频繁提交会导致ORA‐01555错误
但是具体是怎样导致的呢?
eventually revisit a block we can simply no longer reproduce and get the error.
看这个 频繁的提交 会频繁的使用 rollback space 就导致了这个错误
至于添加index和where 和这个没关系 只是为了随机的访问这个表的数据块 防止访问同一块
也就是为了提高这段代码的执行速度
那这个例子中是哪里导致了ORA‐01555呢?
为什么会导致呢?