Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Joe.

Asked: September 25, 2002 - 8:08 am UTC

Last updated: October 09, 2012 - 11:52 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Tom,

I have a LMT tablespace defined as follows:

CREATE TABLESPACE ABC_IDX
DATAFILE '/ora101/oradata/ABC/ap_idx01.dbf' SIZE 10240064K,
'/ora101/oradata/ABC/ap_idx02.dbf' SIZE 4096064K,
'/ora310/oradata/ABC/ap_idx03.dbf' SIZE 1024064K autoextend on maxsize 3000M
extent management local uniform size 64M
/
I have about 35 indexes created at this point. It looks like the 64M I chose for the uniform extent size was too big. I believe there is a lot of wasted space. Is it possible to use the deallocate_unused_clause on the alter index? If so, how can I tell how much space was actually used?
Or, is it better to do an EXPORT, rebuild the tablespace and IMPORT the data.



Thanks for your help.

Joe

and Tom said...

Uniform is uniform.

The extents will ALL always be 64m in this tablespace.

You could:

o create new tablespace with smaller uniform OR system managed extent sizes (for when you don't KNOW the ultimate size or just don't "care")

o alter index INDEX_NAME rebuild tablespace New_Tablespace <online if you want>

o drop this tablespace




Rating

  (38 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Follow up Question

Joe, September 25, 2002 - 9:23 am UTC

Tom,
If I do an export for the user, drop and recreate the user, drop and recreate the LMT with a uniform size of lets say 5M and import the data which was exported for this user, how will this affect the number of extents? (ie if an index acually used 10M of the 64M, will the new index use 2 extents?

Thanks,

Joe

Tom Kyte
September 25, 2002 - 10:05 am UTC

<b>No, do not do that, you'll end up with NO CHANGE in storage!!!</b>

Do it the way I suggest, alter index rebuild.  It'll be

a) faster
b) easier
c) using less space (your goal)
d) safer (you KNOW it'll work without losing a single thing.  with export/import, too easy to lose a grant or something along the way)

Consider this example:


ops$tkyte@ORA920.US.ORACLE.COM> create tablespace test
  2  datafile size 5184k
  3  extent management local
  4  uniform size 1m
  5  /

Tablespace created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create table t ( x int ) tablespace users;

Table created.

ops$tkyte@ORA920.US.ORACLE.COM> create index t_idx on t(x) tablespace test;

Index created.

ops$tkyte@ORA920.US.ORACLE.COM> select segment_name, extent_id, bytes from user_extents;

SEGMENT_NAME                    EXTENT_ID      BYTES
------------------------------ ---------- ----------
T                                       0      65536
T_IDX                                   0    1048576


<b>so, t_idx is 1 extent, 1 meg...</b>

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> host exp userid=/ tables=t

Export: Release 9.2.0.1.0 - Production on Wed Sep 25 10:06:09 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                              T          0 rows exported
Export terminated successfully without warnings.

ops$tkyte@ORA920.US.ORACLE.COM> host imp userid=/ full=y show=y

Import: Release 9.2.0.1.0 - Production on Wed Sep 25 10:06:09 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing OPS$TKYTE's objects into OPS$TKYTE
 "CREATE TABLE "T" ("X" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRA"
 "NS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USE"
 "RS" LOGGING NOCOMPRESS"
. . skipping table "T"

 "CREATE INDEX "T_IDX" ON "T" ("X" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STOR"
 "AGE(<b>INITIAL 1048576</b> FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "TEST" LOGGIN"
 "G"
Import terminated successfully without warnings.

<b>Import is telling us that the initial extent will AGAIN be 1m - the current initial extent!!!</b>


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> drop table t;

Table dropped.

ops$tkyte@ORA920.US.ORACLE.COM> drop tablespace test;

Tablespace dropped.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create tablespace test
  2  datafile size 5184k
  3  extent management local
  4  uniform size 512k
  5  /

Tablespace created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> host imp userid=/ full=y

Import: Release 9.2.0.1.0 - Production on Wed Sep 25 10:06:09 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing OPS$TKYTE's objects into OPS$TKYTE
. . importing table                            "T"          0 rows imported
Import terminated successfully without warnings.

ops$tkyte@ORA920.US.ORACLE.COM> select segment_name, extent_id, bytes from user_extents;

SEGMENT_NAME                    EXTENT_ID      BYTES
------------------------------ ---------- ----------
T                                       0      65536
T_IDX                                   0     524288
T_IDX                                   1     524288

<b>See the index is in two extents to get 1m of space</b>

So, just do it the way I suggest -- you can write a very very very easy query against user_indexes to generate the alter index rebuild statements!!!

 

A hacked up solution

Connor McDonald, September 25, 2002 - 10:03 am UTC

sys@bcon> create tablespace blah
2 datafile '/ora02/bcon/bcondemo01.dbf.blah' size 10m reuse
3 extent management local uniform size 2m;

Tablespace created.

sys@bcon> create table t1 ( x number ) tablespace blah;

Table created.

sys@bcon> exec dbms_space_admin.tablespace_migrate_from_local('BLAH');

PL/SQL procedure successfully completed.

sys@bcon> alter tablespace blah minimum extent 128k;

Tablespace altered.

sys@bcon> alter tablespace blah default storage ( initial 128k next 128k);

Tablespace altered.

sys@bcon> create table t2 ( x number ) tablespace blah
2 storage ( initial 128k);

Table created.

sys@bcon> exec dbms_space_admin.tablespace_migrate_to_local('BLAH');

PL/SQL procedure successfully completed.

sys@bcon> select segment_name, bytes
2 from user_segments
3 where segment_name in ('T1','T2');

SEGMENT_NAME BYTES
---------------------------------------- -------------
T1 2097152
T2 131072

So you've got a smaller extent in an LMT...admittedly its no longer uniform :-)

Tom Kyte
September 25, 2002 - 10:08 am UTC

oh yuck.

alter index rebuild -> new tablespace. (please!)

uniform size vs autoallocate (system managed)

A reader, September 25, 2002 - 10:08 am UTC

hi tom

In oracle 9i tablespace management is by default autoallocate, wouldnt this lead to fragmentation?

Shall we stick to the traditional strategy creating several sized tablespace (such as small medium big) but using uniform size or we should use the new default oracle bahaviour?

Tom Kyte
September 25, 2002 - 11:00 am UTC

No, fragmentation occurs in a tablespace when you have lots of wacky sized extents -- eg:

5 blocks allocated
50 blocks allocated
15 blocks allocated
35 blocks allocated
150 blocks allocated
10 blocks allocated


and start DROPPING/truncating objects leaving "holes" of weird sizes.

With an LMT using system managed extents -- you'll find the extents are one of a COUPLE of sizes - not an infinite number of sizes -- and each size can be used by any other object (cause the NEXT extent is not a fixed number -- it is some number we decide for you AT the point in time we need it)

It will not fragment.

(i prefer uniform when you KNOW the sizes of the objects. If you don't know or don't care to know, system allocated is just fine)

Uniform LMT

Joe, September 25, 2002 - 10:41 am UTC

Tom,

Thanks for the visual. I will be rebuilding the indexes.

Joe

What shoud be uniform size for LMT ?

Parag Jayant Patankar, January 14, 2005 - 8:50 am UTC

Hi Tom,

What are the things to be considered for having uniform size in LMT in Oracle 9i for newly created TBS ?

Is this uniform size should be praportionate to uniform size of temporary tbs ?

( Can I take a approch of create uniform extent of 64k and just forget everything )

regards & thanks
pjp

Tom Kyte
January 14, 2005 - 9:01 am UTC

If you have access to "Effective Oracle by Design" -- I go over the in's and out's of this in some detail.

I actually am a fan of system allocated extents now. It grows the extent allocation size as the table grows. A 10gig segment would be in about 300/400 extents -- very good.

If you use uniform, you size the extent size based on the expected growth pattern of the object. I've heard some smart people suggest "size the extent size so the table grows at about 1 extent per month" (that would work well for new tables). In a data warehouse, you might do it so the segment has a couple hundred extents after loading.

64k would not be a good "all around uniform size"

A reader, January 24, 2005 - 3:45 pm UTC


Behavior upon import of objects into system managed LMTs

Bernice, May 18, 2005 - 3:31 pm UTC

Tom,

I briefly searched this site for an answer to my question but still confused. It is related to this thread
and this </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1633139438701 <code>

Goal: migrate database (8174) on solaris to linux (9204). Both, 8K block size.
The tablespaces in the 8174 are all dictionary managed.
The tablespaces in the 9205 db are locally managed tablespaces (system-managed, auto segment space management).

----------------------------------------------------------
Based on Ch4 of your Effective Oracle by Design, I am aware of the Legacy Storage Clauses and their effects. I did a test exp/imp of one of my tables to prove what I expect. I am slightly confused with what I see.


In the 8174 db, the table is ~2M in size and is occupying 47 extents (1st extent has 185 blocks and
rest of the extents are 2 blocks/extent).
I use the following command to export the table --
exp blam/*** tables=tab1 rows=y compress=n consistent=y direct=y file=tab1.dmp


This is the command I used to import into the 9205 db --
imp usr/usr fromuser=blam touser=usr indexes=n file=tab1.dmp


Upon import, I run the following query to check the object and the amt of extents allocated for the object.

select segment_name, extent_id, blocks, file_id
from dba_extents
where segment_name = 'TAB1'
and owner='USR'
order by extent_id;

SEGMENT_NAME EXTENT_ID BLOCKS FILE_ID
-------------------- ---------- ---------- ----------
TAB1 0 128 3
TAB1 1 128 3


I am confused. Since this is system managed, I was expecting to see at least a few initial extents to be of 8 blocks in size and then slowly increase in extent size until 128 blocks/extent. Instead, I just see only 2 extents allocated!

Here is also the create table command that I extracted from the dmp file using the indexfile parameter in an import command.

REM CREATE TABLE "USR"."TAB1" ("USER_ID" VARCHAR2(30) NOT NULL
REM ENABLE, "COMPONENT" VARCHAR2(30) NOT NULL ENABLE, "NAME" VARCHAR2(30)
REM NOT NULL ENABLE, "HEADING" VARCHAR2(30), "SELECTED" VARCHAR2(1),
REM "FLD_ORDER" NUMBER(3, 0), "WIDTH" NUMBER(4, 0), "FORMAT"
REM VARCHAR2(255), "DATA_TYPE" VARCHAR2(1), "INSERTED_DATE" DATE,
REM "INSERTED_BY" VARCHAR2(30), "LAST_MODIFIED_DATE" DATE,
REM "LAST_MODIFIED_BY" VARCHAR2(30)) PCTFREE 10 PCTUSED 40 INITRANS 1
REM MAXTRANS 255 LOGGING STORAGE(INITIAL 1507328 NEXT 16384 MINEXTENTS 1
REM MAXEXTENTS 121 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
REM BUFFER_POOL DEFAULT) TABLESPACE "RSB" ;
REM ... 34867 rows


Another thing I need to mention -- the 8174 database was created about 3.5 yrs back and had its contents populated from export/import from an 805 database on a sgi platform. At that time, I was under the impression that compress=y was a good thing and stupidly used that feature. That is why the storage is weird in the 8174 db.

Can you explain why the extents are allocated the way they are in the 9205 database? Is it because of the remnants of using compress=y from a previous migration? Or it is briefly answered in the 'other thread' (#8 of your initial answer) where you said "oracle would look at the 1gig initial request and allocate a couple of extents to satisfy that". So, if the initial storage request is large enough, Oracle will by pass all the smaller-sized extents and just allocate a large one?

thanks,
Bernice

Tom Kyte
May 18, 2005 - 4:53 pm UTC

 
ops$tkyte@ORA9IR2> select 128*2*8/1024, 1507328 /1024/1024  from dual;
 
128*2*8/1024 1507328/1024/1024
------------ -----------------
           2            1.4375


it has to allocate at least 1.4375 meg (for your old initial).  so, it used what it felt like.

LMT's take the initial, next, minextents, pctincrease to figure out how much to initially allocate and allocate that much.

if you started "small" it would have done the 64k thing and stepped up.

ops$tkyte@ORA9IR2> create tablespace autoallocate_lmt
  2  datafile size 100m
  3  extent management local
  4  autoallocate;
 
Tablespace created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t1 ( x int ) tablespace autoallocate_lmt storage
  2  ( INITIAL 1507328 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 121
  3    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t2 ( x int ) tablespace autoallocate_lmt storage
  2  ( INITIAL 128k NEXT 16384 MINEXTENTS 1 MAXEXTENTS 121
  3    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t3 ( x int ) tablespace autoallocate_lmt storage
  2  ( INITIAL 128k NEXT 128k MINEXTENTS 10 MAXEXTENTS 121
  3    PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select segment_name, extent_id, blocks, file_id
  2  from dba_extents
  3  where segment_name in ( 'T1', 'T2', 'T3' )
  4  and owner=user
  5  order by segment_name, extent_id;
 
SEGMENT_NAME                    EXTENT_ID     BLOCKS    FILE_ID
------------------------------ ---------- ---------- ----------
T1                                      0        128         11
T1                                      1        128         11
T2                                      0          8         11
T2                                      1          8         11
T3                                      0        128         11
T3                                      1        128         11
T3                                      2        128         11
T3                                      3        128         11
T3                                      4        128         11
T3                                      5        128         11
T3                                      6        128         11
T3                                      7        128         11
T3                                      8        128         11
T3                                      9        128         11
 
14 rows selected.



it is autoallocate and if you start empty and grow -- it'll adjust.  If you say "give me big", it'll start big 

excellent

Bernice, May 18, 2005 - 5:21 pm UTC

Thanks! Crystal clear.



Uniform Size scenario

Deepak Panda, June 28, 2005 - 1:01 pm UTC

Hi Tom,

Are there any scenarios where UNIFORM SIZE is preferable to AUTOALLOCATE?

Tom Kyte
June 28, 2005 - 1:17 pm UTC

when you want to micromanage space utilization and be able to predict file growth accurately over time.

You can size your extents so that your objects add say a new extent once a month based on historical growth. You'll know about when a new extent will be allocated, you can size precisely for it and you can monitor exactly your growth.

with autoallocate, the extent sizes will jump up in size over time, making the predication of when a next extent will allocate hard and how big the next extent is "not so possible"

Fragmentation with autoallocate

Deepak, June 28, 2005 - 2:06 pm UTC

In case of autoallocate option Oracle completely controls the extent allocation. How does Oracle predicts the extent size requirement for our objects? The performance might degrade if the proper extent size is not chosen. Autoallocate might also cause fragmention.

Kindly help me understanding the concepts behind autoallocate feature.

Tom Kyte
June 28, 2005 - 2:39 pm UTC

powers of 2, all about powers of 2. As the object grows, so do the extents.

performance would not be impacted. size would.

autoallocate is not going to cause widespread fragmentation. And if you keep objects you drop/truncate separate from objects you don't -- it'll never fragment.

If you would like to read my writing on this -- I write a section on it in Effective Oracle by Design.

autoallocate can fragment

Niall, July 21, 2005 - 8:40 am UTC

A couple of years ago and earlier on in the thread you wrote.

#bNo, fragmentation occurs in a tablespace when you have lots of wacky sized
extents -- eg:

5 blocks allocated
50 blocks allocated
15 blocks allocated
35 blocks allocated
150 blocks allocated
10 blocks allocated


and start DROPPING/truncating objects leaving "holes" of weird sizes.

With an LMT using system managed extents -- you'll find the extents are one of a
COUPLE of sizes - not an infinite number of sizes -- and each size can be used
by any other object (cause the NEXT extent is not a fixed number -- it is some
number we decide for you AT the point in time we need it)

It will not fragment.#b

But this statement is not always true. The script below exhibits the same behaviour on 10g and 9i (haven't tried 10Gr2)


OPS$NIALL@orcl>select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
PL/SQL Release 10.1.0.4.0 - Production
CORE 10.1.0.4.0 Production
TNS for 32-bit Windows: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production

5 rows selected.

OPS$NIALL@orcl>
OPS$NIALL@orcl>create tablespace auto_alloc_test
2 datafile 'c:\temp\auto_alloc.dbf' size 32832k
3 extent management local;

Tablespace created.

OPS$NIALL@orcl>
OPS$NIALL@orcl>/*
OPS$NIALL@orcl>create the tables
OPS$NIALL@orcl>*/
OPS$NIALL@orcl>
OPS$NIALL@orcl>prompt 'create 32 tables'
'create 32 tables'
OPS$NIALL@orcl>begin
2 for i in 1..32 loop
3 execute immediate 'create table table'||i||'(col1 number,col2 number) tablespace auto_alloc_test';
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

OPS$NIALL@orcl>
OPS$NIALL@orcl>select sum(bytes)/1024 free_k from dba_free_space where tablespace_name='AUTO_ALLOC_TEST';

FREE_K
----------
30720

1 row selected.

OPS$NIALL@orcl>
OPS$NIALL@orcl>prompt ' extent all the objects to 15 extents but in a piecemeal fashion '
' extent all the objects to 15 extents but in a piecemeal fashion '
OPS$NIALL@orcl>begin
2 for i in 1..15 loop
3 for j in 1..32 loop
4 execute immediate 'alter table table'||j||' allocate extent';
5 end loop;
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

OPS$NIALL@orcl>
OPS$NIALL@orcl>select sum(bytes)/1024/1024 free_M from dba_free_space where tablespace_name='AUTO_ALLOC_TEST';

FREE_M
----------


1 row selected.

OPS$NIALL@orcl>prompt 'drop every second objects freeing up half the space in the tablespace'
'drop every second objects freeing up half the space in the tablespace'
OPS$NIALL@orcl>begin
2 for i in 1..32 loop
3 if i mod 2 = 0 then
4 execute immediate 'drop table table'||i;
5 end if;
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

OPS$NIALL@orcl>prompt 'lots of lovely space'
'lots of lovely space'
OPS$NIALL@orcl>select sum(bytes)/1024/1024 free_mb from dba_free_space where tablespace_name='AUTO_ALLOC_TEST';

FREE_MB
----------
16

1 row selected.

OPS$NIALL@orcl>prompt 'now extend table 1 but I can't use the space'
'now extend table 1 but I can't use the space'
OPS$NIALL@orcl>alter table table1 allocate extent;
alter table table1 allocate extent
*
ERROR at line 1:
ORA-01653: unable to extend table OPS$NIALL.TABLE1 by 128 in tablespace AUTO_ALLOC_TEST


OPS$NIALL@orcl>
OPS$NIALL@orcl>drop tablespace auto_alloc_test including contents and datafiles;

Tablespace dropped.

OPS$NIALL@orcl>
OPS$NIALL@orcl>spool off

So I'd contend that if you have the sort of behaviour you describe, extending, dropping, truncating going on #b and#b you are short of space then fragmentation is still a potential issue for autoallocate.

The script if you want to repeat/vary the test is at </code> http://www.niall.litchfield.dial.pipex.com/scripts/tests/auto_alloc_frag.sql <code>

Tom Kyte
July 21, 2005 - 3:35 pm UTC

I'd put this into the "extreme case" yes. But i'll stand by the advice:

IF you know PRECISELY how big the object is, will be or will grow by -- go ahead, do the math, feel free to use uniform.

Else, use autoallocate, it is just too easy to pass up and the extreme case you demonstrate, well, if you are doing that, maybe that is the problem more than anything (something wrong with implementation)

You'd think Oracle could find space that _is_ there.

Joel Garry, July 21, 2005 - 4:38 pm UTC

I'm missing something here. I took Niall's script and ran it on 9iR2 hp/ux, without the drop tablespace at the end. Then I dropped table3, table5, and so on one by one, still getting the error. I was not able to alter table table1 allocate extent; until after I dropped table31. Coalescing TS didn't help, viewing tablespace map showed way lots open space, including numerous 168 blocks per single blockid (noted after dropping table21). What exactly does "ORA-01653: unable to extend table SYS.TABLE1 by 128 in tablespace AUTO_ALLOC_TEST" mean here? 128 what? It created a 128 block extent when it finally did it (with the holes between the table1 extents at 248 blocks). Looks to me like a bug if Oracle throws 1653 trying to allocate 128 blocks with n*168 blocks available (maybe a bug in OEM or coalescing, but still). I use autoallocate all the time, so I need to know what to look for to avoid unexpected problems.



Tom Kyte
July 21, 2005 - 5:45 pm UTC

128 blocks

but I concurr, if you

begin
for i in 1..32 loop
if i mod 2 = 0 then
execute immediate 'drop table table'||i;
end if;
end loop;
end;
/
begin
for i in 2..17 loop
if i mod 2 = 1 then
execute immediate 'drop table table'||i;
end if;
end loop;
end;
/

there are a couple of 136 block free extents that should be used but aren't. I'll file a bug - thanks for persisting.

(i'll still be using autoallocate though! I still feel in my mind the problem is the radical drops more than anything)




Here is something interesting ... maybe?

Gabe, July 21, 2005 - 8:10 pm UTC

The net effect of the way the use case is constructed is that in order to have that 17th extent of 128 blocks, one has to drop at least 16 _consecutive_ tables.

Dropping tables the way Niall did (the _even_ ones) has no chance of producing 128 continuous blocks … all free space is in 8-blocks pockets.

Continuing to drop tables after that should’ve worked after reaching 16 _consecutive_ tables … so it is a bug.

Now, I can make it work by dropping from the back … that is dropping partitions 17 thru 32:

SQL> drop tablespace auto_alloc_test including contents and datafiles;

Tablespace dropped.

SQL> create tablespace auto_alloc_test
  2  datafile 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\ALPHA\auto_alloc.dbf' size 32832k reuse
  3  extent management local;

Tablespace created.

SQL> begin
  2  for i in 1..32 loop
  3  execute immediate 'create table table'||i||'(col1 number,col2 number) tablespace auto_alloc_test';
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> begin
  2  for i in 1..15 loop
  3     for j in 1..32 loop
  4             execute immediate 'alter table table'||j||' allocate extent';
  5     end loop;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> begin
  2  for i in 17..32 loop
  3       execute immediate 'drop table table'||i;
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> alter table table1 allocate extent;

Table altered.

SQL>

SQL> col segment_name format a20
SQL> set pages 100 lines 120
SQL> select segment_name,block_id,blocks from dba_extents
  2  where owner='GABE' and segment_name like 'TABLE%' order by block_id;

SEGMENT_NAME           BLOCK_ID     BLOCKS
-------------------- ---------- ----------
TABLE1                        9          8
TABLE2                       17          8
TABLE3                       25          8
TABLE4                       33          8
TABLE5                       41          8
TABLE6                       49          8
TABLE7                       57          8
TABLE8                       65          8
TABLE9                       73          8
TABLE10                      81          8
TABLE11                      89          8
TABLE12                      97          8
TABLE13                     105          8
TABLE14                     113          8
TABLE15                     121          8
TABLE16                     129          8
TABLE1                      137        128
TABLE1                      265          8
TABLE2                      273          8
TABLE3                      281          8
….

Note: dropping tables 16 thru 31 won’t work!
 

Niall Litchfield, July 22, 2005 - 2:15 am UTC

Gabe, I guess really my point was that - in my test - Oracle is determined to use 128 blocks for the next extent for table1, apparently whether or not it is there. A 'smarter' routine could try and allocate 128 blocks, fail then try and allocate 8 and succeed. Maybe. Still make sure all extents were from the same list of sizes, just not be so rigorous about which size it picks when.

Joel, looks bug like to me.

Tom, absolutely this is an extreme case and likely a case of bad app design (though we run an ERP app that creates and drops 'temp' tables as if DDL is in danger of atrophy through underuse or something). I did intend to be clear that you needed the bad behaviour going on, *and* to be extremely low on space to get fragmentation to be a problem.

It occurs to me that I have never run this with truncates instead of drops (which *might* be a more common test case). I'm off on holiday now though so I'll probably forget for another couple of years.

Tom Kyte
July 22, 2005 - 8:52 am UTC

truncate does same, I tried it

Rationale

Jonathan Lewis, July 22, 2005 - 2:38 am UTC

Niall,

Addressing two points here, yours and Joel's.

The easy one is the "won't allocate" 128 blocks even though they are there. A detail of the auto-allocate algorithm that keeps wasted holes to a minimum is that a 1MB extent HAS to start on a 1MB boundary of the file, an 8MB extent HAS to start on an 8MB boundary ...

The harder one (your 'why fail when you could reduce the target') is less mechanical. The aim of autotallocate is to avoid creating extreme numbers of extents, whilst simultaneously avoiding extreme amounts of wasted space. If Oracle took your view of "I can't do 128 blocks, but there's a hole for 8", when should it stop ? I've lost sight of your worked example but in principle, between you and Joel, you could have ended up with a segment of 16 * 31 extents of 8 blocks each - and could have kept on going if you'd wanted to. In effect, Oracle has taken the option to fail RIGHT NOW rather than waiting until your tablespace was a horrid mess and then failing.

As a final thought - in what sort of production system do you create 32 small tables and drop 16 of them ? Possibly in a production system that's going to be creating more small tables quite soon. And if you make that assumption (as an ASM developer) it's perfectly reasonable to make ASM behave as your example shows.



woo hoo! We found a bug!

Joel Garry, July 22, 2005 - 10:24 am UTC

(and thanks to Jonathan for the clarification).

The truncate variant of this is disturbing. I've seen similar effects in production environments, but always just assumed I goofed up somewhere, or a doc bug for truncate. And of course, any allocation error is going to mean "fix it NOW" without much time for reflection on what is really happening, so you just add or extend a datafile, or recreate the table, and move on. I'd think such an obvious bug would've been explicated by now, it's been quite a while...

Tom Kyte
July 22, 2005 - 10:27 am UTC

hold on -- especially based on Jonathan's inputs and analysis, very good chance this is "not a bug".

I agree, it deserves looking at

But may very very well NBAB -- not be a bug.



definition of [file] boundary

Gabe, July 22, 2005 - 10:40 am UTC

Jonathan,

Can you expand a bit on the 128K [file] boundary for a 128K extent?

In my test case above the 17th extent (of 128K) for Table1 starts at block_id=137 which is after 128+8. I’m assuming the first 8 blocks of the tablespace are used for the bitmap … since the first extent for Table1 starts at block_id=9. So is that boundary your mention file-based? … or block-based [and offset by the blocks taken by the bitmap]?

Thanks.

Different type of fragmentation?

Holger, July 22, 2005 - 10:54 am UTC

I've not been in the industrie long enough from suffering fragmentation the 'old style', but I thought an important aspect was that the extents where of a highly volatile size. Now the example shows that the tablespace can seem to have wholes in it, however, these are easily to come by:

SQL>  begin
  2    for i in 1..32 loop
  3      if i mod 2=1 then
  4      execute immediate 'alter table table'||i ||' move';
  5      end if;
  6    end loop;
  7* end;
SQL> /

Since all the extents are of 'compatible' sizes, moving the tables coalesces the free extents.

Or am I completely off the track? 

Tom Kyte
July 22, 2005 - 11:00 am UTC

do you mean these are easily "overcome"?

I wouldn't move tables at the drop of a hat like that. (indexes must be rebuilt, extra work)

This is an extreme case here.

OK, Lets modify the advice:

If you plan on creating/dropping a set of tables frequently or truncating them
then
a) reconsider that, bad idea. see if global temporary tables wouldn't
work better, see WHY you THINK you need to do this.
b) if after reconsidering, you say "we need to", then put them into their
own super special tablespace with perhaps uniform extent sizes if you
can figure out the perfect size for all, this is scratch space by
definition, consider it like you would temp.
else
use old advice
end if

