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: Set initial extent to half of its size

Re: Set initial extent to half of its size

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Mon, 7 Feb 2000 12:10:42 +0100
Message-ID: <87m97n$40t$1@news4.isdnet.net>


Connor, you're right: i used an old doc. The new ones (7.3.4 and 8.x) read:
"KEEP specifies the number of bytes above the high-water mark that the table will have after deallocation. If the number of remaining extents are less than MINEXTENTS, then MINEXTENTS is set to the current number of extents. If the initial extent becomes smaller than INITIAL, then INITIAL is set to the value of the current initial extent."

I checked that, here's the results
(first with version 7.3.4 and 2K blocks and then with version 8.1.5 and 4K blocks):

v734> create table t (col number) storage (initial 20k next 10k minextents 2); v734> select substr(segment_name,1,30) name, bytes/1024 Kbytes,   2 extents, initial_extent/1024 Kinitial, min_extents   3 from user_segments where segment_name='T';

NAME                               KBYTES    EXTENTS   KINITIAL MIN_EXTENTS
------------------------------ ---------- ---------- ---------- -----------
T                                      30          2         20           2

v734> alter table t allocate extent (size 20k); v734> select substr(segment_name,1,30) name, bytes/1024 Kbytes,   2 extents, initial_extent/1024 Kinitial, min_extents   3 from user_segments where segment_name='T';

NAME                               KBYTES    EXTENTS   KINITIAL MIN_EXTENTS
------------------------------ ---------- ---------- ---------- -----------
T                                      50          3         20           2

v734> alter table t deallocate unused keep 10k; v734> select substr(segment_name,1,30) name, bytes/1024 Kbytes,   2 extents, initial_extent/1024 Kinitial, min_extents   3 from user_segments where segment_name='T';

NAME                               KBYTES    EXTENTS   KINITIAL MIN_EXTENTS
------------------------------ ---------- ---------- ---------- -----------
T                                      12          1         12           1


v815> create table t (col number) storage (initial 20k next 10k minextents 2); v815> select substr(segment_name,1,30) name, bytes/1024 Kbytes,   2 extents, initial_extent/1024 Kinitial, min_extents   3 from user_segments where segment_name='T';

NAME                               KBYTES    EXTENTS   KINITIAL MIN_EXTENTS
------------------------------ ---------- ---------- ---------- -----------
T                                      32          2         20           2

v815> alter table t allocate extent (size 20k); v815> select substr(segment_name,1,30) name, bytes/1024 Kbytes,   2 extents, initial_extent/1024 Kinitial, min_extents   3 from user_segments where segment_name='T';

NAME                               KBYTES    EXTENTS   KINITIAL MIN_EXTENTS
------------------------------ ---------- ---------- ---------- -----------
T                                      52          3         20           2

v815> alter table t deallocate unused keep 10k; v815> select substr(segment_name,1,30) name, bytes/1024 Kbytes,   2 extents, initial_extent/1024 Kinitial, min_extents   3 from user_segments where segment_name='T';

NAME                               KBYTES    EXTENTS   KINITIAL MIN_EXTENTS
------------------------------ ---------- ---------- ---------- -----------
T                                      16          1         16           1

--
Have a nice day
Michel

Connor McDonald <connor_mcdonald_at_yahoo.com> a écrit dans le message : 38997460.6BC0_at_yahoo.com...
> Michel Cadot wrote:
> >
> > I'm afraid i'm not agree with you, follows an abstract
> > of the Oracle alter table documentation:
> >
> > <quote>
> > When the high water mark is less than the size of INITIAL
> > or MINEXTENTS, then all unused space above MINEXTENTS is freed.
> > </quote>
> >
> > It seems to me that means you cannot deallocated below the INITIAL.
> >
> > --
> > Have a nice day
> > Michel
> >
> > Jaap W. van Dijk <j.w.vandijk_at_hetnet.nl> a écrit dans le message :
> > #dpkQQkb$GA.284_at_net003s...
> > > This is only possible if the High Water Mark of the table is below half
the
> > > inital extent, because you can never shrink below the HWM.
> > > If this is the case you can shrink the initial extent with
> > >
> > > ALTER TABLE <tablename> DEALLOCATE UNUSED KEEP <half the size of the
> > > intitial extent minus HWM)
> > >
> > > You have to check your next extent specification, if I'm not mistaken it
> > > will be resized to the last extent (back to front) removed.
> > >
> > > Jaap.
> > >
> > > Dominik Schroeder heeft geschreven in bericht
> > > <38995060.2C603A79_at_gei-aachen.de>...
> > > >Hi!
> > > >
> > > >For a number of tables I need to set the initial extent to half of its
> > > >current size. Since I'd like to do it automatically, I wonder if I can
> > > >do it (w/o PL/SQL...if possible).
> > > >
> > > >The statements' functionality ought to be like:
> > > >
> > > >alter table xxx storage ( initial select 0.5*initial_extent
> > > > from user_tables
> > > > where table_name='xxx'
> > > > );
> > > >
> > > >Best regards,
> > > >
> > > >Dominik Schröder
> > >
> > >

>

> My doco reads
>

> "If the initial extent becomes smaller than INITIAL, then INITIAL is
> adjusted to the new size"
>

> Its time for someone to post an example... :-)
> --
> ===========================================
> Connor McDonald
> "These views mine, no-one elses etc etc"
> connor_mcdonald_at_yahoo.com

>
> "Some days you're the pigeon, and some days you're the statue."
Received on Mon Feb 07 2000 - 05:10:42 CST

Original text of this message

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