Re: Pctincrease and extent size

In article <88hpog$oc0$1_at_nnrp1.deja.com>,   Ed Stevens <Ed.Stevens_at_nmm.nissan-usa.com> wrote:
> A little education needed here -- and determine the outcome of a
> friendly bet!
>
> Given a table with storage parms of INITIAL=100K, NEXT=100K,
> PCTINCREASE=50 . . .
>
> My understanding is the initial extent will be 100k, the second extent
> will be 100k, the third will be 150k, the 4th will be 225, etc.
>
> Now, after the 4th extent (of 225k) we alter the table to
> PCTINCREASE=0. What will be the size of the 5th extent? Also, at
this
> point, will a simple export/import of the table yield uniform extents
> of 100k?
>
> TIA
>
> - Ed Stevens
>

Please observe the following:

UT1> create table marktest(fld1 varchar2(10), fld2 number, fld3 date)   2 storage (initial 100k next 100k pctincrease 50 minextents 4)   3 /

Table created.

UT1> column segment_name format a30
UT1> select segment_name, bytes/1024
  2 from sys.dba_extents
  3 where segment_name = 'MARKTEST'
  4 /

SEGMENT_NAME                   BYTES/1024
------------------------------ ----------
MARKTEST                              100
MARKTEST                              100
MARKTEST                              160
MARKTEST                              240

UT1> select next_extent
  2 from sys.dba_tables
  3 where table_name = 'MARKTEST'
  4 /
NEXT_EXTENT

     352256

UT1> alter table marktest allocate extent   2 /

Table altered.

UT1> select segment_name, bytes/1024
  2 from sys.dba_extents
  3 where segment_name = 'MARKTEST'
  4 /

SEGMENT_NAME                   BYTES/1024
------------------------------ ----------
MARKTEST                              100
MARKTEST                              100
MARKTEST                              160
MARKTEST                              240
MARKTEST                              360

UT1> spool off

It would appear the Oracle calculates the next_extent value at the time it takes the prior extent so it will be the next_extent value.

The answer to you export depends on if you set compress=y or compress=n on the export. Compress=y is the default so the table will get built with an initial extent of around 960K. Note Oracle likes to round extent allocations into units evenly divisible by five. --
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Feb 18 2000 - 14:46:44 CST