0

I'm working in Oracle 19c DB. Block size is 8k. I'm trying to insert new data in my table. I have enough free space (according to dba_free_space more than 2 GB). But I've got ORA-01654: unable to extend index by 8192. The question is why I need 8192*8k=64 MB free space if NEXT_EXTENT = 1048576 = 1MB? And how to reuse my free space?

I've checked that i need free extents 64 MB or above, byt I have only 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

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
-------
1642

I've got an error because of the lack of large enough free extents. But I still don't understand why I need 64 MB (not 1 MB).

1
  • You might not have uniform allocation. You want locally managed tablespaces with uniform allocation. CREATE TABLESPACE testts DATAFILE 'testts.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; Commented Jun 5, 2023 at 23:55

2 Answers 2

0

Oracle databases are split up into Tablespaces, each of which has its own size limit.
Looking at free space across the whole database is of questionable value.

It's also possible that you have, actually, run out of disk space but your Data Files(s) and/or Tablespaces are blissfully unaware of it.
For this reason, it's Good Practice to "pre-fill" [SMALLFILE] Data Files to their maximum size (32GB) instead of allowing them to "grow" automatically (AUTOEXTEND). That way, Oracle Database has a complete picture of what's really available to it.

1
  • I don't look at free space across the whole database, but only tablespace_name = 'MY_TABLESPACE' I'm more curious about why I need 64 MB instead of 1 MB to extent Commented Jun 5, 2023 at 7:02
0

If you want to modify NEXT_EXTENT size for a table:

ALTER TABLE table_name STORAGE (NEXT next_extent_size);

To modify the NEXT_EXTENT size for an index, use the following syntax:

ALTER INDEX index_name STORAGE (NEXT next_extent_size);

THIS IS AN EXAMPLE:

ALTER TABLE my_table STORAGE (NEXT 10M);

Hope this helps.

//Edit

You can also use the autoextend clause.

ALTER TABLESPACE tablespace_name AUTOEXTEND ON [NEXT next_extent_size] [MAXSIZE maximum_size];

EXAMPLE

ALTER TABLESPACE my_tablespace AUTOEXTEND ON NEXT 10M MAXSIZE 1G;
1
  • Thanks for answer I don't want to modify NEXT_EXTENT. I have default value 1MB, but for some reason it needs 64 MB to extent. Commented Jun 5, 2023 at 6:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.