Re: decreasing initial extent

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:xFZU9.24113$jM5.64166_at_newsfeeds.bigpond.com...
>
> "Peter Laursen" <ptl_at_edbgruppen.dk> wrote in message
> news:3e23fcaa$0$206$edfadb0f_at_dread15.news.tele.dk...
> >
> > "Peter van Rijn" <p.vanrijnREMOVE_at_THISzhew.nl> wrote in message
> > news:v27u50s55or2f8_at_corp.supernews.com...
> > > alter table <table_name>
> > > move
> > > storage(initial <desired size>;
> > >
> > > regards,
> > > Peter
> > >
> >
> > Thank you,
> >
> > Dum question. The Admin Guide has "If the initial extent becomes
smaller,
> > the INITIAL value changes to reflect the new size of the initial extent.
"
> > in the text on alter table x deallocate unused. How come deallocate does
> not
> > decrease the initial extent? Because the tablespace is LMT?
> >
>
> Got it in one. In LMTs, assuming uniform extent allocation, there can be
> only one extent size... whatever was specified at tablespace creation
time.
> Deallocate use ordinarily chops off extents at the table's High Water Mark
> (on a 5-block boundary), which implies leaving behind 'odd-sized' extents.
> Since those physically can't exist in LMTs, the deallocate unused is not
> going to have the effect you were hoping for.
>

Might be of interest.

SQL> create tablespace major_tom datafile 'c:\bowie\major_tom01.dbf' size 10m un
iform size 64k;

Tablespace created.

SQL> create table ground_control (x number) tablespace major_tom storage (initia
l 256k);

Table created.

SQL> select initial_extent from user_tables where table_name = 'GROUND_CONTROL'; INITIAL_EXTENT

        262144

SQL> alter table ground_control deallocate unused keep 64K;

Table altered.

SQL> select initial_extent from user_tables where table_name = 'GROUND_CONTROL'; INITIAL_EXTENT

        131072

So the initial extent *can* be reduced if you initially made it greater than the uniform size.

One last trap. When you deallocate, it will by default only deallocate up to the minextent size. To reduce further, you must use the keep option.

Cheers

Richard Received on Wed Jan 15 2003 - 07:59:01 CST