Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

snapshot too old error

Posted on 2008-06-22
7
Medium Priority
?
749 Views
Last Modified: 2013-12-19
hi
   The below segment of code is generating snapshoot too old. Can i know the reason and solution ?

declare
type t is table of emp%rowtype index by binary_index;

type t1 is table of emp.empno%type index by binary_interger;

coll1  t;
coll2 t1;
cursor cur_emp
is
select
 empno,
 ename
 from emp
 where deptno=10;

begin
    open cur_emp
   loop
    fetch cur_emp bulk collect into coll1 limit 5000
    exit when coll1.count=0;

    forall j in 1..coll1.count
     insert into emp_history values
     coll1(j) returning empno bulkcollect into coll2;

     forall i in 1..coll2.count
     delete emp where empno=coll2(i);

   commit
  end loop;
exception
 when others then
 Rollback
end;
0
Comment
Question by:vishali_vishu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 7

Expert Comment

by:Dauhee
ID: 21841821
What are you trying to do?
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 21843778
a commit within a loop causes this problem . . .
0
 
LVL 7

Expert Comment

by:Dauhee
ID: 21843966
no the commit wouldn't cause that - if anything it would alleviate things as redo could be discarded
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 48

Expert Comment

by:schwertner
ID: 21844163
Your code is unacceptable.
You open cursor. Thats good.
You never close it. Thats bad! very bad!!!!

begin
    open cur_emp;
   loop
    fetch cur_emp bulk collect into coll1 limit 5000
    exit when coll1.count=0;

    forall j in 1..coll1.count
     insert into emp_history values
     coll1(j) returning empno bulkcollect into coll2;

     forall i in 1..coll2.count
     delete emp where empno=coll2(i);

   commit
  end loop;
   close cur_emp;
exception
 when others then
 Rollback;
 close cur_emp;
end;
0
 
LVL 1

Author Comment

by:vishali_vishu
ID: 21845759
schwertner:

ok i will be closing the cursor but how to get rid of this snapshot too old error.?

The table is huge
0
 
LVL 7

Expert Comment

by:Dauhee
ID: 21845824
vishali_vishu:

What are you trying to do?
0
 
LVL 48

Accepted Solution

by:
schwertner earned 2000 total points
ID: 21846077
When you close the cursor and exit the procedure an automatic COMMIT occure.
In most cases this will shrink the UNDO segments.

But if this doesn't help you have to investigate the business logic
and to figure out if it is possible to put COMMIT statement for every 100
rows or for every 500 rows ....

This also will shrink the UNDO segment.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question