On Jun 26, 12:54 pm, darr..._at_yahoo.com wrote:
> Hi,
> I have a production tablespace with millions of rows.
> I need to set sequence values in each row but I can not lock the table
> for any more than 5 seconds.
> I created SEQUENCE named tempseq and found that I can update 1000 rows
> in a short enough time.
>
> Here is the script I tested. I have tested it but I don't understand
> how each row (from the SELECT) is getting processed only once, or if
> this script even guarantees that this will happen.
>
> If someone can explain how ROWNUM is working in this UPDATE then that
> would help me.
> Alternative solutions (that don't involve explicit cursors) are also
> welcome.
>
> begin
> loop
> update (select seq_no from mytable) set seq_no = tempseq.nextval
> where rownum <= 1000;
> exit when SQL%FOUND = false;
> commit;
> end loop;
> end;
>
> TIA
> -Darren
Perhaps the use of online redefinition be a good solution for you.
Received on Sun Jul 01 2007 - 08:55:00 CDT
