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

Yes -
 I'd be tempted to say that you can only SAFELY use rowid's when you got those rows locked.

 In fact, if you use partitioned tables, a simple update on the partition key is enough to break the rowid processing. (unless you have the row locked, which would have prevented the update)

Karsten

simmons_mark_at_yahoo.com (Mark) wrote in message news:<5366fb41.0403080822.27a7b7ca_at_posting.google.com>...
> 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 Tue Mar 09 2004 - 05:42:34 CST