ORA-01659: unable to allocate MINEXTENTS  - But loads of space available

Coco Beans
Coco Beans used Ask the Experts™
on
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

I'm working in Oracle Warehouse Builder but essentially it's just doing an insert update on a table.  I'm getting the following error:

ORA-01659: unable to allocate MINEXTENTS beyond 800 in tablespace stagedata

I had allocated more tablespace.  it says it has almost 26GB free across 4 DBF files. One file as 12 GB free alone.  It's locally managed storage.


At first the error started at 415,416, 417 etc… I allocated another 12 GB and now the error reads 717,718,719.

At first the error started at 415,416, 417 etc… I allocated another 12 GB and now the error reads 717,718,719.





The tables are not that big.  Any ideas?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Developer / Linux System Administrator / Managing Director
Commented:
Even if you have space available, it may be non-contiguous, so the minimum number of extents cannot be allocated in a single block. Modify the minimum number of extents, or add some extra storage so that there is an amount of free space that is contiguous and meets the requirements. The versions of Oracle that I use do not support any method of defragmentation of table spaces but you could check if there are ways of achieving this with the version you use.

Edit: I see that you have already added storage, but if it was done incrementally, it may still be that there is not enough contiguous storage. You can change the initial parameter for the table to help or I believe there are ways of shrinking tables and reducing data file and tablespace fragmentation.

Edit 2: I would only have expected to see that message when Oracle is creating an object. Is there any chance that the insert/update operation is making use of temporary storage that resides in stagedata and is attempting to create a large object using that tablespace?
Geert GOracle dba
Top Expert 2009
Commented:
remove the minextents from the table definition

are you sure you extended in the correct tablespace ?
Coco BeansDesigner

Author

Commented:
It was the initial set in the storage of the table.  It was set to 4000000.  I changed it to 400 and it ran okay.  Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial