Re: Help: Snapshot Too Old

From: Martin Drautzburg <drautzburg_at_altavista.net>
Date: 12 Apr 2001 23:03:08 +0200
Message-ID: <87elux6dbn.fsf@altavista.net>

u518615722_at_spawnkill.ip-mobilphone.net writes:

> I have to move 100m rows to another table use select/insert in PL/SQL.
> We have 10 rollback segments and a huge RBS tablespace, plenty room
> for rollback segment to grow.
>
> At first, we try to commit every 200 rows

This smells like a "fetch across commits" problem. Do you have an outer loop that fetches data and INSIDE the loop you commit. Then the problem is, that oracle tries to maintain a read consistent view for the outer loop. Committing inside the loop does not solve the problem, in a way it even makes it worse.

One way to get around this problem is:

while true
loop

        if no_more_data_to_move
        then
                exit;
        end if
        ;
        insert into target
        select * from source
        where rownum < 200
        ;
        delete from source
        where rownum < 200
        ;
        commit;

end loop;

You may want to replace the rownum clause by something more intelligent. In this case you really have a bunch of small transactions with no rolback requirements across commits; Received on Thu Apr 12 2001 - 16:03:08 CDT