Re: How to associate a rowid with a table?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 25 Jan 2000 15:04:31 -0500
Message-ID: <ervr8s8negaeacoagsb60tl4nhealqvmuq@4ax.com>

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 Received on Tue Jan 25 2000 - 14:04:31 CST