Re: How to associate a rowid with a table?

OK Thomas, I'm "Busted". The clustering would break this. I was thinking that the object number from the rowid in 8 would point you to the correct object.

Jmaes

In article <ervr8s8negaeacoagsb60tl4nhealqvmuq_at_4ax.com>,   tkyte_at_us.oracle.com wrote:
> A copy of this was sent to James Lorenzen
<james_lorenzen_at_allianzlife.com>
> (if that email address didn't require changing)
> On Tue, 25 Jan 2000 19:29:19 GMT, you wrote:
>
> >Yep.
> >In Oracle 7, the rowid breaks down to bbbbbbbb.ssss.ffff, where
bbbbbbbb
> >id the blockid and the ffff is the file id, dba_extents will show you
> >the way.
> >
> >In Oracle 8, the rowid is bigger, it contains the above, plus an
object
> >id, that can be used to get the object from dba_objects.
> >
>
> Actually... in both 7 and 8 you cannot reliably go from a rowid to a
table. You
> need to know what table the rowid belongs to to use it.
>
> Consider this example:
>
> ops$tkyte_at_8.0> insert into t_emp values ( 1, 1 );
> 1 row created.
>
> ops$tkyte_at_8.0> insert into t_dept values ( 1 );
> 1 row created.
>
> ops$tkyte_at_8.0> select rowid from t_emp;
>
> ROWID
> ------------------
> AAAG9tAAcAAAAXGAAA
>
> ops$tkyte_at_8.0> select rowid from t_dept;
>
> ROWID
> ------------------
> AAAG9tAAcAAAAXGAAA
>
> ops$tkyte_at_8.0>
> ops$tkyte_at_8.0> select emp_rid, dept_rid
> 2 from ( select rowid emp_rid from t_emp ),
> 3 ( select rowid dept_rid from t_dept )
> 4 where emp_rid = dept_rid
> 5 /
>
> EMP_RID DEPT_RID
> ------------------ ------------------
> AAAG9tAAcAAAAXGAAA AAAG9tAAcAAAAXGAAA
>
> both t_emp and t_dept are *real* tables -- both are in the same
database (no
> links, no tricks).... Yet, they have the same rowid....
>
> If you like a challenge, don't read further -- try to figure it out.
If you
> want the answer, goto the bottom...
>
> >HTH
> > James
> >
> >In article <388DC816.6350B924_at_mcgraw-hill.com>,
> > Jeffery Cann <Jeffery_Cann_at_mcgraw-hill.com> wrote:
> >> Hello.
> >>
> >> Given that a rowid specifies a particular physical location of a
row
> >> in a database, if I only have a rowid, is it possible to determine
> >> which schema and table it resides in? I was thinking this might be
> >> stored in the Data Dictionary, but then again...
> >>
> >> Thanks
> >> Jeff
> >>
>
> Use a cluster.....
>
> ops$tkyte_at_8.0> CREATE CLUSTER emp_dept_cluster
> 2 ( department_number NUMBER(2) )
> 3 SIZE 512
> 4 STORAGE (INITIAL 100K NEXT 50K);
>
> Cluster created.
>
> ops$tkyte_at_8.0>
> ops$tkyte_at_8.0> create index emp_dept_cluster_idx on cluster
emp_dept_cluster;
>
> Index created.
>
> ops$tkyte_at_8.0>
> ops$tkyte_at_8.0> CREATE TABLE t_emp
> 2 (empno NUMBER PRIMARY KEY,
> 3 deptno NUMBER(2) NOT NULL )
> 4 CLUSTER emp_dept_cluster (deptno);
>
> Table created.
>
> ops$tkyte_at_8.0>
> ops$tkyte_at_8.0>
> ops$tkyte_at_8.0> CREATE TABLE t_dept
> 2 (deptno NUMBER(2) )
> 3 CLUSTER emp_dept_cluster (deptno);
>
> Table created.
>
> --
> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June
21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>

--
Life is complex; it has real and imaginary parts.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 26 2000 - 09:12:25 CST