Re: Updating by RowId when alread has fetched the row....

I was thinking that too, but then later started thinking about how doing an 'alter table move' command could screw up your table that stores the original rowid.

So I believe that there is something to consider here if you are thinking about using rowids in a table.

Plus, everyone's thrown out an idea and then later realized that what they said didn't make much sense.

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX

groups_at_karsten-schmidt.com (Karsten Schmidt) wrote in message news:<c6711ac4.0403080130.49b2b1aa_at_posting.google.com>...
> Hi,
> this example is bogus -
> you should have (as the OP) done a 'select for update'.
>
> then everything works fine, your alter table will complain about not
> being able to get an exclusive lock on the table - which is the right
> behaviour.
>
> Karsten
>
> srivenu_at_hotmail.com (srivenu) wrote in message news:<1a68177.0403050353.370e1024_at_posting.google.com>...
> > Small note (May not be a problem but just wanted to show).
> > create table x(a number);
> > insert into x values(1);
> >
> > from session 1
> > declare
> > v1 rowid;
> > begin
> > select rowid into v1 from x;
> > dbms_lock.sleep(20);
> > update x set a=100 where rowid=v1;
> > end;
> > /
> >
> > from session 2 - After 5 secs of starting the above in session 1
> > alter table x move tablespace users;
> >
> > Session 1 error
> > ERROR at line 1:
> > ORA-01410: invalid ROWID
> > ORA-06512: at line 6
> >
> > Even worse you may update a wrong row.
> > May want to convert that select rowid into v1 from x into a select
> > rowid into v1 from x for update.
> >
> > regards
> > Srivenu
Received on Mon Mar 08 2004 - 10:22:01 CST