Re: Are rowids unique among tables?

In article <%8lD5.20828$XV.1132383_at_nntp3.onemain.com>,   "Barbara Kennedy" <barbken_at_teleport.com> wrote:
> Actually in Oracle 8 rowid is unique within a tablespace. It is
 possible to
> have the same rowid in different tablespaces. This is a change from
 Oracle
> 7 and why the rowid changed. This is so they could have adatabase up
 to a
> total of 50 petabytes in size.
> Jim
> <pku12345_at_my-deja.com> wrote in message news:8rj6a2
 $3nu$1_at_nnrp1.deja.com...
> > By definition, a rowid identifies the storage location of a row
 within
> > a datafile/extent/block. Since no two rows (from the same table or
> > otherwise) can physically occupy the same space, rowids are unique
> > through out a given database. I don't know why your last query
 didn't
> > work though -- curious....
> >
> > Pei Ku
> >
> > In article <8rj4am$28m$1_at_nnrp1.deja.com>,
> > kal121_at_my-deja.com wrote:
> > > I know that rowids are unique within a given table, but are they
 unique
> > > among tables? I tried to verify this myself, but Oracle wouldn't
 let
 me.
> > >
> > > (works ok)
> > >
> > > SQL> select rowid from tab1;
> > >
> > > ROWID
> > > ------------------
> > > AAAA5bAAHAAAAFrAAA
> > > AAAA5bAAHAAAAFrAAB
> > >
> > > (does not work)
> > >
> > > SQL> select rowid from tab1 where rowid in (select rowid from
 tab2);
> > >
> > > select rowid from tab1 where rowid in (select rowid from tab2)
> > > *
> > > ERROR at line 1:
> > > ORA-01410: invalid ROWID
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > >
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

>
>

Unfortunately that is not quite correct; the relative file number is unique within a tablespace so the relative file number can be duplicated throughout a database between tablespaces, however the object_id (data object number in the extended rowid) is unique throughout the database, thus making each and every ROWID unique throughout.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.