oracle事务_笔记6：Snapshot Too Old错误

Snapshot Too Old错误

下面来看开发人员喜欢在过程性循环中提交更新的第二个原因，这是因为他们可能被误导，试图节俭地使用“受限资源”(undo段)。这是一个配置问题，你需要确保有足够的undo空间来适应当地确定事务的大小。如果在循环中提交，一般会更慢，不仅如此，这也是导致让人胆战心惊的ORA-01555错误的最常见的原因。

Oracle的多版本模型会使用undo段数据依照语句或事务开始时的原样来重建块。如果必要的undo信息不再存在，就会收到一个ORA-01555:snapshot too old错误消息，查询也不会完成。

sys@ORCL>create table t as select * from all_objects;

Table created.

sys@ORCL>create index t_idx on t(object_name);

Index created.

sys@ORCL>exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

PL/SQL procedure successfully completed.

sys@ORCL>create undo tablespace undo_small

2 datafile '/home/oracle/app/oracle/oradata/orcl/undo_small.dbf'

3 size 10m reuse

4 autoextend off

5 /

Tablespace created.

sys@ORCL>alter system set undo_tablespace=undo_small;

System altered.

sys@ORCL>begin

2 for x in(select /*+ INDEX(t t_idx) */ rowid rid,object_name,rownum r

3 from t

4 where object_name>' ')

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 with name "" too small

ORA-06512: at line 2

动态性能视图V$UNDOSTAT对于监视所生成的undo数量可能非常有用，还可以用来监视运行时间最长的查询的持续时间。

3、可重启动的过程需要复杂的逻辑

如果采用“在逻辑事务结束之前提交”的方法，最严重的问题是：如果UPDATE半截失败了，这会经常将你的数据库置于一种未知的状态中。除非你提前对此做了规划，否则很难重启动这个失败的过程，让它从摔倒的地方再爬起来。

例如，假设我们不是像上一个例子那样对列应用LOWER()函数，而是应用以下的列函数：

last_ddl_time=last_ddl_time + 1;

如果UPDATE循环半路停止了，怎么重启动呢?不能简单的重新运行。

我们需要更复杂的逻辑，必须有办法对数据“分区”。例如，可以处理以A开头的每一个OBJECT_NAME，然后是以B开头的，依次类推：

sys@ORCL>create table to_do

2 as

3 select distinct substr(object_name,1,1) first_char

4 from T

5 /

Table created.

sys@ORCL>begin

2 for x in(select * from to_do)

3 loop

4 update t set last_dll_time=last_ddl_time+1

5 where object_name like x.first_char || '%';

6 dbms_output.put_line(sql%rowcount || 'rows updated ');

7 delete from to_do where first_char=x.first_char;

8 commit;

9 end loop;

10 end;

11 /

1040rows updated

622rows updated

996rows updated

22rows updated

414rows updated

3887rows updated

3982rows updated

4104rows updated

6599rows updated

1121rows updated

393rows updated

1876rows updated

102rows updated

349rows updated

156rows updated

34984rows updated

1502rows updated

begin

*

ERROR at line 1:

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO_SMALL'

ORA-06512: at line 4

sys@ORCL>alter system set undo_tablespace=UNDOTBS1 ;

System altered.

sys@ORCL>begin

2 for x in(select * from to_do)

3 loop

4 update t set last_ddl_time=last_ddl_time+1

5 where object_name like x.first_char || '%';

6 dbms_output.put_line(sql%rowcount || 'rows updated');

7 delete from to_do where first_char=x.first_char;

8 commit;

9 end loop;

10 end;

11 /

72554rows updated

1119rows updated

3477rows updated

390rows updated

3rows updated

19rows updated

2rows updated

83rows updated

740rows updated

3085rows updated

58rows updated

1303rows updated

PL/SQL procedure successfully completed.

现在，如果这个过程失败了，我们就能重启动，因为不会再处理已经得到成功处理的任何对象名。不过这种方法也是有问题的，除非有某个属性能均匀地划分数据，否则最终行的分布就会差异很大。

最好的方法：力求简单。如果能SQL完成，那就在SQL里完成。如果不能在SQL中完成，就用PL/SQL实现。要用尽可能少的代码来完成，另外应当分配充分的资源。一定要考虑到万一出现错误会怎么样。应当正确地设置undo段的大小。如果你有非常大的表需要更新，就应该使用分区，这样就能单独地更新各个分区。甚至可以使用并行DML来执行更新，如果是Oracle Database 11g Release 2及以上版本，可以使用DBMS_PARALLEL_EXECUTE。

---

http://www.52ij.com/jishu/5240.htmloracle事务_笔记6：不好的事务习惯

本文来源 我爱IT技术网 http://www.52ij.com/jishu/5242.html 转载请保留链接。