Solved

snapshot too old error

Posted on 2008-06-22
7
745 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

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!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 video shows how to recover a database from a user managed backup

729 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