This discussion is archived
1 Reply Latest reply: Nov 7, 2009 9:44 AM by Lubiez Jean-Valentin RSS


725629 Newbie
Currently Being Moderated
I am a novice DBA, we use Oracle 9i version-we use locally managed tablespace with db_block_size=8192. I would appreciate if anyone can throw some insight in to this one. We have automatic alerts setup in our environment; I have received the following alerts recently
‘When the largest objects (or any object with uniform extent sizes) have allocated 10 extents, the tablespace: ‘My_TableSpace’ will be out of space. You should probably increase the size of the tablespace.’

And again after 10 minutes I received the following error:
‘When the largest objects (or any object with uniform extent sizes) have allocated 9 extents’ The number of extents kept decreasing, hence I had to increase the table space by running the following command

alter tablespace ‘My_TableSpace’ add datafile 'C:\MyDB\My_DATA02.dbf' size 1024m autoextend on
This worked and I stopped receiving the errors, but my question is I looked at the tablespace it has only consumed 1020 MB, still 4 MB was left. so theoretically It should increase by next 512 MB once the remaining 4 MB is consumed until it reaches the maximum of 32 GB limit, is that correct? If so why did I receive only 9 extents still available? Won’t they get increase automatically upto MaxExtents as mentioned below.
Tablespace     FileType     Status     FileStatus     UsedMB     FreeMB     InitialExt     MinExtents     MaxExtents
My_TableSpace     Datafile     ONLINE     AVAILABLE     1020     4     524288     1     2147483645


Block Sz Max Datafile Sz (Gb) Max DB Sz (Tb)

2,048 8 512
4,096 16 1,024
8,192 32 2,048

Create tablespace ‘My_TableSpace’ add datafile 'C:\MyDB\My_DATA01.dbf' size 1024m autoextend on next 512m maxsize UNLIMITED
Oracle best practice recommends to use "maxextents unlimited" and "autoextend on" which was did as mentioned and it seems ‘The database can grow till 32 gb limit.’
‘When the existing space in a segment is completely used, Oracle allocates a new extent for the segment.’
Does this mean it has room to grow once the 9 extents are over as the 16GB limit is not yet reached or it would make the database unable to grow, I wasn’t sure hence I had to go with adding one more datafile. Can someone suggest the right approach?
Referred the following docs:
1.     I have also referred Metalink but in vain: 152475.1, 1019721.6
  • 1. Re: MaxExtents
    Lubiez Jean-Valentin Guru
    Currently Being Moderated

    About the MaxExtents, normally with LMT this parameter is set to 2 billions.

    Else, when you use the setting "autoextend on next 512m maxsize UNLIMITED", it means that the datafile will increase by 512 Mo up to 4 MegaBlock.
    If your block size (given by the parameter db_block_size) is 8192 (8 Ko) then the size limit for this datafile is 4x8 = 32 Go.
    If you need more space you can add another datafile etc, up to 1022 datafile for one (smallfile) Tablespace.

    About the message you receive, you can report the error message from the "alert_SID.log file".

    Best Regards,


  • Correct Answers - 10 points
  • Helpful Answers - 5 points