Solved

snapshot too old error

Posted on 2008-06-22
7
737 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now