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?