Table movement

Holger, July 22, 2005 - 11:23 am UTC

Ok, point taken. 

I surely didn't want to suggest to move tables at your leisure, but if after an extreme usage of a tablespace you
see much space scattered around, moving the tables might
be an option. 

SQL> select * from dba_lmt_free_space where tablespace_id = 16;

TABLESPACE_ID    FILE_ID   BLOCK_ID     BLOCKS
------------- ---------- ---------- ----------
           16         18         33         16
           16         18         65         16
           16         18         97         16
           16         18        129         16
           16         18        161         16
           16         18        193         16
           16         18        225         16
           16         18        257         16
           16         18        289         16
           16         18        321         16

.
.
.

My point was that with LMT you'll actually coalesce the 
free space (something that wouldn't necessarily happen with DMT, I think read about this in Jonathan Lewis' book), however after writing all this I realize that this is not exactly what this thread is all about, so sorry for going off track.

 

Tom Kyte
July 22, 2005 - 12:54 pm UTC

no worries -- and LMT's do not need "coalescing", that just happens.

But -- the move would "tend to make non-contigous free extents, contigous". Tend, not assure. But yes..

OK, not a bug but...

Joel Garry, July 22, 2005 - 2:39 pm UTC

does this mean we need to defr^H^H^H^H realign our segment boundaries in autoallocate tablespaces when sum(bytes) from dba_free_space is much greater than max(bytes)? 

