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...