Mystery solved:

This table exists in an 8i database where I'm using LMT. In this particular
tablespace, I have uniform extent size of 100MB. Prior to truncate, the
table had 5 extents, or 500MB. After truncating, it still had 5 extents
which was stumping me. I finally found the answer to be that the initial
extent value was 500MB. I believe the initial extent value of 500MB is
there from when we migrated the database via export/import from v8.0.5 on
another server 6 months back.

Anyway, I'm glad I figured this one out because it would have kept me up
tonight!

Thanks everyone!

> -----Original Message-----
> From: Thurow, Laura
> Sent: Friday, November 30, 2001 2:52 PM
> To: LazyDBA.com Discussion
> Subject: RE: Extents > 1 after Truncate
>
> Thanks Don, however as I stated in the original post, my minextents is
> already at one......
>
> > -----Original Message-----
> > From: Don Granaman [SMTP:[Email Address Removed] > Sent: Friday, November 30, 2001 2:41 PM
> > To: LazyDBA.com Discussion; Thurow, Laura
> > Subject: Re: Extents > 1 after Truncate
> >
> > It is "truncate table <table_name> drop storage" that will reduce it
> > to one extent and "truncate table <table_name> reuse storage" that
> > will keep all extents. "deallocate usused" is not valid syntax for
> > truncate. The default is "drop storage".
> >
> > I suspect that the reason you ended up with more that one extent is
> > that minextents > 1. By default, truncate does reduce it to one
> > extent - if minextents has the default value of one! If you want to
> > reduce minextents, do:
> >
> > SQL> alter table <table_name> storage (minextents 1);
> >
> > -Don Granaman
> > [OraSaurus]
> >
> > ----- Original Message -----
> > From: "Thurow, Laura" <[Email Address Removed] > To: "LazyDBA.com Discussion" <[Email Address Removed] > Sent: Friday, November 30, 2001 2:10 PM
> > Subject: RE: Extents > 1 after Truncate
> >
> >
> > > Patricia
> > >
> > > Thanks, but that did not work either. Furthermore, truncating the
> > table by
> > > default is suppose to reduce the table to 1 extent, per Oracle's own
> > > documentation. I'll keep searching...
> > >
> > > > -----Original Message-----
> > > > From: Patricia Karla Cordeiro [SMTP:[Email Address Removed] > > > Sent: Friday, November 30, 2001 2:04 PM
> > > > To: LazyDBA.com Discussion
> > > > Subject: RE: Extents > 1 after Truncate
> > > >
> > > > Laura,
> > > > When you truncate, the High Water Mark is set back, but the
> > extents are
> > > > not
> > > > deallocated. To do this, issue an "alter table table_name
> > deallocate
> > > > unused".
> > > >
> > > > Regards,
> > > > Patricia.
> > > >
> > > > -----Original Message-----
> > > > From: Thurow, Laura [mailto:[Email Address Removed] > > > Sent: Sexta-feira, 30 de Novembro de 2001 17:02
> > > > To: LazyDBA.com Discussion
> > > > Subject: Extents > 1 after Truncate
> > > >
> > > >
> > > > Hi
> > > >
> > > > I have a table with 5 extents. I truncated the table and it still
> > has 5
> > > > extents and I cannot figure out why. Should it not be 1 extent
> > after
> > > > truncation? Min extents is 1.
> > > >
> > > > 8.1.7 on HP-UX11.0
> > > >
> > > >
> > > >
> > > >
> > > > "WorldSecure" made the following
> > > > annotations on 11/30/01 14:00:37
> > >
> > > --------------------------------------------------------------------
> > ------
> > > > --
> > > > --
> > > >
> > > > [INFO] -- Virus Manager:
> > > > This message was scanned with Network Associates Viper,
> > > > and no viruses were detected.
> > > >
> > > >
> > ======================================================================
> > ====
> > > > ==
> > > > ==
> > > >
> > > >
>

"WorldSecure" made the following
annotations on 11/30/01 16:21:22
------------------------------------------------------------------------------

[INFO] -- Virus Manager:
This message was scanned with Network Associates Viper,
and no viruses were detected.

==============================================================================

Oracle LazyDBA home page