SQL> select sum(bytes)/1024/1024 free_mb from dba_free_space where tablespace_name='AUTO_ALLOC_TEST';

   FREE_MB
----------
        16

SQL> select max(bytes) from dba_free_space where tablespace_name = 'AUTO_ALLOC_TEST';

MAX(BYTES)
----------
     65536

 

Allocating extents in advance

Khalid, September 04, 2005 - 8:24 am UTC

Tom,
We are planning to migrate some tables from one tablespace to another and we have calculated the amount of space that will be needed by each of the tables. The tablespace is locally managed and extent allocation method is autoallocate wih initail set as 64k. I have been asked to create these new tables so that their entire data is accomadated in only one extent , now i tried running some test cases and it seems that since the allocation method is autoallocate i do not have much control over the size of extents. below are some test cases

hr@ORCL.US.ORACLE.COM> create table test( a number )
2 storage ( initial 512k );

Table created.

hr@ORCL.US.ORACLE.COM> select EXTENT_ID, BYTES, BLOCKS from user_extents where
2 SEGMENT_NAME = 'TEST'
3 /

EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
0 65536 8
1 65536 8
2 65536 8
3 65536 8
4 65536 8
5 65536 8
6 65536 8
7 65536 8

hr@ORCL.US.ORACLE.COM> drop table test;

