Re: decreasing initial extent

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<H0mV9.24997$jM5.65909_at_newsfeeds.bigpond.com>...
>
> Come off it Richard! (And I mean that in a loving way). You queried the
> wrong view. Here's a rather more telling test:
>

Hi Howard,

You spoil sport, you went and ruined it all ;)

I'm sorry, I was being a bit mischievous (again).

With LMT, the term "Initial Extent" is just a touch ambiguous as the Initial storage parameter (along with the others) is used to determine the initial *size* of the segment, which is listed as Initial_Extent in the _table views. IMHO, once DMT are gone forever, it would be nice if this column be renamed "Initial Size" (or some such).

With LMT and the *uniform* clause the "true" initial extent (the first extent belonging to a segment), resizing is a tad difficult.

However, the "true" initial extent can be resized in a LMT (I'll be a bit more serious this time although yes, it's a bit of a dirty trick ;)

SQL> create tablespace auto_lmt datafile 'c:\bowie\auto_lmt01.dbf' size 100m autoallocate;

Tablespace created.

SQL> create table shrinkage_bowie (x number) tablespace auto_lmt storage (initial 2m);

Table created.

SQL> select bytes from dba_extents where segment_name='SHRINKAGE_BOWIE';

     BYTES

   1048576
   1048576

So autoallocate has decided based on my initial storage size of 2m to create 2 x 1m extents. Sounds fair enough to me, isn't Oracle clever.

However, oopps, really didn't mean it to be 2m, 50K would have been more than enough so let's resize the bugger.

SQL> alter table shrinkage_bowie deallocate unused keep 50K;

Table altered.

and now looking at dba_extents

SQL> select bytes from dba_extents where segment_name='SHRINKAGE_BOWIE';

     BYTES

     65536

There we go !! Oracle has said well since you only want 50K, we had better look at allocating you a more appropriate extent size and has resized my "true" initial extent down to 64K (smallest possible with autoallocate).

Pretty clever I thought !!

Cheers

Richard Received on Wed Jan 15 2003 - 22:03:38 CST