Solved

snapshot too old error

Posted on 2008-06-22
7
744 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
Industry Leaders: 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!

 
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 500 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

734 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