hr@ORCL.US.ORACLE.COM> create table test( a number )
2 storage ( initial 16M );

Table created.

hr@ORCL.US.ORACLE.COM> select EXTENT_ID, BYTES, BLOCKS from user_extents where
2 SEGMENT_NAME = 'TEST'
3 /

EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
0 1048576 128
1 1048576 128
2 1048576 128
3 1048576 128
4 1048576 128
5 1048576 128
6 1048576 128
7 1048576 128
8 1048576 128
9 1048576 128
10 1048576 128
11 1048576 128
12 1048576 128
13 1048576 128
14 1048576 128
15 1048576 128

My concern is that since Oracle follows its own algorithm in allocating these extents so would it be really helpful if i specify the storage clause alongwith the table creation DDL. How much of overhead will be incurred if i let Oracle allocate extents as and when the data is loaded rather than allocating these extents in advance ? To rephrase it in simpler words would *storage* clause be really helpful in this context ?

Tom Kyte
September 04, 2005 - 10:14 am UTC

I have been asked to create these new tables so that
their entire data is accomadated in only one extent , now i tried running some
test cases and it seems that since the allocation method is autoallocate i do
not have much control over the size of extents.


ouch, some 20 year old thinking in there.


You are CORRECT - with AUTOallocate, you do not have any control over the extent sizes.

