| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Extent allocation for objects
Consider this...
SQL> select tablespace_name, initial_extent, next_extent,
extent_management, allocation_type
2 from dba_tablespaces where tablespace_name = 'WORK_DATA';
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT EXTENT_MANALLOCATIO
------------------------------ -------------- ----------- ---------- --------- WORK_DATA 10485760 10485760 LOCALUNIFORM Elapsed: 00:00:00.00
SQL> create table blah1 tablespace work_data as select * from dual;
Table created.
Elapsed: 00:00:00.11
SQL> create table blah2 storage(initial 2M next 2M) tablespace
work_data as select * from dual;
Table created.
Elapsed: 00:00:00.04
SQL> create table blah3 storage(initial 5M next 5M) tablespace
work_data as select * from dual;
Table created.
Elapsed: 00:00:00.06
--here I ran a pl/sql loop to insert 1 million rows into each.
SQL> select segment_name, extents, initial_extent, next_extent 2 from dba_segments where segment_name like 'BLAH%';
SEGMENT_NA EXTENTS INITIAL_EXTENT NEXT_EXTENT ---------- ---------- -------------- -----------
BLAH1 1 10485760 10485760 BLAH2 1 2097152 10485760 BLAH3 1 5242880 10485760
-- Looks like Oracle did what you told it for initial but not next. Not really, it kind of lies (see the following) -- SQL> select segment_name, extents, initial_extent, next_extent, bytes/1024/1024 2 from dba_segments where segment_name like 'BLAH%'; SEGMENT_NA EXTENTS INITIAL_EXTENT NEXT_EXTENT BYTES/1024/1024 ---------- ---------- -------------- ----------- --------------- BLAH1 2 10485760 10485760 20 BLAH2 2 2097152 10485760 20 BLAH3 2 5242880 10485760 20 -- Each table is 20 MBs in size. No matter what size was specified (or not), Oracle gave each table enough extents to maintain a 10 by 10 uniform allocation. So, what if we create one with larger extent size that LMT default. -- SQL> create table blah4 storage(initial 100M next 100M) tablespace work_data as select * from dual; Table created. Elapsed: 00:00:00.06 SQL> select segment_name, extents, initial_extent, next_extent, bytes/1024/1024 2 from dba_segments where segment_name like 'BLAH%'; SEGMENT_NA EXTENTS INITIAL_EXTENT NEXT_EXTENT BYTES/1024/1024 ---------- ---------- -------------- ----------- --------------- BLAH1 2 10485760 10485760 20 BLAH2 2 2097152 10485760 20 BLAH3 2 5242880 10485760 20 BLAH4 10 104857600 10485760 100 Elapsed: 00:00:00.05 -- Initial says 100M, but 10 extents at 10 MBs each = 100 MBs. Still, we have a 10 by 10 uniform allocation (even though for blah4 they are empty). It appears that for storage parameter control that LMT uniform allocation is undefeatable. -- >>> Stephen.Lee_at_DTAG.Com 05/19/03 04:57PM >>> It's a characteristic of uniform size. If you have uniform size 512 M, then you can't have an extent that is any other size. So you can specify initial extent and next extent until you're blue in the face, but you will always get 512 M extent sizes. See? Maybe LMT is quite the grand panacea.Received on Mon May 19 2003 - 17:52:36 CDT
> -----Original Message-----
>
> Let me give you the scenarios. Maybe I can get a good
> explanation from
> someone.
>
> Tablespace A locally managed uniform extents size 512M.
> Tablespace B locally managed uniforma extents size 1M.
>
> 1) Create table TabA tablespace A. Insert data, and it has 1
> extent of
> 512M even though the table size is only 76M. This makes sense.
> 2) Create table TabA tablespace B. Insert data, and it has
> 76 extents of
> 1M each. This makes sense.
> 3) Create table TabA tablespace A. Same as scenario 1. Alter
> table TabA
> move tablespace B. Creates 512 extents of 1M each. Why?
> The table only needs 76M of space. Why create all those
> additional
> extents?
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: Stephen.Lee_at_DTAG.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: DLANDRUM_at_zalecorp.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).
 |
 |