Thanks for the question, Rahul.

Asked: October 11, 2004 - 10:12 am UTC

Last updated: June 21, 2012 - 8:51 am UTC

Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom!

Pls tell me how does the "autoallocate" option allocate the extents to the LM tablespace. Are the extents allocated in some pattern, or according to the transaction or what?

You see, my concern is, if i create a small tablespace, say, 200m and then how are the extents actually gonna allocated to the tablespace........

Pls clarify this concept to me.

Thanks in advance!

and Tom said...

when you use something starting with "A" for auto -- that means it just happens.

It is not documented.
It will change over time.
It is "automatic"

In my car with an automatic transmission -- at which speed will the car shift gears? what causes a downshift? I don't know -- every car is a little different -- even the same model over years is different. It is "auto".

In my car with a manual transmission -- I decide (and change the speed at which I shift all of the time).

Same concept.

Here is a short excerpt from my book "Effective Oracle By Design" in which I describe how this seems to work although you can and will *see different results from time to time*.

The algorithm starts out with small extents and grows them over time for the segment.

And remember, having hundreds or thousands of extents is healthy and good for a segment!

<quote>

Use System-Managed LMTs When You Do Not Know How Big Your Objects Will Become

With a system-managed LMT, the system figures out exactly what the extent sizes will be for a table. Oracle will use an internal, undocumented algorithm to allocate space for every object in the tablespace. With this method, the first couple of extents will be small (64KB), and then the extents will get larger and larger over time. They will all be multiples of each other. That fact precludes free space fragmentation in these tablespaces, because every chunk of free space is potentially usable by any other object. This is in contrast to a DMT, where an object will request arbitrary extent sizes, and if there is not a contiguous free extent large enough to satisfy the request, the request will fail. It is true that in a system managed LMT, since there is more than one extent size, a request for space may fail even if there is existing free space. It is however many times less likely - due mostly to the fact that there is a very limited number of extent sizes. It would be rare to have free space that is not usable in a System managed LMT and in general, the space will be very small.

In the beginning, I was a little cautious about using this type of tablespace. It just didn't feel right. I was always taught that we should size our objects, watch their space usage like a hawk-micromanage the tablespace. Maybe I'm getting lazy, but I would prefer not to work that way if I can avoid it. With system-managed LMTs. I'm not kept awake at night wondering if a PCTINCREASE is going to go crazy on me or if my tablespace will be fragmented like Swiss cheese, with a lot of different-sized holes in it. Objects grow in a sensible fashion, without a lot of watching or handholding.

To see how space might be allocated in such a tablespace, let's use BIG_TABLE again (as noted in the appendix on "setting up", this is a table created from ALL_OBJECTS and duplicated over and over to have quite a few rows). For this demonstration, I made a copy of this table in an auto-allocate LMT.

big_table@ORA920> create tablespace SYSTEM_MANAGED
2 extent management local;

Tablespace created.

big_table@ORA920> create table big_table_copy
2 tablespace SYSTEM_MANAGED
3 as
4 select * from big_table;

Table created.

big_table@ORA920> select tablespace_name, extent_id, bytes/1024, blocks
2 from user_extents
3 where segment_name = ' BIG_TABLE_COPY'
4 /

TABLESPACE_NAME EXTENT_ID BYTES/1024 BLOCKS
------------------------------ ---------- ---------- ----------
SYSTEM_MANAGED 0 64 8
SYSTEM_MANAGED 1 64 8
...
SYSTEM_MANAGED 14 64 8
SYSTEM_MANAGED 15 64 8
SYSTEM_MANAGED 16 1024 128
SYSTEM_MANAGED 17 1024 128
...
SYSTEM_MANAGED 77 1024 128
SYSTEM_MANAGED 78 1024 128
SYSTEM_MANAGED 79 8192 1024
SYSTEM_MANAGED 80 8192 1024
...
SYSTEM_MANAGED 91 8192 1024
SYSTEM_MANAGED 92 8192 1024

93 rows selected.

