RE: Extent allocation for objects

Fyi,
Note I was referring to the other day.

       04:11 PM PST Please respond to [EMAIL PROTECTED]

Sent by: [EMAIL PROTECTED]

To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc:

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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).







American Express made the following
 annotations on 06/09/2003 02:26:35 PM
------------------------------------------------------------------------------
******************************************************************************

     "This message and any attachments are solely for the intended recipient and may 
contain confidential or privileged information. If you are not the intended recipient, 
any disclosure, copying, use, or distribution of the information included in this 
message and any attachments is prohibited.  If you have received this communication in 
error, please notify us by reply e-mail and immediately and permanently delete this 
message and any attachments.  Thank you."

******************************************************************************


==============================================================================

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tracy Rahmlow
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).