Re: decreasing initial extent

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:PFcV9.24820$jM5.65253_at_newsfeeds.bigpond.com... [snip]
>
> 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.

Come off it Richard! (And I mean that in a loving way). You queried the wrong view. Here's a rather more telling test:

SQL> create tablespace britten datafile 'd:\oracle\oradata\oemrep\god01.dbf'   2 size 10m uniform size 64k;

Tablespace created.

SQL> create table pgrimes (x number)
  2 tablespace britten
  3 storage (initial 256K);

Table created.

SQL> select initial_extent from user_tables   2 where table_name='PGRIMES';

INITIAL_EXTENT

        262144

[So far, I agree with you test entirely, except that I have better taste in music].

But, and here's the killer:

SQL> select bytes from dba_extents
  2 where segment_name='PGRIMES';

     BYTES

     65536
     65536
     65536
     65536

The initial extent clause has been completely ignored, despite what DBA_TABLES (and, indeed, DBA_SEGMENTS will tell you). I've acquired 4 64K extents, not a single 256K one. As is to be expected when the tablespace simply cannot allocate anything other than 64K.

Right. Now for the final test:

SQL> alter table pgrimes deallocate unused keep 64K;

Table altered.

SQL> select bytes from dba_extents
  2 where segment_name='PGRIMES';

     BYTES

     65536
     65536

So yes, de-allocate unused does do *something*. In this case, it's de-allocated two extents. But has the size of those extents left behind altered in some way? Not a bit of it.

Did DBA_TABLES and DBA_SEGMENTS mysteriously change to record an inital_extent of 128K? Yup, most certainly. Does that mean anything as far as what is physically stored on disk? Nope.

It *might* have an impact if one were to export and import into another database with DMTs, or with differently-uniformly-sized LMTs. But it doesn't do what you'd expect if that had been DMTs.

And you'll note that I very carefully said originally that it wouldn't do what you might have expected, not that it wouldn't have any effect at all.

Regards
HJR
>
> 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 - 17:30:28 CST