NEXT_EXTENT for PRODDTAT?

In a client with OneWorld Xe, Update 6, SP19.1 with Oracle 8.1.7 and W2K on DS/ES:

My client could not input orders. I looked at the JDE.LOG and saw many entries like this:
OCI0000179 - Error - ORA-01653 could not extend table PRODDTA.F5532001 with 7 in tablespace PRODDTAT
(This is not the exact error message, because I get it in Spanish, but it's pretty close I think).

It fails trying to extent PRODDTAT by 7 blocks. My BLOCK_SIZE according to \oracle\admin\jde\pfile\init.ora is 8K. This means it can't extend by 56K.

I ran this query:
select tablespace_name, max_extents, next_extent
from dba_tablespaces
where tablespace_name='PRODDTAT';

with this result:
TABLESPACE_NAME MAX_EXTENTS NEXT_EXTENT
------------------------------ ----------- -----------
PRODDTAT 2147483645 57344

So the NEXT_EXTENT is exactly 56K-- I assume that's too small.

I want to issue this command to augment the NEXT_EXTENT for my tablespaces:

alter tablespace PRODDTAT default storage (next NEW_NEXT_EXTENT)

What number should I put in place of NEW_NEXT_EXTENT? The current size of the tablespace files for PRODDTAT is about 1.5GB.

UPDATE: I also have found that in DBA_TABLESPACES, PCT_INCREASE is 0 for all my tablespaces. I think this is a problem. My boss has asked me to set this to 20% for all with this command:

alter tablespace PRODDTAT default storage (pctincrease 20);

Does this sound reasonable?
Thanks in advance.

Hi Tim,
it looks like you run out of space for this tablespace. Can you expand the tablespace manually? Did you setup the tablepsace size to unlimited or to a value?
Regards

Herbert

My understanding goes like this ..........

PCT_INCREASE controls the amount by which your next extent is bigger than the last one you added. If you make it non-zero then the extents added will differ in size, and there aren't many good reasons for doing that.
NEXT controls the size of the size of the extents added after the initial.

So if Initial is 1Mb and next = 2Mb then the extents that added are 1Mb, 2Mb, 2Mb, 2Mb etc.
If you throw in a PCTONCREASE of 50 % they go 1Mb, 2Mb, 3Mb (2Mb + 50%), 4.5 Mb (3Mb + 50%).

Different extent sizes are going to give you fragmentation down the line so, if you can't use locally managed tablespaces, the best setting is probably to make INITIAL=NEXT and have increase set to zero.

Running out of space grenerally can be a problem if people your client isn't monitoring something. Allowing datafiles to grow "indefinitely" is easier because seeing disk disappear is fairly easy to watch and set alarms for.

I run a daily script to report tablespaces and remaining free space but I'd be interested to hear what others are doing. Enterprise Manager alerts woud have been nice if the money had been spent .........

You understanding is right Richard and you provided very good example. I will add few lines

The definition of extend is the no of contigious Oracle data block allocked to an Oracle object. The (initial,next extent,pctincrease) parameters are set the table(Object) level. If not set at the table level then these parameter take the default value set at the tablespace level.
So there is a relationship between block size(in ini.ora of Oracle) and extent size. The extent size would be rounded to the multiple of Oracle block size. But the size of the extent will be controlled as explained by Richard.

These are my 2 cents