Oracle8 ROWIDs - data file numbers

From: Ben Ryan <benryan_at_my-deja.com>
Date: 2000/03/01
Message-ID: <89hnid$k2d$1@nnrp1.deja.com>#1/1

The New Oracle8 ROWID format

The Oracle8.0 "new features" courses talk about the file number component of the ROWID as being the relative data file number within the tablespace. (In Oracle7 the datafile component of ROWID is the absolute data file number within the database.)

I cannot produce an example that shows the difference. My attempt below shows a partitioned table with two partitions containing two rows, one in each partition, with each partition being in a different tablespace. When I compare the relative data file numbers with the absolute data file numbers I do not see any difference. I would have expected the relative file number to be, say, 1 (meaning first data file within tablespace).

Can anyone give me an example which would show the difference.

Test is on 8.0.4.

CREATE TABLE junk (ord_id NUMBER)

   PARTITION BY RANGE (ord_id)
   (PARTITION p1 VALUES LESS THAN (10) TABLESPACE data01,     PARTITION p2 VALUES LESS THAN (MAXVALUE) TABLESPACE data02); INSERT INTO junk VALUES (1);
INSERT INTO junk VALUES (11);
COMMIT; SELECT RPAD(o.object_name,10) object,

       o.object_id,
       p.tablespace_name tablespace

  FROM user_objects o, user_tables p

 WHERE o.object_type = 'TABLE'
   AND o.object_name = 'JUNK'
   AND o.object_name = p.table_name

 UNION
SELECT RPAD(o.object_name||'.'||o.subobject_name,10) object,

       o.object_id,
       p.tablespace_name tablespace

  FROM user_objects o, user_tab_partitions p

 WHERE o.object_type = 'TABLE PARTITION'
   AND o.object_name = 'JUNK'
   AND o.object_name = p.table_name
   AND o.subobject_name = p.partition_name;

SELECT DBMS_ROWID.ROWID_OBJECT(ROWID) object,
       DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) relative_fno,
       DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,user,'JUNK') absolute_fno,
       DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) block_number,
       ord_id

  FROM junk;

Output is

OBJECT OBJECT_ID TABLESPACE

---------- ---------- ------------------------------
JUNK             3474
JUNK.P1          3475 DATA01
JUNK.P2          3476 DATA02

    OBJECT RELATIVE_FNO ABSOLUTE_FNO BLOCK_NUMBER     ORD_ID
---------- ------------ ------------ ------------ ----------
      3475            5            5            3          1
      3476            7            7            3         11

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Mar 01 2000 - 00:00:00 CST