And -- having data in one extent is not good. There, I'll just say it. Having one or few extents is bad. You shouldn't do it.

Not really, but having one or few isn't good, it isn't bad, it is mostly a waste of our time trying to achieve it is all.



IF you want to preallocate the storage for your segement, just use INITIAL <x> during the create, we'll take care of allocating the storage for you using an internal undocumented algorithm that you cannot really modify or control.


Or you can use a uniform sized one where you pick an extent size that allows for say a new extent added every month (as the table grows). That is, if you believe the table will grow by 10MB a month, use a 10MB uniform extent size.


But do not lose a seconds sleep over trying to get it into a single extent.

reader

A reader, October 20, 2005 - 4:36 pm UTC

From Oracle 9i concepts
"
Locally Managed Tablespaces
A tablespace that manages its own extents maintains a bitmap in each datafile to
keep track of the free or used status of blocks in that datafile. Each bit in the bitmap
corresponds to a block or a group of blocks. When an extent is allocated or freed for
reuse, Oracle changes the bitmap values to show the new status of the blocks.
"

Not very clear. Just LMT no ASSM, each bit in the 64k , is this 1 bit/extent or 1 bit /block or a combination


Tom Kyte
October 21, 2005 - 7:46 am UTC

"Each bit in the bitmapcorresponds to a block or a group of blocks"
^^


