A question comes up on Chinese Oracle DBA forum about how to find which datafile has the selected BLOB data. Some answered using dbms_rowid. Here's a test in Oracle 9.2.0.1.0 to prove that dbms_rowid doesn't help. (BLOB or CLOB doesn't matter but I'll test with BLOB.)

SQL> create table testlob (lobdata blob, otherdata number) tablespace users
  2   lob (lobdata) store as (tablespace tools disable storage in row);

Table created.

SQL> insert into testlob values (hextoraw('12345678'), 123);

1 row created.

SQL> alter tablespace tools offline;

Tablespace altered.

SQL> select length(lobdata) from testlob;
select length(lobdata) from testlob
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: 'D:\ORA9I\ORADATA\RCAT\TOOLS01.DBF'


SQL> alter tablespace tools online;

Tablespace altered.

SQL> select length(lobdata) from testlob;

LENGTH(LOBDATA)
---------------
              4

SQL> select rowid from testlob;

ROWID
------------------
AAAHV6AAIAAAAHfAAA

SQL> select dbms_rowid.rowid_relative_fno('AAAHV6AAIAAAAHfAAA') from dual;

DBMS_ROWID.ROWID_RELATIVE_FNO('AAAHV6AAIAAAAHFAAA')
---------------------------------------------------
                                                  8

SQL> select file_id, tablespace_name from dba_data_files where tablespace_name in ('USERS', 'TOOLS');

   FILE_ID TABLESPACE_NAME
---------- ------------------------------
         7 TOOLS
         8 USERS

By default the lob data would be stored inline with non-lob column. So I explicitly did "disable storage in row" because my table is so small. You can tell the effect of this clause by failing to read lob when tools is offline. But dbms_rowid only tells us the data is in users tablespace, not tools.

I did another test (not shown here) by switching the order of testlob and otherdata columns, thinking maybe the order matters to dbms_rowid. The result is the same, rowid_relative_fno still reports 8.

I did a 10046 trace
alter session set events '10046 trace name context forever, level 8';
Unfortunately, the trace file indicates both datafiles are accessed:

WAIT #1: nam='db file sequential read' ela= 73385 p1=8 p2=467 p3=1
WAIT #1: nam='db file scattered read' ela= 832 p1=8 p2=468 p3=8
WAIT #1: nam='db file scattered read' ela= 533 p1=8 p2=476 p3=5
WAIT #1: nam='db file sequential read' ela= 10954 p1=7 p2=1604 p3=1

Interestingly, in file 8 there's a full table scan of 1+8+5=14 blocks, while file 7 where my blob is has only 1 block read, even though user_blobs says I got 16 blocks in it too.

SQL> select file_id, block_id, blocks, relative_fno, tablespace_name
  2  from dba_extents
  3  where segment_name = (select segment_name from user_lobs where table_name = 'TESTLOB');

   FILE_ID   BLOCK_ID     BLOCKS RELATIVE_FNO TABLESPACE_NAME
---------- ---------- ---------- ------------ ------------------------------
         7       1585         16            7 TOOLS

So using SQL trace is not that obvious either.

Yong Huang