Skip to Main Content
  • Questions
  • ORA-01654: unable to extend index by 8192 with enough free space

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Andy.

Asked: June 13, 2023 - 9:06 am UTC

Last updated: June 19, 2023 - 4:47 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Recently I made a reorganization in my database with changing/rebuilding/dropping large tables and indexes.
Now i'm trying to insert new data into my table (relatively small amount of data). I have enough free space. According to dba_free_space its more than 3 GB. But I've got ORA-01654: unable to extend index by 8192. And same error while creating new tables.
Tablespace parameters: EXTENT_MANAGEMENT LOCAL, BLOCK_SIZE 8192, ALLOCATION_TYPE SYSTEM

I've checked that i need not just free space but free extents 64 MB or above, when I only have 1-3 MB extents:

select count(*) as extents_qnt 
from dba_free_space 
where tablespace_name = 'MY_TABLESPACE' 
and bytes/1024/1024 >= 64 -- 64 MB, before adding new datafiles

EXTENTS_QNT
-------
0

select count(*) as extents_qnt
from dba_free_space 
where tablespace_name = 'MY_TABLESPACE' 
and bytes/1024/1024 BETWEEN 1 AND 3

EXTENTS_QNT
-------
2108


The question is why I need 8192*8k=64 MB free space if NEXT_EXTENT = 1048576 = 1MB? 64 MB is too much for me and system doesn't want to reuse free space.
Is it possible that i actually don't have free space and dba_free_space shows wrong/obsolete information?
If dba_free_space is correct, then how to reuse free space?

and Connor said...

We'd need to see more information, but some things to note

a) extent sizes are variable (by default), so the first "n' extents in a segment will be 64k, then they jump to 1m, then 8m after a while, then 64m and so on. (The timing/sizes can depend on a number of things), so its possible your table wants to make the jump to a larger extent size

b) We might need contiguous space - its not just about total free space

To my knowledge, there is no issues with dba_free_space misreporting

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database