depends on whether we are talking uniform or non-uniform extent sizes.

reader

A reader, October 21, 2005 - 8:06 am UTC

It it is not uniform, does oracle finds the common smallest
denominator of the extents and register 0 or 1 in the
bitmap block.

Tom Kyte
October 21, 2005 - 10:14 am UTC

not documented, not of general interest to "us". You could try to dump blocks and reverse engineer it - but "why"?

does number of extents for a table matter in LMT

Venu Dandu, November 10, 2005 - 5:17 pm UTC

Hi Tom,

Scenario1: Planning to create one big table(having millions of rows) in LMT of uniform size 64k.

Scenario2: Same table in LMT with uniform size 128M.

Could you please tell me the scenario which gives the better performance in the above two scenarios?

Thanks,
Venu

Tom Kyte
November 11, 2005 - 12:01 pm UTC

the answer is

scenario three: same table in an lmt with system allocated extents.


that would be my choice.

64k is too small.
128m is probably too large.


starting with 64k and letting the system grow the size of the extents as the segment grows - gets you a nice solution without having to think too hard about it.

LMT & Object size

A reader, August 10, 2006 - 2:54 pm UTC

Recently, I've migrated from 8i to 10g. As all the old TBS were DMT I wanted to convert them to LMT. I created new LMT tablespace with 10M uniform extent size. Rebuilt some indexes. I saw one strange thing. One index which was appx 5g in size, now showing as 1.xg.

select segment_name, bytes, segment_type from dba_segments where segment_name = 'old index' -> 5g

select segment_name, bytes, segment_type from dba_segments where segment_name = 'new index' -> 1.xg

But tablespace usage in both versions is same i.e 5g.

SELECT bytes, count(*) FROM dba_extents WHERE SEGMENT_NAME= 'old index' group by bytes

909312,1
3612672,1
6823936,1
16900096,1
18350080,1
20152320,1
20299776,1
20971520,201
104857600,8

SELECT bytes, count(*) FROM dba_extents WHERE SEGMENT_NAME= 'new index' group by bytes

BYTES,COUNT(*)
10485760,458

Can't understand why dba_segments is showing 1.xg in 10g?

Uniform Size, performance, and ASM

Emad Kehail, October 17, 2006 - 6:24 am UTC

I have checked the Oracle Database 9.2.7 at my new work, and I have found the DBA has created all the tablespaces with uniform size of 152K. Why this number and why this way, he really did not answer me!

Anyhow, I would like to know if this setting could affect the performance of the queries from the IO perspective.

Moreover, I am planning to upgrade to Oracle 10g R2 with ASM. Could uniform size allocation of 1 MB be helpful here. I mean, ASM mirror at the extent level, and it also stripe data of 1 MB size. If we create all our tablespaces to be of 1 MB uniform size allocation, would it give more benefit to ASM and Oracle performance in general?



Tom Kyte
October 17, 2006 - 7:11 am UTC

152k, interesting.


doubtful it is affecting performance massively. extent allocation and deallocation is pretty fast with locally managed tablespaces. Only thing that might be affect could be full scans if the extent size is not a nice multiple of the multi block read count.

just use system allocated extents in the future.

Jan, December 28, 2007 - 7:43 am UTC

There is much discussion in this thread (and others) about autoallocate versus uniform size extents. I put together the following example which appears to leave empty space just sort of hanging in the middle of the tablespace. The empty space occurs where I drop the TEST2 tablespace. I realize that a new table could use the empty space that had been occupied by TEST2.

But if I don't have new tables being created in this tablespace, will the space remain empty and therefore be lost to "old tables" which are adding large size extents? This would seem like "wasted space" within the tablespace that could only be cleaned up via a reorganization. Is there anyway to encourage Oracle to reuse this space without something like an alter table move in the tablespace? Is this one reason to simply use a uniform size tablespace?

Is there a reason not to set a uniform extent size? Is the performance degradation that great in allocating new extents?

