Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Bug in dba_free_space?

Re: Bug in dba_free_space?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 6 May 2002 22:17:17 +0100
Message-ID: <1020719801.27156.0.nnrp-07.9e984b29@news.demon.co.uk>

I'll guess that your uniform extent size is 100MB, and your file size is 1700 MB.

If so, add 64K to the file size and you'll find that your dba_free_space jumps by 100MB.

dba_free_space measures usable free space, and if you don't allow 64K at the head of the file for the bitmap header on an LMT, then there will be a gap at the end of the file which is too small for a complete extent, and therefore doesn't appear as free space.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



Chuck wrote in message ...

>Can someone tell me what's going on here? I always thought that the size of
>the allocated extents plus the free space in a datafile would equal the
>total size of the datafile. That doesn't appear to be true with this file.
I
>think dba_free_space or dba_extents is missing something. BTW this datafile
>is the sole datafile of a locally managed tablespace.
>
>select 'free space' block_class,sum(bytes)/1048576 megabytes
>from dba_free_space where file_id = 733
>union all
>select 'used extents',sum(bytes)/1048576 megabytes
>from dba_extents where file_id = 733
>union all
>select 'total in file',sum(bytes)/1048576 megabytes
>from dba_data_files where file_id = 733;
>
>Here's the results. They don't add up.
>
>BLOCK_CLASS MEGABYTES
>------------- ----------
>free space 200
>used extents 1400
>total in file 1700
>
>Platform: SGI IRIX64
>Oracle version: 8.1.7.2.0 (the current 8i patch for IRIX).
>
>
Received on Mon May 06 2002 - 16:17:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US