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

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Fri, 05 Mar 2004 16:49:20 -0800
Message-ID: <1078534125.477312@yasure>

Mark wrote:

> I don't know of anyone that would consider executing DML code on a
> table while they were re-organizing it like this.  So your example
> doesn't make a lot of sense.
> 
> Mark Simmons
> Sr. Oracle DBA
> Sabre-Holdings, Southlake, TX
> 
> 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

If someone wants to alter a table's structure while the database is up and the table is in use they should look at the DBMS_REDEFINITON built in package.

http://www.psoug.org/reference/dbms_redefinition.html

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Fri Mar 05 2004 - 18:49:20 CST