Re: Freespace in Tablespaces that doesn't show in dba_free

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 23 Feb 1999 16:27:03 GMT
Message-ID: <36dbd61c.97836150@192.86.155.100>

A copy of this was sent to lekdhjfl_at_aol.com (Lekdhjfl) (if that email address didn't require changing) On 23 Feb 1999 15:54:36 GMT, you wrote:

>
>Hi,
>I have done this testing on Oracle 8.0.4 database running on Solaris 2.6.
>Please go through my testing procedure and help me with my problem.
>
>Thanks,
>Sub
>
>

you check the wrong table -- you looked at dba_segments to see what the initial extent was requested to be. you need to look at user/dbs_extents to see what was actually allocated. Oracle won't leave little holes around and will round extent requests up in some cases. Here is your example with the query against user_extents to see that the table filled the entire 1016k free in the tablespace:

SQL> REM create tablespace my_tab
SQL> REM datafile '/d01/oracle8/oradata/oracle8/tab1.dbf' size 1048576
SQL> 
SQL> select tablespace_name, bytes

  2 from dba_free_space
  3 where tablespace_name = 'MY_TAB'
  4 /

TABLESPACE_NAME                     BYTES
------------------------------ ----------
MY_TAB                            1040384

SQL>
SQL> create table my_table
  2 (xx varchar2(100),
  3 yy number(10)
  4 )
  5 tablespace my_tab storage
  6 ( initial 1015808 next 16384 minextents 1 pctincrease 0)   7 /

Table created.

SQL> 
SQL> 
SQL> select initial_extent, next_extent, min_extents, max_extents, pct_increase

  2 from dba_segments
  3 where segment_name = 'MY_TABLE'
  4 /

INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE -------------- ----------- ----------- ----------- ------------

1015808 16384 1 505 0

SQL>
SQL> select segment_name, bytes, blocks   2 from user_extents
  3 where segment_name = 'MY_TABLE'
  4 /

SEGMENT_NAME                        BYTES     BLOCKS
------------------------------ ---------- ----------
MY_TABLE                          1040384        127
                                  ^^^^^^^ = 1016k = 1024k-8k overhead for mgmt..











>SQL> create tablespace my_tab

> 2 datafile '/u04/oradata/assurep/tab1.dbf' size 1048576;
>
>Tablespace created.
>
>
>SQL> select tablespace_name, bytes from dba_free_space
>where tablespace_name = 'MY_TAB';
>TABLESPACE_NAME BYTES
>------------------------------ ----------
>MY_TAB 1040384
>
>
>/* well, here Iam creating a table in the tablespace I have created
>in the above statement with an initial extent of 1015808
>and next extent of 16384, so that it can accomadate both of them
>in the same tablespace */
>
>
>SQL> create table my_table (xx varchar2(100),
>yy number(10))
>tablespace my_tab
> storage
>(
>initial 1015808
>next 16384
> minextents 1
>pctincrease 0
>)
>
>
>select initial_extent, next_extent, min_extents, max_extents,
> pct_increase
> from dba_segments
> where segment_name = 'MY_TAB';
>
>
>INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
>-------------- ----------- ----------- ----------- ------------
> 1015808 16384 1 505 0
>
>
>SQL> select tablespace_name, bytes
> from dba_free_space
> where tablespace_name = 'MY_TAB';
>
>no rows selected
>
>/* Now here eventhough I have a free space of 24,576 bytes
>ie.. 1040384 - 1015808 = 24576 bytes
>the entry for tablespace_name MY_TAB does not appear
>in the table dba_free_space.
>This is causing me a lot of problem.
>Can you explain me why it does not show in the dba_free_space
>table and what I should do to overcome this ..*/

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Feb 23 1999 - 10:27:03 CST

Re: Freespace in Tablespaces that doesn't show in dba_free_space