Thanks for the answers.

Jan, December 28, 2007 - 7:45 am UTC

I forgot to append the example!

create tablespace autoal datafile '/db7/oracle/data/test/autoal1.dbf' size 200M
extent management local autoallocate;

create table test (col1 varchar2(10)) tablespace autoal;

-- Put a bunch of rows in. Extent sizes increase from 64k to 1M to 8M (in this test).

begin
for x in 1..4500000 loop
insert into test values ('xxxxxxxxxx');
end loop;
end;
/
commit;

create table test2 (col1 varchar2(10)) tablespace autoal;

-- Put in a few rows. Extent size is 64k. 13 extents created.

begin
for x in 1..40000 loop
insert into test2 values ('xxxxxxxxxx');
end loop;
end;
/
commit;

-- Add more rows to test. Extent size continues at 8M.

begin
for x in 1..500000 loop
insert into test values ('xxxxxxxxxx');
end loop;
end;
/
commit;

--Create the hole in the tablespace.

drop table test2;
purge recyclebin;

--Add some more rows to test to verify the hole is not filled.

begin
for x in 1..500000 loop
insert into test values ('xxxxxxxxxx');
end loop;
end;
/
commit;

drop table test;

drop tablespace autoal including contents and datafiles;

Tom Kyte
December 28, 2007 - 3:44 pm UTC

we can use any space we feel like in an autoallocate tablespace - we can have extent sizes go up, down, all around - it is AUTO allocate.

I would not be too worried about it, the ease of the autoallocate - the benefits it provides - far far outweigh any chance of a small hole being an issue to keep me up at night.

Jan, December 29, 2007 - 8:40 pm UTC

What is the advantage of multiple extent sizes? From what I have read (from you and others), it doesn't seem that it matters if I have 20,000 extents (or any number for that matter). If that is the case, in a hypothetical case, why wouldn't I want all extents to be 128k for all tables (for arguement sake)? So I write out a whole bunch of extents for a large table. Is the performance loss that great for large inserts? (I don't know, that's why I ask.)

With autoallocate, we remove some of that performance loss, but again, I don't know how bad the performance loss is to begin with (maybe you know better, actually, I'm sure you do!) On the flip side, we run the risk of losing some space.

Now, I know the space loss only comes when we drop tables, and that probably isn't a common occurrence for our applications, and if it is, it probably belongs in its own tablespace. But, what is the true advantage of autoallocate versus a uniform extent size (whatever that extent size is)?
Tom Kyte
January 01, 2008 - 5:53 pm UTC

that is how ASM works actually (with many small extents - the striping is done at the extent level)

It is just space management wise somewhat "easier" in many peoples mind to have enough space being allocated at one time for lots of incoming data - so autoallocate just pre-allocates space for larger objects - preallocating more space as the objects get larger and larger.

I don't want to have to pick 128k or Xk or Yk - autoallocate is rather nice in that it just does it for me and works out quite nicely.

Does autoallocate choose a smaller extent size

A reader, February 07, 2008 - 7:53 pm UTC

To right-size extents in datafiles using autoallocate, will Oracle use smaller extent sizes?

I had the impression it started small, and got up to large extent sizes and never chose smaller again for that segment. For example 64K --> (through) --> 128M.

Will Oracle ever choose a smaller extent just to extent trim? Say, we grow a table so it is now allocating in 128M chunks. Will Oracle look at the tablespace extent map, and say "I can't allocate 128M, but I can fit another 3 more 64K extents in this file". Will it change back to the 64K extent size just to use all the space?
Tom Kyte
February 07, 2008 - 9:28 pm UTC

We don't know what it will do, it is "auto" allocate, subject to change, modification, to do what it wants.

crazy testing

A reader, February 08, 2008 - 6:52 pm UTC

Ok - I was a little curious to see if we'd downsize the extent size. The answer I got from the basic testing I did was "maybe". 

steps:
- create autoallocate tablespace
- create table in this tablespace as select * from all_objects
- self insert (insert into test select * from test) to grow fast

extents were as follows:
SQL>  select bytes,count(*) from user_extents group by bytes;

     BYTES   COUNT(*)
---------- ----------
   1048576         64
   8388608         75
     65536         16

The mapping followed logic - start small - get larger. I started with the 64k, next 1m, then 8m. 

I ran my self populate till ora-01653.

Made tablespace off-size - so I couldn't fit another 8m extent. 

alter table test allocate extent;

added a 1m extent. (max(extent_id))

repeated until another ora-01653. I had enough for more 64K extents, but Oracle chose not to go there. 

So, I ended up with 
extents 0-16 64k
extents 16-78 1m
extents 78-(ora-01653) 8m
allocate manually 
next extents are 1m until ora-01653

Just for giggles, I "inserted into the test table select * from all_objects" so I had data all the way up to the end of my tablespace, exported, dropped table, and imported. 

On import I got a ora-01653 - and a partial load. Seems like it wouldn't downsize the extents automatically once it chose a larger size. 

Probably useless info in the real world, but I was curious to see what the db would do when left to its own device. 

System Vs Local

anupam, December 28, 2008 - 5:43 am UTC

Hi Tom ,
I executed following commands in my DB.

create tablespace local_managed
datafile 'C:\ORANT\ORADATA\WORK\lmt01.dbf' size 100 M uniform size 2 M;

create tablespace system_managed
datafile 'C:\ORANT\ORADATA\WORK\lmt02.dbf' size 100 M ;

select tablespace_name,sum(bytes/1024/1024)
from dba_free_space
where tablespace_name in ('LOCAL_MANAGED','SYSTEM_MANAGED')
group by tablespace_name;

TABLESPACE_NAME SUM(BYTES/1024/1024)

LOCAL_MANAGED 98
SYSTEM_MANAGED 99.9375


Why is this difference in free space size ?

Thanks And Regards,
Anupam
Tom Kyte
December 29, 2008 - 3:40 pm UTC

because you forgot to added the 64k used by us to manage the space.

So, your 100m file is 100m - 64k. When you have 100m - 64k you only get 49 2mb extents, not 50. You waste 2mb-64k of space.

Yet another reason to never use uniform sized extents, there are many and this is one of them.

local auto vs local uniform

prasad, May 26, 2009 - 10:06 am UTC

Tom

