RE: Extent allocation for objects

At 02:16 PM 5/19/2003 -0800, you wrote:

>Adding the initial clause appears to work fine. Thank you very much for
>your help.
>
>Thanks for the other responses. I learn new things every day.

If you specify an initial extent size that is at most as large as the smallest uniform extent size of any of your LMTs then you'll not run into this problem in the first placs. Consider:

SQL> select
TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTLEN,CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE    2 from dba_tablespaces where tablespace_name in ('USERS','TOOLS')    3 /

TABLESPA INITIAL_EXTENT NEXT_EXTENT MIN_EXTLEN CONTENTS EXTENT_MAN ALLOCATIO -------- -------------- ----------- ---------- --------- ---------- ---------

TOOLS            262144      262144     262144 PERMANENT LOCAL      UNIFORM
USERS             32768       32768      32768 PERMANENT LOCAL      UNIFORM

i.e. 2 tablespaces with 256K and 32K uniform extents respectively.

SQL> create table a (n number) tablespace tools; Table created.

SQL> create table b (n number) tablespace tools storage( initial 2K); Table created.

pollux.stats.scott> select segment_name, segment_type, tablespace_name, initial_extent, extents, bytes

   2 from user_segments where segment_name in ('A','B');

SEGMENT_ SEGMENT_TYPE TABLESPA INITIAL_EXTENT EXTENTS BYTES

-------- ------------------ -------- -------------- ---------- ----------
A        TABLE              TOOLS            262144          1     262144
B        TABLE              TOOLS              8192          1     262144

The 2K initial extent request for table b got adjusted by Oracle to the minimum possible in the database with a 4K db_block_size, but it got recorded in tab$ even though a 256K extent got allocated in the TOOLS LMT according to the uniform extent size.

SQL> alter table a move tablespace users; Table altered.

SQL> alter table b move tablespace users; Table altered.

SQL> select segment_name, segment_type, tablespace_name, initial_extent, extents, bytes

   2 from user_segments where segment_name in ('A','B');

SEGMENT_ SEGMENT_TYPE TABLESPA INITIAL_EXTENT EXTENTS BYTES

-------- ------------------ -------- -------------- ---------- ----------
A        TABLE              USERS            262144          8     262144
B        TABLE              USERS              8192          1      32768

Because the recorded initial extent size of table b is less than the uniform extent size of the target tablespace, it gets sized down correctly while table a maintains it allocated size.

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).