高分请教回滚段问题

Question

oracle文档说明中对此错误提供的方案只有一个：使用更大的回滚段。这个方案很多时候并不能解决问题。要解决这个问题，必须理解oracle回滚段的工作原理：回退段中存放的信息被称为“前照”(pre-image)，也就是说当一个进程对某个表进行了DML操作以后，更改前的纪录信息被存放于回滚段，其作用有两个：1、当进程要求回滚（ROLLBACK）的时候，使用回滚段中信息是纪录复原；2、保持数据读的一致性，当一个进程从某个表中读纪录的时候，ORACLE返回的是当读开始或者进程开始时的纪录，如果在读取过程中有其他进程更改了表纪录，ORACLE就会从回滚段中读取当读操作开始时的数据。
回滚段中信息并不是持久有效的，当进程提交（COMMIT）或者回滚（ROLLBACK）的时候，回滚段就被释放了。
当一个进程在执行一个大查询的时候，如果在查询的过程中所读取得的表被更改而且更改COMMIT太久，那回滚段中的“前照”就有可能会被其他的进程覆盖，从而导致ORA-01555错误。几个建议：
1、增加回滚段的大小，因为ORACLE总是覆盖最旧的回滚段，所以大的回滚段能有效的降低数据被覆盖的可能性。
2、检查你的程序，避免在一个大查询的过程中对所查询的表执行太多更新操作。有一种情况下非常容易出现ORA-01555错误，就是用CURSOR查询，然后更改CURSOR的基表并且多次的COMMIT，比如：declare
  cursor my_cursor is
  select col1, col2...
    from mytable;  -- read from mytable
  ...
begin
  for i in my_cursor loop
     update mytable   
        set ...
      where col1 = i.col1;  -- update record by key     commit;   -- commit every row or a certain number of rows
  end loop;
end;
/对于上面的例子，你必须把commit拿掉，如果实在要做commit，就必须想办法保证cursor的读取不受更新的影响。比如更新以后关闭cursor然后再重新打开。我推荐的方法是使用bulk collect把纪录一次性读取到 pl/sql table变量中，然后无论你怎么commit都不会影响读的一致性了。今天太晚了，如果你需要bulk collect的示例，请告知。我明天给你。

Answer 1

bulk collect  是批量游标吧。如果你是大批量的数据话，用这个可以，每次游标批量取10000，每次commit.
这样就减少了对回滚段的大小要求。应该可以解决你的问题。

Answer 2

--创建了一个测试表TEST
SQL> create table test (col1 number);Table created--插入100000条测试数据
SQL> declare
  2    i number;
  3  begin
  4    for i in 1 .. 100000 loop
  5      insert into test (col1) values (i);
  6      commit;
  7    end loop;
  8  end;
  9  /PL/SQL procedure successfully completed--创建了一个测试表TEST2
SQL> create table test2 (col1 number,col2 number);Table created--现在通过循环将测试表test中的数据插入到Test2表
SQL> declare
  2    type curtype is ref cursor;
  3    cur curtype;
  4    n number;
  5  begin
  6    dbms_output.disable;
  7    dbms_output.enable(1000000);
  8    open cur for select col1 from test;
  9    dbms_output.put_line('第1条记录插入开始：'||to_char(sysdate,'hh24:mi:ss'));
 10    loop
 11      fetch cur into n ;
 12      exit when cur%notfound;
 13      insert into test2 (col1,col2) values (n,n*n);
 14    end loop;
 15    dbms_output.put_line('第'||cur%rowcount||'条记录插入结束：'||to_char(sysdate,'hh24:mi:ss'));
 16    commit;
 17    close cur;
 18  end;
 19  /第1条记录插入开始：01:18:37
第100000条记录插入结束：01:19:36PL/SQL procedure successfully completed--清空表TEST2
SQL> truncate table test2;Table truncated--现在通过游标批量帮定数据将测试表test中的数据插入到测试表Test2中--每次3000条
SQL> declare
  2    type curtype is ref cursor;
  3    cur curtype;
  4    type ntype is table of test.col1%type;
  5    n ntype;
  6    i number;
  7  begin
  8    dbms_output.disable;
  9    dbms_output.enable(1000000);
 10    open cur for select col1 from test;
 11    dbms_output.put_line('第1条记录插入开始：'||to_char(sysdate,'hh24:mi:ss'));
 12    loop
 13      fetch cur bulk collect into n limit 3000;
 14      for i in n.first .. n.last loop
 15        insert into test2 (col1,col2) values (n(i),n(i)*n(i));
 16      end loop;
 17      commit;
 18      exit when cur%notfound;
 19    end loop;
 20    dbms_output.put_line('第'||cur%rowcount||'条记录插入结束：'||to_char(sysdate,'hh24:mi:ss'));
 21    close cur;
 22  end;
 23  /第1条记录插入开始：01:40:26