As you can see in this example, the first 16 extents each are 64KB (but don't be surprised if you see something slightly different). The next 63 are each 1MB, and the remaining 14 are 8MB. As the object grew, the extents grew as well. That is a total of about 180MB of space in 93 extents, which is perfectly acceptable. For those who believe you must have your objects in one extent, I hope that this Oracle-supported algorithm, which promotes and encourages multiple extents, puts that notion to rest.

In this example, when we quadrupled the size of the BIG_TABLE_COPY to about 650MB, Oracle added another 64 extents, each 8MB, for a total of 512MB more. That table was just taking care of its space needs.

When you do not know how big your objects will become, system-managed LMTs, are the way to go. This type of space management is most useful when you are creating objects for an application, and depending on how the people using the application configure it, some tables might be empty, some might be 100MB, and some might be 2GB. On another installation, the tables that were 2GB are empty, the tables that were empty are 100MB, and the tables that were 100MB tables are 2GB. In other words, you have no idea how big these tables are going to be in the real world. Here, having each object start at 64KB and stay that way for the first couple of extents lets the tables that are nearly empty stay very small. The tables that are going to get large will get large fast.

If you are using Oracle9i Release 2 with an LMT, you'll find that it uses system-managed extent sizing for the SYSTEM tablespace. For the SYSTEM tablespace, this extent-sizing strategy is the best thing ever. It will prevent dictionary tables from growing exponentially (as PCTINCREASE would have them doing after a while; even a small PCTINCREASE would cause a table to grow by huge amounts after a short period of time) and keep the number of extents at a reasonable maximum. Consider that if you install Oracle and use a ton of PL/SQL, your SYS.SOURCE$ table and the tables that hold the compiled PL/SQL will be huge. On the other hand, your friend may install Oracle and not write any lines of PL/SQL code. With the system-managed approach, Oracle will let the database figure out how big the extents should be.

System-managed LMTs work well, as long as the objects are destined to be 10GB or less. At 10GB, you would be using about 300 extents for the object in this type of tablespace, which is fine. Segments that exceed 10GB are fairly rare. If you are using partitioning, the individual partitions are the segments; 10GB partitions or smaller would be a good size. For tables and indexes that are getting into the 10GB size range, consider partitioning them into smaller, more manageable segments. For segments larger than 10GB, or for those that you prefer to size yourself for some reason, consider using LMTs with uniform extent sizes.
</quote>

Rating

(19 ratings)

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

Comments

automatic transmission

Connor, October 12, 2004 - 10:06 am UTC

Yes but even with an auto car, there's a red line on the rev counter, which means no matter what gear the automatic has chosen, you always know *in advance* how hard you can push the car before it will reach a cutout point.

With auto-alloc lmt's, how do you *know* when you're out of free space in a tablespace. By the definition of 'undocumented algorithm', ANY amount of free space might not be enough. You might have 10 terabytes of free space in a bigfile tspace, but for all we know, the next extent size is 11 terabytes.

An 11 tb next extent is *unlikely* but 'unlikely' is not a guarantee, its a hope.

I don't really what the algorithm is, or even particularly want to know, but Oracle should publish a ceiling extent size, because without that, you never know if you've got enough space for a segment to extend.

Next extent does not seem to change

Rob, November 09, 2004 - 12:51 pm UTC

Tom:
Version 9.2.0.4
It appears that once an LMT with extents set to autoallocate reaches a large size, any new segment created in that tablespace has it's initial extent created in that large size. We have a dev environment with many dev schemas. These schemas get dropped and recreated somewhat frequently as a way of having each developer on the most current data model during the dev cycle. All schemas share one autoallocate tablespace. It appears that the tablespace which is about 16 GB is now creating all new segments with an initial allocation of 1 MB even if the segment will be miniscule. This seems to obviate some of the usefulness of autoallocate as a way of accomodating large and small segments in the same tablesapce.

November 09, 2004 - 1:32 pm UTC

i have not observed that personally -- but, it seems each schema should have a tablespace, you drop the schema, shrink the files down and let them autoextend back out - it would erase any sort of "memory" associated with this technique.

uniform vs auto allocate

Nishant, February 02, 2005 - 8:14 am UTC

Sir,
i used to think that it is always better to have equalent extent size in a segment to have the best performace out of it ...with uniform size

but autoallocate is just opposite ..

can you please clear up me doubt .. which one is better and how ?
hope you wont mind answering such questions

thanx in advance

February 02, 2005 - 8:25 am UTC

why did you think that?

then maybe I can address your concerns.

it isn't true, it was never true. same sized extents were prefered in dictionary managed tablespaces to avoid fragmentation.

system allocated extents take care of it for us, fragmentation is not the concern it was, and segments can start small and grow big as needed (easier to manage)

anto, February 02, 2005 - 4:35 pm UTC

Ours is a datawarehouse and we have quite a few segments having number of extents greater than 1000, but we did not bother to reorganize those segments, since we don't have any performance issue due to the large number of extents. By reorganizing, you might gain if you are able bring down the high water mark, but not due to reduction in number of extents

Auto Allocate or not auto-allocate

Pankaj Mandalia, April 21, 2006 - 11:04 pm UTC

This thread is more confusing than enlightening!
On one hand Tom suggests letting Oracle do all the housekeeping by LMT and AUTO ALLOCATE. One of the readers points out a very concerning scenario where over time the initial extent size gets out of control. Tom counter suggests to drop and recreate the tablespace/schema. For PeopleSoft I am not sure that is possible where sysadm owns over 36,000 objects. And the database is originally created with LMT's and auto allocate. Within PeopleTools (the development environment of PeopleSoft, something equivalent to a combination of Designer6i and Developer6i)there is a facility that is still creating "build objects" scripts with specific initial and next extent sizes but apparently that gets washed out because of LMT/Auto. I have an original object that was created with 4K initial and 10K next with 0 pct free and max_extents unlimited. After a few months' use, the table became over 200 extents. When I tried to check the initial and next through the dba_segments, the value in next_extent column was null and the object showed 218 extents. I used the PeopleTools facility to 'Alter' the object (basically it creates a script to: create new table with new storage clause, inserts the rows of the original table into the new table, drops the original table, and renames the new table to original table. Then the script is run in sql*plus). The dba_segments STILL shows 218 extents which is impossible because the new initial and next extents were specified to be 4MB each. I have two questions:
First: why is the next_extent column (in dba_segments, user_segments, dba_tables, user_tables) everywhere empty?
Second: Why does the number of extents still show the original value when it should show lower value?
Thank you !

April 22, 2006 - 3:10 pm UTC

where did I say that - I see me saying "not in my experience, I've NEVER seen that"

read my original response, I feel I was rather unambigous and clear in my statement.

Why a 10GB limit is suitable?

Naresh, April 23, 2006 - 1:45 am UTC

HI Tom,

Why do you say:

"System-managed LMTs work well, as long as the objects are destined to be 10GB or less. At 10GB, you would be using about 300 extents for the object in this type
of tablespace, which is fine"

There should not be any problem with going beyond 300 extents, right? So why consider 10GB or less as suitable?

Thanks,
Naresh.

April 23, 2006 - 5:32 am UTC

If a segment is going beyond 10gb in size, it is getting larger than I want to manage as a single unit of storage.

Meaning, when the segment is getting to be 10gb in size and beyond - it is time to consider partitioning for administrative purposes.

Can you get beyond 10gb? Sure, yes.
Do you want to? Probably not, for administrative reasons (backup, recovery, moving, reorganizing if needed...)

FYI - Results of Creating a Large Table into an LMT

Tim Sawmiller, August 16, 2006 - 1:45 pm UTC

Just for my own edification, I created an LMT with auto allocate.  Since I knew the table was large (95 gig!), I created the tablespace large (120 gig).

create tablespace H_AA
datafile 'DBSDATA8:[XXXX.YYYY]H_AA_01.DBF'
size 30000M
reuse
extent management local 
segment space management auto
/
alter tablespace H_AA
add datafile 'DBSDATA8:[XXXX.YYYY]H_AA_02.DBF'
size 30000M
reuse
/
alter tablespace H_AA
add datafile 'DBSDATA8:[XXXX.YYYY]H_AA_03.DBF'
size 30000M
reuse
/
alter tablespace H_AA
add datafile 'DBSDATA8:[XXXX.YYYY]H_AA_04.DBF'
size 30000M
reuse
/
alter table C_E move tablespace H_AA parallel 50
/
Ended up with 9,279 extents.
Interesting how it was stuck for awhile at the 1 meg level, then the 8 meg level, and finally at the 64 meg level.


  1  select bytes/1024/1024 Megs, count(1) Extents  from user_extents
  2  where segment_name = 'C_E'
  3* group by bytes/1024/1024
SQL> /

           MEGS     EXT
--------------- -------
            .50       1
            .69       1
           1.00   2,451
           3.63       1
           7.00       2
           8.00   6,099
           9.00       2
           9.63       1
          10.69       1
          10.94       1
          12.13       1
          12.25       1
          13.56       1
          15.00       1
          18.44       1
          22.81       1
          25.19       1
          26.50       1
          27.06       1
          28.50       1
          34.50       1
          36.25       1
          37.69       1
          38.00       1
          38.31       1
          38.69       1
          39.00       1
          39.69       1
          41.38       1
          46.75       1
          46.88       1
          47.25       1
          47.56       1
          47.88       1
          48.00       2
          48.06       1
          48.13       1
          48.56       1
          51.63       1
          52.25       1
          55.00       1
          56.00       2
          56.13       2
          56.81       1
          57.13       1
          57.56       1
          57.63       1
          57.75       1
          57.88       2
          63.56       1
          64.00     675
------------------   -------
                      9,279

August 16, 2006 - 3:55 pm UTC

each of the 50 parallel execution servers started with the 64k, to the 1m, to the 8m.

serial would have been very different.
so would differing degrees of parallelism.

so, it is more akin to

13'ish 64k extents apiece
50'ish 1m extents apiece
122'ish 8m extents apiece

then, each had their last extent trimmed back (autoallocate permits the extent trimming)

proprietary algorithm of LMT Autoallocate

Denny Wong, September 27, 2006 - 12:58 pm UTC

The "proprietary algorithm" of Autoallocate LMT is basically follows:

IF dba_segment.bytes < 1M THEN
next extent = 64K
ElSIF 1M <= dba_segment.bytes < 64M THEN
next extent = 1M
ELSIF 64M <= dba_segment.bytes < 1G THEN
next extent = 8M
ELSE
next extent = 64M

I prefer using Autoallocate LMT. It's maintenance free. Today's DBA have many new things to learn and manage. Why micro-manage about extents.

Denny Wong
dennywong01@yahoo.ca
DB Dolphins Inc.

September 27, 2006 - 4:10 pm UTC

nope, that is not the algorithm, it varies based on space available too, space requested, other things (and can change and will change and has changed).  You are writing what you have "observed" on "your system" given "your space".  

That is sort of like saying "group by x, y sorts by x, y" because you have always observed it to - but it does not (sort by x,y) in general....

but I agree with your last sentiments entirely however




ops$tkyte%ORA10GR2> select tablespace_name, extent_management, allocation_type
  2  from dba_tablespaces where tablespace_name = 'USERS';

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
USERS                          LOCAL      SYSTEM

ops$tkyte%ORA10GR2> select segment_name, tablespace_name
  2  from user_segments where segment_name = 'T';

SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
T                              USERS

ops$tkyte%ORA10GR2> select blocks, count(*) from user_extents where segment_name = 'T'
  2  group by blocks order by blocks;

    BLOCKS   COUNT(*)
---------- ----------
       128         10

there is a table.... auto allocate, but no 64k extents...

Roland, September 28, 2006 - 8:26 am UTC

Oracle Magazine did an article on Locally Managed Tablespaces back in 2000 November/December issue, page 90. It says:

"If you create a new table, the first extent will be 64KB, until the table reaches 1MB in size. At that Point the size of the subsequent extents for the table will be increased to 1MB in size. When the table reaches 64MB in size, the extent size will be increased again to 8MB. Finally, if the table reaches 1GB, the extent sized will be increased one last time, to 64MB."

Is this not valid anymore?

September 29, 2006 - 7:21 am UTC

they should not have said that obviously. It just is not true, never was true, never has been true.

the author wrote what they observed. It is not always true. You cannot rely on it, you don't need to rely on it.

Roland, September 30, 2006 - 11:30 am UTC

Thanks Tom!

Preemptive allocation

Mike, February 26, 2007 - 2:10 pm UTC

I'm using LMT with auto-allocation, which works great. I'm trying to write an automated procedure that will check a table to see if the last extent is almost full, and try to allocate a new extent manually ahead of time before Oracle would do it (in case the tablespace might be full). If the allocate failed, then a message would be sent to the DBA.

I've done this, but the statement "Alter table <tablename> allocate extent" (with no size specified) isn't doing what I want it to do. In one case, even though the last 20 extents in the table are 64M each, the new extent is being allocated as 1M. I was hoping it would use it's algorithm to determine the next extent size. Maybe it is, but it doesn't make sense to me.

The documentation says that "For a table, index, materialized view, or materialized view log, if you omit SIZE, then Oracle Database determines the size based on the values of the storage parameters of the object."

Where did it get 1M for the next extent size, since dba_tables has an NULL for next_extent?

February 26, 2007 - 3:32 pm UTC

why?

why would you do this?????

Why?

Mike, February 26, 2007 - 5:11 pm UTC

Why would we do it? To catch and fix any possible extent allocation errors way ahead of time before they would happen during normal business hours.

February 26, 2007 - 5:21 pm UTC

just make sure that the tablespace has sufficient space, done.

I mean really - make sure the tablespace has empty space and that if possible, the files can autoextend and the filesystem has room.

with auto-allocate, the auto kicks in - you have no control over what the next extent will be.

If you are truly going down this path, you will use UNIFORM and micro-manage it to death - creating many tablespaces with different extent sizes.

But I wouldn't.

Next extent monitoring for autoallocate

Hiten Negandhi, May 20, 2008 - 1:01 pm UTC

We also use AUTOALLOCATE on some of our databases. We had "cannot allocate next extent" monitoring which worked fine for UNIFORM EXTENT type tablespaces. But, it does not work for AUTOALLOCATE tablespaces, since there is no NEXT EXTENT. When space is near full, there may be some segments that can extend (because they are small), but some that may not be able to extend (because they are comparatively large in size). How can we identify those large segments. Is it possible to get the MAX extent allocated for that segment and assume that Oracle is going to try to allocate the next extent based on that size?

May 20, 2008 - 3:42 pm UTC

nope, you just need to have a healthy "pad" there - maybe just query the largest existing extent and presume you want at least that much free - or maybe X times that amount.

(actually, I'm a fan of autoextend datafiles with a maxsize to ensure something doesn't run away, I run my tablespaces at 100% utilization, then just make sure the filesystem has sufficient free space - quite simple/simplistic)

Read Tom's Original Response

Tommy Petersen, January 28, 2009 - 11:05 am UTC

As Tom said, it is automatic.

I used to work with Oracle before LMT and I had a schema where the extent sizes for the tables were all over the map, and I ended up with lots of small free_space extents. (The application did a lot of truncate and load). After resizing the extents on all the tables to 64K, 1M or 32M it became much more manageable, small extents would be reused by small tables and I would have space for the large extents as well.

This is the same principle used for AUTOALLOCATE, except AUTOALLOCATE is better, by segments will allocate extents that are smaller than the prior extent, and I am able to use space until I have less than 1MB left.

Oracle is not strict with the extent sizes, the algorithm says to go from 1M to 8M extents, but I got an extent of 7M

EXTENT_ID BYTES
---------- ----------
78 1048576
79 7340032 <<<<<<<
80 8388608
later I got smaller extents as well
172 8388608
173 4194304 <<<<<<<
174 8388608
at the end
180 8388608
181 7340032 <<<<<<<<
I assume the smaller extents are just free_space extents that were encountered on the way.

I could not allocate from the last free_space that was less than 1M, but that is OK, whether I get to use the last 128K does not really matter, I will be out of space before the end of the day anyway.

With the cost of disk, you do not want to spend a lot of time finding out how far beyond "E" you can go.

next_extent

aliyar, April 10, 2011 - 11:12 am UTC

Hi Tom ,

Appreciate your Help For DBA world

Canu please clarify my folllowing dougt

from dba_segments , the columns pct_increase and next_extent shows null value for one of the table ...

Database : 10.2.0.4
O/s : HP-ux

is there any reason why the values are null ...

Thanks
Aliyar

April 12, 2011 - 4:01 pm UTC

probably because you are using locally managed tablespaces and those archaic settings are no longer used..

but give us a for example, tell us a bit about the table.

new configuration?

Aldo Ustariz, April 19, 2012 - 4:24 pm UTC

Tom,
Could you please tell us. What configuration replaces LMT nowadays. I am still using LMT on 10gR2.

April 20, 2012 - 3:53 pm UTC

Nothing, locally managed tablespaces are still the current 'state of the art'

I prefer system allocated extents over uniform for most all cases, but they are locally managed.

next_extent (aliyar)

GMARTINS, June 04, 2012 - 1:04 pm UTC

Hello Tom,

I´m still trying to clarify what Aliyar told about empty next_extent of table. I Have the same issue with UNDO tablespace. See below:

TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE MIN_EXTLEN
---------------- -------------- ----------- ------------ ----------
TS_UNDO_1 65536 65536 65536

** NEXT_EXTENT: empty
** PCT_INCREASE: empty

For what reason this happen?

Could you help me?

Thanks in advance! =D

June 04, 2012 - 1:13 pm UTC

do not worry about UNDO tablespaces , we control the storage 100% and you cannot change anything.

They are special. They are this way by design.

11g AuoAllocate algorithm

MB, June 19, 2012 - 1:37 pm UTC

Hi Tom

I cannot find any info on what I have observed lately on our new 11gR2 databases. If it's out there somewhere please send a pointer. We use ASM and set autoextend on. It appears that datafiles will now autoextend when they reach about 95 or 96% full rather than when they fill to 100% as they did in 10g. This can end up being a lot of unused space in a large database. Is there a way to change this behavior? I end up running your maxshink more often to reclaim space.

June 19, 2012 - 3:07 pm UTC

perhaps it just means your autoextend size is set too high?

what is your autoextend size?
what is the extent management of your tablespaces?
how big are the segments that are in general causing the autoextend?
how big are your existing files?
do you have more than one file per tablespace?

11 autoallocate algorithm

Matt, June 20, 2012 - 6:07 pm UTC

>what is your autoextend size?
VARIES usually 32M - 128M but does not seem to matter.

> what is the extent management of your tablespaces?
LOCAL

> how big are the segments that are in general causing the autoextend?
VARIES WIDELY

> how big are your existing files?
Varies WIDELY but I have not noticed it on anything < about 2GB

> do you have more than one file per tablespace?
Usually but I have noticed on single file tablespaces



Here's a test case in 11g and 10g.  Notice suddenly in 11g it has allocated way more than what is defined for NEXT (32M).

First 11.2.0.3.0:

SQL> select banner from v$version where banner like 'Oracle%';

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> 
SQL> CREATE TABLESPACE ts_11g_test DATAFILE
  2     SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 5120M
  3  LOGGING
  4  PERMANENT
  5  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  6  BLOCKSIZE 8K
  7  SEGMENT SPACE MANAGEMENT AUTO
  8  FLASHBACK OFF;

Tablespace created.

SQL> 
SQL> select
  2         round(fs.bytes/1024/1024,2) MB_free
  3  ,      round((df.bytes-fs.bytes)/1024/1024,2) MB_used
  4  ,      round((df.bytes-nvl(fs.bytes,0))*100/df.bytes,2) pct_used
  5  from dba_free_space fs, dba_data_files df
  6  where df.file_id = fs.file_id
  7  and df.tablespace_name = 'TS_11G_TEST';

   MB_FREE    MB_USED   PCT_USED
---------- ---------- ----------
        31          1       3.13

SQL> 
SQL> -- create a large table
SQL> create table big (c1 number, c2 char(2000)) tablespace ts_11g_test;

Table created.

SQL> 
SQL> -- insert 100K rows
SQL> insert into big select rownum, 'x' from dual connect by level <=100000;

100000 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> select
  2         round(fs.bytes/1024/1024,2) MB_free
  3  ,      round((df.bytes-fs.bytes)/1024/1024,2) MB_used
  4  ,      round((df.bytes-nvl(fs.bytes,0))*100/df.bytes,2) pct_used
  5  from dba_free_space fs, dba_data_files df
  6  where df.file_id = fs.file_id
  7  and df.tablespace_name = 'TS_11G_TEST';

   MB_FREE    MB_USED   PCT_USED
---------- ---------- ----------
        23        265      92.01

SQL> 
SQL> set serveroutput on
SQL> 
SQL> declare
  2    mb_free number;
  3    mb_used number;
  4    pct_used number;
  5  begin
  6    -- insert 100K at a time 16 times
  7    for v_count in 1 .. 16 loop
  8       insert into big select rownum, 'x' from dual connect by level <=100000;
  9       commit;
 10       select
 11              round(fs.bytes/1024/1024,2) MB_free
 12       ,      round((df.bytes-fs.bytes)/1024/1024,2) MB_used
 13       ,      round((df.bytes-nvl(fs.bytes,0))*100/df.bytes,2) pct_used into mb_free,mb_used,pct_used
 14       from dba_free_space fs, dba_data_files df
 15       where df.file_id = fs.file_id
 16       and df.tablespace_name = 'TS_11G_TEST';
 17       dbms_output.put_line(to_char(v_count)||': mb_free='||to_char(mb_free)||' mb_used='||to_char(
 18    end loop;
 19  end;
 20  /
1: mb_free=15 mb_used=529 pct_free=97.24
2: mb_free=7 mb_used=793 pct_free=99.13
3: mb_free=31 mb_used=1089 pct_free=97.23
4: mb_free=31 mb_used=1345 pct_free=97.75
5: mb_free=31 mb_used=1601 pct_free=98.1
6: mb_free=31 mb_used=1857 pct_free=98.36
7: mb_free=31 mb_used=2113 pct_free=98.55
8: mb_free=31 mb_used=2369 pct_free=98.71
9: mb_free=31 mb_used=2625 pct_free=98.83
10: mb_free=31 mb_used=2881 pct_free=98.94
11: mb_free=31 mb_used=3137 pct_free=99.02
12: mb_free=31 mb_used=3457 pct_free=99.11
13: mb_free=31 mb_used=3713 pct_free=99.17
14: mb_free=31 mb_used=3969 pct_free=99.23
15: mb_free=31 mb_used=4225 pct_free=99.27
16: mb_free=31 mb_used=4481 pct_free=99.31

PL/SQL procedure successfully completed.

SQL> insert into big select rownum, 'x' from dual connect by level <=100;

100 rows created.

SQL> select 
  2         round(fs.bytes/1024/1024,2) MB_free
  3  ,      round((df.bytes-fs.bytes)/1024/1024,2) MB_used
  4  ,      round((df.bytes-nvl(fs.bytes,0))*100/df.bytes,2) pct_used
  5  from dba_free_space fs, dba_data_files df
  6  where df.file_id = fs.file_id
  7  and df.tablespace_name = 'TS_11G_TEST';

   MB_FREE    MB_USED   PCT_USED
---------- ---------- ----------
       287       4481      93.98

W H O A !!!!  Suddenly it's allocated in a big chunk! 280M +/-


Now 10.2.0.5:


SQL> select banner from v$version where banner like 'Oracle%';

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

SQL> 
SQL> CREATE TABLESPACE ts_11g_test DATAFILE
  2     SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 5120M
  3  LOGGING
  4  PERMANENT
  5  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  6  BLOCKSIZE 8K
  7  SEGMENT SPACE MANAGEMENT AUTO
  8  FLASHBACK OFF;

Tablespace created.

SQL> 
SQL> select
  2         round(fs.bytes/1024/1024,2) MB_free
  3  ,      round((df.bytes-fs.bytes)/1024/1024,2) MB_used
  4  ,      round((df.bytes-nvl(fs.bytes,0))*100/df.bytes,2) pct_used
  5  from dba_free_space fs, dba_data_files df
  6  where df.file_id = fs.file_id
  7  and df.tablespace_name = 'TS_11G_TEST';

   MB_FREE    MB_USED   PCT_USED
---------- ---------- ----------
     31.94        .06         .2

SQL> 
SQL> -- create a large table
SQL> create table big (c1 number, c2 char(2000)) tablespace ts_11g_test;

Table created.

SQL> 
SQL> -- insert 100K rows
SQL> insert into big select rownum, 'x' from dual connect by level <=100000;

100000 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> select
  2         round(fs.bytes/1024/1024,2) MB_free
  3  ,      round((df.bytes-fs.bytes)/1024/1024,2) MB_used
  4  ,      round((df.bytes-nvl(fs.bytes,0))*100/df.bytes,2) pct_used
  5  from dba_free_space fs, dba_data_files df
  6  where df.file_id = fs.file_id
  7  and df.tablespace_name = 'TS_11G_TEST';

   MB_FREE    MB_USED   PCT_USED
---------- ---------- ----------
     23.94     264.06      91.69

SQL> 
SQL> set serveroutput on
SQL> 
SQL> declare
  2    mb_free number;
  3    mb_used number;
  4    pct_used number;
  5  begin
  6    -- insert 100K at a time 16 times
  7    for v_count in 1 .. 16 loop
  8       insert into big select rownum, 'x' from dual connect by level <=100000;
  9       commit;
 10       select
 11              round(fs.bytes/1024/1024,2) MB_free
 12       ,      round((df.bytes-fs.bytes)/1024/1024,2) MB_used
 13       ,      round((df.bytes-nvl(fs.bytes,0))*100/df.bytes,2) pct_used into mb_free,mb_used,pct_used
 14       from dba_free_space fs, dba_data_files df
 15       where df.file_id = fs.file_id
 16       and df.tablespace_name = 'TS_11G_TEST';
 17       dbms_output.put_line(to_char(v_count)||': mb_free='||to_char(mb_free)||' mb_used='||to_char(
 18    end loop;
 19  end;
 20  /
1: mb_free=15.94 mb_used=528.06 pct_free=97.07
2: mb_free=7.94 mb_used=792.06 pct_free=99.01
3: mb_free=31.94 mb_used=1088.06 pct_free=97.15
4: mb_free=31.94 mb_used=1344.06 pct_free=97.68
5: mb_free=31.94 mb_used=1600.06 pct_free=98.04
6: mb_free=31.94 mb_used=1856.06 pct_free=98.31
7: mb_free=31.94 mb_used=2112.06 pct_free=98.51
8: mb_free=31.94 mb_used=2368.06 pct_free=98.67
9: mb_free=31.94 mb_used=2624.06 pct_free=98.8
10: mb_free=31.94 mb_used=2880.06 pct_free=98.9
11: mb_free=31.94 mb_used=3136.06 pct_free=98.99
12: mb_free=31.94 mb_used=3456.06 pct_free=99.08
13: mb_free=31.94 mb_used=3712.06 pct_free=99.15
14: mb_free=31.94 mb_used=3968.06 pct_free=99.2
15: mb_free=31.94 mb_used=4224.06 pct_free=99.25
16: mb_free=31.94 mb_used=4480.06 pct_free=99.29

PL/SQL procedure successfully completed.

SQL> insert into big select rownum, 'x' from dual connect by level <=100;

100 rows created.

SQL> commit;

Commit complete.

SQL> select 
  2         round(fs.bytes/1024/1024,2) MB_free
  3  ,      round((df.bytes-fs.bytes)/1024/1024,2) MB_used
  4  ,      round((df.bytes-nvl(fs.bytes,0))*100/df.bytes,2) pct_used
  5  from dba_free_space fs, dba_data_files df
  6  where df.file_id = fs.file_id
  7  and df.tablespace_name = 'TS_11G_TEST';

   MB_FREE    MB_USED   PCT_USED
---------- ---------- ----------
     31.94    4480.06      99.29

SQL>

June 21, 2012 - 8:51 am UTC

I get slightly different output:

1: mb_free=15 mb_used=529 pct_free=97.24
2: mb_free=39 mb_used=793 pct_free=95.31
3: mb_free=63 mb_used=1089 pct_free=94.53
4: mb_free=31 mb_used=1345 pct_free=97.75
5: mb_free=31 mb_used=1601 pct_free=98.1
6: mb_free=31 mb_used=1857 pct_free=98.36
7: mb_free=159 mb_used=2113 pct_free=93
8: mb_free=95 mb_used=2369 pct_free=96.14
9: mb_free=31 mb_used=2625 pct_free=98.83
10: mb_free=159 mb_used=2753 pct_free=94.54
11: mb_free=95 mb_used=3137 pct_free=97.06
12: mb_free=31 mb_used=3457 pct_free=99.11
13: mb_free=31 mb_used=3713 pct_free=99.17
14: mb_free=31 mb_used=3969 pct_free=99.23
15: mb_free=31 mb_used=4225 pct_free=99.27
16: mb_free=31 mb_used=4481 pct_free=99.31

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into big select rownum, 'x' from dual connect by level <=100;

100 rows created.

ops$tkyte%ORA11GR2> select
  2         round(fs.bytes/1024/1024,2) MB_free
  3  ,      round((df.bytes-fs.bytes)/1024/1024,2) MB_used
  4  ,      round((df.bytes-nvl(fs.bytes,0))*100/df.bytes,2) pct_used
  5  from dba_free_space fs, dba_data_files df
  6  where df.file_id = fs.file_id
  7  and df.tablespace_name = 'TS_11G_TEST';

   MB_FREE    MB_USED   PCT_USED
---------- ---------- ----------
        31       4481      99.31

but it still demonstrates the issue - there was 159mb free in the file at one point.

I did some poking around (I learn something new every day :) ) and discovered it is a new 11g 'feature'...

See:

SMCO (Space Management Coordinator) For Autoextend On Datafiles And How To Disable/Enable (Doc ID 743773.1)

I disabled it:

ops$tkyte%ORA11GR2> ALTER SYSTEM SET "_ENABLE_SPACE_PREALLOCATION" = 0 scope=memory;

System altered.

and reran the test:

1: mb_free=15 mb_used=529 pct_free=97.24
2: mb_free=7 mb_used=793 pct_free=99.13
3: mb_free=31 mb_used=1089 pct_free=97.23
4: mb_free=31 mb_used=1345 pct_free=97.75
5: mb_free=31 mb_used=1601 pct_free=98.1
6: mb_free=31 mb_used=1857 pct_free=98.36
7: mb_free=31 mb_used=2113 pct_free=98.55
8: mb_free=31 mb_used=2369 pct_free=98.71
9: mb_free=31 mb_used=2625 pct_free=98.83
10: mb_free=31 mb_used=2881 pct_free=98.94
11: mb_free=31 mb_used=3137 pct_free=99.02
12: mb_free=31 mb_used=3457 pct_free=99.11
13: mb_free=31 mb_used=3713 pct_free=99.17
14: mb_free=31 mb_used=3969 pct_free=99.23
15: mb_free=31 mb_used=4225 pct_free=99.27
16: mb_free=31 mb_used=4481 pct_free=99.31

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into big select rownum, 'x' from dual connect by level <=100;

100 rows created.

ops$tkyte%ORA11GR2> select
  2         round(fs.bytes/1024/1024,2) MB_free
  3  ,      round((df.bytes-fs.bytes)/1024/1024,2) MB_used
  4  ,      round((df.bytes-nvl(fs.bytes,0))*100/df.bytes,2) pct_used
  5  from dba_free_space fs, dba_data_files df
  6  where df.file_id = fs.file_id
  7  and df.tablespace_name = 'TS_11G_TEST';

   MB_FREE    MB_USED   PCT_USED
---------- ---------- ----------
        31       4481      99.31

it goes back to the old behavior...

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

automatic transmission

Next extent does not seem to change

uniform vs auto allocate

Auto Allocate or not auto-allocate

Why a 10GB limit is suitable?

FYI - Results of Creating a Large Table into an LMT

proprietary algorithm of LMT Autoallocate

Preemptive allocation

Why?

Next extent monitoring for autoallocate

Read Tom's Original Response

next_extent

new configuration?

next_extent (aliyar)

11g AuoAllocate algorithm

11 autoallocate algorithm

More to Explore

VLDB

VLDB

How does "Autoallocate" allocate the extents?

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

automatic transmission

Next extent does not seem to change

uniform vs auto allocate

Auto Allocate or not auto-allocate

Why a 10GB limit is suitable?

FYI - Results of Creating a Large Table into an LMT

proprietary algorithm of LMT Autoallocate

Preemptive allocation

Why?

Next extent monitoring for autoallocate

Read Tom's Original Response

next_extent

new configuration?

next_extent (aliyar)

11g AuoAllocate algorithm

11 autoallocate algorithm

More to Explore

VLDB

VLDB