Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Set initial extent to half of its size
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
> > >
> > >
>
>
>