I need to create tablespace dedicated to a table.
The table has INITIAL and NEXT extent sizes 512M.
And it is 30G in size.
So, the tablespace needs to have more than one datafile.
Even though, the next extent is 512M, I am seeing extensts of different sizes getting allocated to this table from dba_extents table. The extent sizes are anywhere between exact 512M to 512.03, 512.06, 512.12. There doesn't seem to be any logic !!
So, if I want every datafile to hold 4 extents, what should be the datafile size ? And why the extent sizes are different ?
What is the pctincrease value set to?
When you created the table, did you specify a pctincrease?
If not, what is the pctincrease of the tablespace?
Yes, PCTINCREASE was specified as 0 at the time of creation.
PCTINCREASE for tablespace is also 0.
And moreover, if PCTINCREASE was supposed to be the cause, there should have been a pattern in the size. This is not the case. Some extents are of 512M, some are 512.03M, some 512.06, etc.
If you create a datafile, you have to take in account the datafile header.
if you want to make a datafile that containts 512Mb,
create a datafile with size 512M + 1block.
eg, if you have a 4K block db,
alter tablespace .... add datafile ... size 524292K;
524292 = ( 512 * 1024 ) + 4
Hope this helps