Data Dictionary: relative_fno question

From: Jan Gelbrich <j_gelbrich_at_westfalen-blatt.de>
Date: Wed, 15 Jan 2003 11:28:15 +0100
Message-ID: <b03e96$ka0ha$1@ID-152732.news.dfncis.de>

Hello, this time I have a question about informations from data dtictionary.

When I want to see all used data files for the database, of cource this is easy:

SQL> select distinct relative_fno, substr(file_name,1,30) from dba_data_files;

RELATIVE_FNO SUBSTR(FILE_NAME,1,30)

------------ ------------------------------
           1 /u01/oradata/ora8/system01.dbf
           2 /u01/oradata/ora8/rbs01.dbf
           3 /u01/oradata/ora8/temp01.dbf
           4 /u01/oradata/ora8/tools01.dbf
           5 /u01/oradata/ora8/users01.dbf
           6 /u02/oradata/ora8/tt_dat01.dbf
           7 /u02/oradata/ora8/tt_dat02.dbf
           8 /u03/oradata/ora8/tt_temp01.db
           9 /u03/oradata/ora8/tt_ind01.dbf
          10 /u01/oradata/ora8/rbs02.dbf
          11 /u02/oradata/ora8/es_dat02.dbf
          12 /u01/oradata/ora8/system02.dbf
          13 /u03/oradata/ora8/es_ind01.dbf
          14 /u02/oradata/ora8/es_dat01.dbf
          15 /u04/oradata/ora8/op01.dbf
          16 /u04/oradata/ora8/op_ind01.dbf
          17 /u03/oradata/ora8/versand_ind0
          18 /sicher/altdaten01.dbf
          19 /u03/oradata/ora8/tt_ind02.dbf
          20 /sicher/altdaten.dbf
          21 /u03/oradata/ora8/versand_ind0
          22 /u01/oradata/ora8/versand1.dbf
          23 /u04/oradata/ora8/op_ind02.dbf
          26 /u01/oradata/ora8/versand.dbf

24 Zeilen ausgewählt.

Once I wanted to find out what segments are in the second data file of tablespace VERSAND.
It has 2 files:

/u01/oradata/ora8/versand1.dbf [RELATIVE_FNO: 22]
/u01/oradata/ora8/versand.dbf [RELATIVE_FNO: 26]

BUT: in tablespaces with more than one data file, all segments would reside in
at least one of them, so if I would do the following statement, I would expect
the same RELATIVE_FNO as in dba_data_files - but as everybody can see, some are missing !

SQL> select distinct relative_fno from dba_segments;

RELATIVE_FNO

---

           1
           2
           4
           6
           7
           9
          11
          12
          13
          14
          15
          16
          18
          19
          20
          21
          26

17 Zeilen ausgewählt.

SQL> Data File #22 is /u01/oradata/ora8/versand1.dbf, one of two files for tablespace VERSAND, and this file is still containing data segments (it is used by 30%), so there must be some segments in it; and I am not filtering any users out ...

So: why cant I see it in dba_segments ?
Where has file #22 gone ?

Hmmmmmmmmmmmmmmmmmm ...

Shall I use only *one* data file per tablespace ? This is not how I understood scalability ...

Any hints greatfully appreciated.

Jan Received on Wed Jan 15 2003 - 04:28:15 CST