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

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?
Coco BeansDesignerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
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?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
remove the minextents from the table definition

are you sure you extended in the correct tablespace ?
0
Coco BeansDesignerAuthor 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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Storage

From novice to tech pro — start learning today.