Re: DBA_SEGMENT information??

From: <markp28665_at_aol.com>
Date: 1996/11/20
Message-ID: <19961120025400.VAA07059@ladder01.news.aol.com>#1/1

Author confused with object size in tablespace not equaling total space.

From what I can see in your note you have around 40M worth of total indexes allocated in a 60M file. The bytes column of sys.dba_segment is the total size in bytes of all the extents for the object.

You can query sys.dba_free_space as

select * from sys.dba_free_space where tablespace_name = '????' order by file_id, block_id;

If you 'union' this to the same columns of sys.dba_extents you can map the tablespace to see how fragmented it is.

I hope this is helpful.

Mark D. Powell -- The only advise that counts is the advise that you follow
so follow your own advise.

Original note >>>>
I am trying to calculate the size of the data within my tablespaces. I have come up with some conflicting information and I am baffled. Hopefully I am just misinterpretting the results.

Scenario:

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?

Regards,

David E. Daniel
Western Michigan University
From Received on Wed Nov 20 1996 - 00:00:00 CST