Re: Data Dictionary: relative_fno question

This is normal. The relative_fno (as well as the header_file column) in dba_segments view indicates the file number of the datafile that the segment header resides, and as you know one segment can span more than one data file. Those "missing" FNO in the view simply means those datafiles have no segment header - they are occupied by the "body" of data segments whose header is elsewhere (in other FNOs). If you really want to get a full list of FNOs (for datafiles that has data in them), you can check the dba_extents view - go one step further in a more detail level. A select distinct relative_fno from dba_extents will get you a full list of FNOs as you see from dba_data_files (as long as every datafile has data, i.e. extents, in it).

Jan Gelbrich (j_gelbrich_at_westfalen-blatt.de) wrote:
: 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 - 15:59:31 CST