You have mentioned in your post one of many reasons not to use uniform. I agree. I wanted to know how local auto is better than local uniform logically. Is it going to create performance issues, just trying to understand why local auto is prefered that local uniform. Are too many extents in local uniform can be bad for performance, not really sure. When I have tested with data it seems response time seems to be almost same. Not sure what would be the problem with local uniform. I am asking this question for large tables and we are not sure about the growth.


Thanks,
Prasad.

Tom Kyte
May 26, 2009 - 10:15 am UTC

It has nothing to do with performance - there are no performance reasons really to use one over the other.

It has everything to do with ease of use. Say the developers give you an application with 500 tables. Say they also just sort of shrug their shoulders when you ask for sizing information. Ok, now, take those 500 tables and using uniform tablespaces - allocate them so as to not have hundreds of thousands of extents ultimately - and to not waste space.

Also, if you do anything parallel...
https://www.oracle.com/technetwork/issue-archive/2007/07-may/o37asktom-101781.html


tell you what - why don't you (since you already know about the lack of performance related stuff) tell us why uniform might be superior to auto-allocate? I've already listed why I prefer auto-allocate.

A reader, June 13, 2009 - 4:40 am UTC

Respected Mr Tom;

I took a full export from oracle 7 and tried to full mport into new 9i database.
The size of oracle 7 is 15G.

Before the import process I created tablespaces with uniform size 32M ,2M ,4M , etc...
Then I start the import process and notice that the size of the database increased enormously.
The sum of the used part of the entire tablespaces was 25G!!!! Eventually I run out of space.
I used the script you suggested:
http://asktom.oracle.com/tkyte/Misc/free.html
The thing is
when I checked the sum of the used part of the all tablespaces there is an enormous difference!!!!!!!!!
The total size may differ but used part always be same. Am I wrong?


Then I dropped all tablespaces and recreated them with default oracle AUTOALLOCATE.
And reimport.
The entire size is normal now(around 15G),not much diffrence with the db that I exported.

What is the reason for this?
Tom Kyte
June 15, 2009 - 11:57 am UTC

why did you use uniform sizes??

you do understand that an empty table in a 25mb uniform tablespace would consume.... 25mb - an EMPTY table would.

so, tell us, why did you do what you did? Why didn't you use autoallocate?



The reason for this should be obvious??? We did what you asked us to do - you used really large extent sizes and we obeyed you (with uniform)

with autoallocate we used 64k extents initially and things were smaller.

A reader, June 20, 2009 - 9:36 pm UTC

Sir
The total size may differ but used part should be same. Am I wrong? so why there is an enourmous diffrence between used part?

Tom Kyte
June 20, 2009 - 10:43 pm UTC

I don't know what you mean, I don't know how you measured "used part" or what "used part" means to you.



to me, the "used" part of a tablespace is the "allocated space" and if you have 100 EMPTY tables in a uniform tablespace with 25mb extents - you would have 100x25mb of storage "used" in the tablespace - that should be "obvious"

(well, until a new release comes out - then things might be different - but right now, 11gr1 and before - 100 'normal' tables in a 25mb uniform extent size tablespace will consume 100x25mb of space in that tablespace.

A reader, June 27, 2009 - 11:20 pm UTC

Thanks Sir.
I still confused the terms "used" and "free".
Assume I have 100 empty tables with 25mb extents. Since these tables are empty, I was thinking I have 100*25mb free space.When the data inserted into these tables,it will become used.
Tom Kyte
July 06, 2009 - 5:13 pm UTC

give some context.

context: tablespace level free space report

if you have 100 tables each with ONE 25mb extent allocated to them and the tablespace has 1gb of space allocated then:

tablespace USED space = 100 tables * 25mb = 250mb of used (allocated space)
tablespace FREE space = 1gb - 250mb = 750mb of free space for new segments/extents


context: table level 'free space report'

table has one 25mb extent
table has zero rows
table therefore has 25mb free space



You are almost certainly creating a tablespace free report - and the free space in a tablespace is ALLOCATED SPACE TO TABLESPACE minus SPACE ALLOCATED TO SEGMENTS IN TABLESPACE

A reader, July 11, 2009 - 10:08 pm UTC

Thanks Sir, Now I am clear.I am using your famous script for tablespace level report ( http://asktom.oracle.com/tkyte/Misc/free.html ).
Do you have anything like this for table level report?
Tom Kyte
July 14, 2009 - 5:24 pm UTC

I don't know what it would look like for a table... Tables are not anything remotely like a tablespace.

I do have "showspace", search for that, it works on segments.

Autoallocate vs. Uniform - Revisited in 11.2

Gary Wicke, October 03, 2012 - 6:12 am UTC

Hi Tom

I realize this is an old thread but it seemed to fit my question the best.

I was having a discussion with a fellow Oracle DBA concerning the 'current' difference between Autoallocate and Uniform extent size management. By 'current' I mean in Version 11.2 of the database.

He was now recommending the use of 'Uniform' extents because starting in Version 11.2 an empty table does not allocate the one (1) extent it used to in prior releases but does the 'deferred allocation' thing.

He also commented that the 'main' reason the Autoallocate feature was developed was to reduce the size of the allocated but unused space in a tablespace for empty tables. The thinking being that one 64k extent was much better (i.e. smaller) than one of the uniform sized extents that was typically much larger than 64k.

I know you've argued for the use of the Autoallocate feature in the past but was wondering if your attitude has changed with Version 11.2 and the deferred allocation feature.

Thanks very much.

-gary
Tom Kyte
October 09, 2012 - 11:52 am UTC

He was now recommending the use of 'Uniform' extents because starting in
Version 11.2 an empty table does not allocate the one (1) extent it used to in
prior releases but does the 'deferred allocation' thing.


that is entirely optional.


and why would that have any bearing on the choice of autoallocate versus uniform? I don't see how that even comes into consideration.



He also commented that the 'main' reason the Autoallocate feature was developed
was to reduce the size of the allocated but unused space in a tablespace for
empty tables. The thinking being that one 64k extent was much better (i.e.
smaller) than one of the uniform sized extents that was typically much larger
than 64k.


not really. it was invented to make things more self managing.

and it isn't always 64k, try creating a partitioned table for example...

I prefer autoallocate in most all cases.