Re: archiving records

On 27 Aug 2006 21:58:05 -0700, "Daud" <daud11_at_hotmail.com> wrote:

>I just saw this piece of code written by one developer to archive
>records from one table to another table. The code has, of course, been
>simplified. He is doing it 100K rows at a time to avoid using too much
>rollback, I think. But what I am not sure is whether the 100K rows that
>got inserted in the archive table are the same as those that later got
>deleted.
>Please advised.
>
>Daud
>
>---------------------------------------------------------
>declare
> v_loop_count integer;
>begin
> SELECT COUNT(*)
> INTO v_loop_count
> FROM MYTAB
> WHERE ROWNUM < 100001;
>
> WHILE (v_loop_count > 0) LOOP
>
> INSERT INTO MYTAB_ARC
> SELECT * FROM MYTAB WHERE ROWNUM < 100001';
>
> DELETE FROM MYTAB WHERE ROWNUM < 100001;
> -- have i deleted the same 100K rows that just got inserted?
>
> COMMIT;
>
> SELECT COUNT(*)
> INTO v_loop_count
> FROM MYTAB
> WHERE ROWNUM < 100001;
>
> END LOOP;
>end;
>/

Not necessarily as there is no exclusive lock on the table. There are several solutions to this
1 forget about the rollback segments: disk is cheap. Make it one transaction (recommended)
2 use the returning into clause of the insert statement to return the ids of the records into a collection. the delete would read delete ... where id in select cast(table(collection)) from dual; how to cast a collection is on asktom.oracle.com 3 exclusively lock the table, prior to starting the insert.

--
Sybrand Bakker, Senior Oracle DBA