第100000条记录插入结束：01:41:05PL/SQL procedure successfully completedSQL> select count(*) from test2;  COUNT(*)
----------
    100000SQL> truncate table test2;Table truncated

Answer 3

想用不同的批量测试，但是时不时就报错，不知道原因。代码没变，有时候成功执行，有时候报错，郁闷。declare
  type curtype is ref cursor;
  cur curtype;
  type ntype is table of test.col1%type;
  n ntype;
  i number;
begin
  dbms_output.disable;
  dbms_output.enable(1000000);
  open cur for select col1 from test;
  dbms_output.put_line('第1条记录插入开始：'||to_char(sysdate,'hh24:mi:ss'));
  loop
    fetch cur bulk collect into n limit 10000;
    for i in n.first .. n.last loop
      insert into test2 (col1,col2) values (n(i),n(i)*n(i));
    end loop;
    commit;
    exit when cur%notfound;
  end loop;
  dbms_output.put_line('第'||cur%rowcount||'条记录插入结束：'||to_char(sysdate,'hh24:mi:ss'));
  close cur;
end;ORA-06502: PL/SQL: 数字或值错误 
ORA-06512: 在line 14
就是for i in n.first .. n.last loop这里.
我之前一次测试却是正常的。
SQL> declare
  2    type curtype is ref cursor;
  3    cur curtype;
  4    type ntype is table of test.col1%type;
  5    n ntype;
  6    i number;
  7  begin
  8    dbms_output.disable;
  9    dbms_output.enable(1000000);
 10    open cur for select col1 from test;
 11    loop
 12      fetch cur bulk collect into n limit 10000;
 13      exit when cur%notfound;
 14      dbms_output.put('第'||(cur%rowcount-10000+1)||'条插入开始：'||to_char(sysdate,'hh24:mi:ss'));
 15      for i in n.first .. n.last loop
 16        insert into test2 (col1,col2) values (n(i),n(i)*n(i));
 17      end loop;
 18      dbms_output.put_line('第'||cur%rowcount||'条插入结束：'||to_char(sysdate,'hh24:mi:ss'));
 19      commit;
 20    end loop;
 21    close cur;
 22  end;
 23  /第1条插入开始：01:24:00第10000条插入结束：01:24:04
第10001条插入开始：01:24:04第20000条插入结束：01:24:09
第20001条插入开始：01:24:10第30000条插入结束：01:24:15
第30001条插入开始：01:24:16第40000条插入结束：01:24:22
第40001条插入开始：01:24:22第50000条插入结束：01:24:27
第50001条插入开始：01:24:28第60000条插入结束：01:24:32
第60001条插入开始：01:24:32第70000条插入结束：01:24:36
第70001条插入开始：01:24:36第80000条插入结束：01:24:40
第80001条插入开始：01:24:41第90000条插入结束：01:24:46
第90001条插入开始：01:24:46第100000条插入结束：01:24:50PL/SQL procedure successfully completed

Answer 4

1、从你的代码来看，对test表没有任何更新操作，如果只有这个进程在工作，那绝对不应该出现回滚段太旧的错误。2、现在你的测试中出现新的错误ORA-06502: PL/SQL: 数字或值错误，这个问题出现最可能的原因是当最后一次fetch cur bulk collect into n limit 10000;的时候，找不到任何记录，所以n.first和n.last是空的，加一句控制就可以。declare
  type curtype is ref cursor;
  cur curtype;
  type ntype is table of test.col1%type;
  n ntype;
  i number;
begin
  dbms_output.disable;
  dbms_output.enable(1000000);
  open cur for select col1 from test;
  dbms_output.put_line('第1条记录插入开始：'||to_char(sysdate,'hh24:mi:ss'));
  loop
    fetch cur bulk collect into n limit 10000;
    if n.count > 0 then  -- 检查n中的纪录数
        for i in n.first .. n.last loop
          insert into test2 (col1,col2) values (n(i),n(i)*n(i));
        end loop;
    end if;
    commit;
    exit when cur%notfound;
  end loop;
  dbms_output.put_line('第'||cur%rowcount||'条记录插入结束：'||to_char(sysdate,'hh24:mi:ss'));
  close cur;
end;
/你成功执行的那段代码中exit when放在 for i in ...之前，和我加控制的效果是一样的，所以不出错。你pl/sql的功力不错，再仔细一点就更好了 :)

Answer 5

谢谢.我之前是把exit when cur%notfound;放在for循环之前,但是后来发现问题,如果纪录总数不能整除limit参数,那么最后一部份纪录将不会被游标扫描到.

Answer 6

关于回滚段和bulk collect，你还有什么问题吗？

Answer 7

楼上各位写法都很好，不过在用bulk collect 做批量数据处理的时候，用for语句循环，执行效率还不够理想，对大数据量处理的时候，forall语句比起for语句在速度上有很大程度的提高，可以去官方网站看关于forall语句的介绍，保证很有收获