Re: DBA_SEGMENT information??

David E. Daniel wrote:

> I have a datafile for a tablespace named "psindex4.dbf". This
> tablespace contains three indexes and only three indexes as listed
> below.
>
> I run the following queries in SQL*PLUS:
>
> SQL> l
> 1 select substr(segment_name ,1,20) "SEGMENT NAME",
> 2 initial_extent "INITIAL",
> 3 next_extent "NEXT",
> 4 extents "EXTENTS",
> 5 bytes "BYTES"
> 6 from DBA_SEGMENTS
> 7 where tablespace_name = 'PSINDEX4'
> 8* order by segment_name
> And am returned ...
>
> SEGMENT NAME INITIAL NEXT EXTENTS BYTES
> -------------------- ---------- ---------- ---------- ----------
> PSAVCHR_ACCTG_LINE 15104000 102400 11 16128000
> PSBVCHR_ACCTG_LINE 5632000 102400 7 6246400
> PS_VCHR_ACCTG_LINE 18636800 102400 11 19660800
>
> The next query that I run is:
>
> select substr(file_name,1,40) "DATA FILE NAME",
> substr(tablespace_name,1,10) "TABLESPACE",
> bytes "BYTES"
> from dba_data_files
> order by tablespace_name;
>
> And am returned the following:
> [snip]
> DATA FILE NAME TABLESPACE BYTES
> ---------------------------------------- ---------- ----------
> c:\ordata\PSFS\FSP\psindex1.dbf PSINDEX1 104857600
> c:\ordata\psfs\fsp\psindex2.dbf PSINDEX2 104857600
> c:\ordata\psfs\fsp\psindex4.dbf PSINDEX4 62914560 ****This
> is the line***
> [snip]
>
> My question is if I add up the following:
>
> PSAVCHR_ACCTG_LINE = 15104000
> PSBVCHR_ACCTG_LINE = 5632000
> PS_VCHR_ACCTG_LINE = 18636800
> 27 Extents X 102400 = 2764800
> ________________________________________
> Total 209868800 BYTES
>
> Why does this not total to the same or less than my data file size?? I
> must be adding this incorrectly or misreading the information for which
> I have queried. Could someone, please offer me some insight?

  A datafile contains several segments. But, it also has left free spaces for the extents of segments. Unless you create many objects (segments), there must be some free spaces left.

-- 
Name        : Lun Wing San
Title       : Oracle developer of the Hong Kong Productivity Council
              System Administrator and Oracle DBA of the Quick Response
Center
Email Addr. : sunny_at_hkpc.org
Telephone   : 852-2788-5841