Re: From row to datafile

From: Ian Cary <Ian.Cary_at_ons.gsi.gov.uk>
Date: Thu, 5 Aug 2004 10:00:35 +0100
Message-ID: <OF34A1CBEA.ED9AA23D-ON80256EE7.002DBA48-80256EE7.00317E1E@ons.gov.uk>

David is right about using dbms_rowid to identify the file a row is stored in although I think he meant to type dbms_rowid.rowid_relative_fno(rowid) (no _to_)

e.g. (using one I prepared earlier)

SQL> desc test_blob_data

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 FILENAME                                           VARCHAR2(500)
 FILEDATA                                           BLOB

SQL> select count(*) from test_blob_data   2 /

  COUNT(*)

---

         3

SID> l

  1   select dbms_rowid.rowid_relative_fno(t.rowid) fno,
  2          file_name
  3   from   test_blob_data t,
  4          dba_data_files f

  5* where dbms_rowid.rowid_relative_fno(t.rowid) = f.relative_fno SID> /    FNO FILE_NAME

------ ------------------------------------------------------------
     4 /u02/oradata/otstest1/data02.dbf
     4 /u02/oradata/otstest1/data02.dbf
     4 /u02/oradata/otstest1/data02.dbf

The BLOB column gets stored in a LOBSEGMENT the location of which can be derived with;

SID> l

  1  select l.table_name,
  2         l.column_name,
  3         s.segment_name,
  4         s.relative_fno,
  5         f.file_name
  6  from   dba_segments s,
  7         dba_lobs l,
  8         dba_data_files f

  9 where segment_type = 'LOBSEGMENT'

 10  and    l.segment_name = s.segment_name
 11  and    s.relative_fno = f.relative_fno
 12* and    l.table_name = 'TEST_BLOB_DATA'

SID> / TABLE_NAME COLUMN_NAME SEGMENT_NAME RELATIVE_FNO FILE_NAME

-------------- ----------- ------------------------- ------------
--------------------------------------------------
TEST_BLOB_DATA FILEDATA    SYS_LOB0000031352C00002$$           16

/u02/oradata/otstest1/index04.dbf

Hope this helps,

Cheers,

Ian

For the latest data on the economy and society consult National Statistics at http://www.statistics.gov.uk

---

Please Note: Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications

---

Legal Disclaimer : Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics

---

---

This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email

---

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Aug 05 2004 - 03:56:25 CDT