Re: Some basic dba_free_space stuff

Absolutely.

Deleting rows never dealocates an extent. If you delete all the rows of a table, you still have all the extents allocated to the table. Each index on the table will also retain all its allocated extents.

If you have never dropped, truncated or alter drop unused any table in a tablespace, the tablespace's freespace will be un-fragmented.

Rollback segments have one more option - the optimal parameter. If this parameter is used, Oracle will automatically deallocate extents to shrink a segment that has over-extended. These shrinks also result in freespace extents in the rollback tablespace.

Doug Cowles wrote:

> If deletes don't free any extent, that in that case, if you never dropped any
> tables, never did any dealocate unused, or drop storage, there would never be
> any fragmentation apparent in dba_free_space, are you sure that deletes never
> free extents?
>
> - Dc.
>
> John Higgins wrote:
>
> > Basically, Oracle creates a free space extent one for one for every extent
> > of a table when you DROP the table. Deleting rows has no effect on dba_ffree
> > space.
> >
> > The ALTER TABLE ____ DEALOCATE UNUSED also returns extents to dba_free
> > _space.
> >
> > The TRUNCATE TABLE _____ DROP STORAGE also returns extents to
> > dba_free_space.
> >
> > Doug Cowles wrote:
> >
> > > Just some basic questions about dba_free_space.
> > > I have a tablespace, which in dba_free_space shows some "chunks" of free
> > > space.
> > > I'm wondering about the size of the chunks. If I delete a row for
> > > example, That one
> > > small chunk of space surely isn't freed - so how does Oracle decide that
> > > a "chunk"
> > > of space is free, after a certain number of deletes, does it do it's own
> > > coalescing
> > > for example? There's a 400K block of space in the below output for
> > > example.
> > > How did a "chunk" that size get freed?
> > >
> > > Output is
> > > tablespace file_id block_id
> > > bytes blocks
> > > INTFD2 15 27
> > > 14540800 1775
> > > INTFD2 15 8267
> > > 409600 50
> > > INTFD2 15 8537 34930688
> > > 4264
> > > INTFD2 15 4187
> > > 7372800 900
Received on Mon Jun 14 1999 - 18:43:56 CDT