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