?
Solved

snapshot too old error

Posted on 2008-06-22
7
Medium Priority
?
751 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
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup
Suggested Courses

621 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