Forum Stats

  • 3,838,250 Users
  • 2,262,346 Discussions
  • 7,900,556 Comments

Discussions

ORA-1654: unable to extend index xx by 128/1024 in tablespace INDX

ProDBA
ProDBA Member Posts: 120 Blue Ribbon

The INDX tablespace has already enough space approx. 20 GBs.

tbs_name   tbs_max_mega_bytes   tbs_mega_bytes  tbs_free_mega_bytes tbs_percent_free  tbs_used_of_max
--------   ------------------   --------------  ------------------- ----------------  ----------------
INDX       223231.94            104168          19922               19.12             37.74

But still getting the following error in the alert log:

ORA-1654: unable to extend index xx by 128 in tablespace INDX

What could be the root cause?

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,004 Blue Diamond
    edited Aug 1, 2022 2:50PM Answer ✓

    Why does your title say "128/1024" - it is a clue that your tablespace has system allocated extents (i.e. will use extent sizes of 64K, 1MB, 8MB, and 64MB as objects grow)? If you can connect as a DBA:

    set long 20000
    select dbms_metadata.get_ddl('TABLESPACE','{your tablespace name here}') from dual;
    

    One possibility (though fairly unlikely) is that your activity has been creating and dropping lots of very small tables in a way that has left lots of holes which are multiples of 64KB, but none which are as large as 1MB at a position that Oracle considers to be a 1MB boundary (or 8MB/8MB for the 1024 error).

    Another possibility is that you have created and dropped lots of objects but never purged the recyclebin, so dba_free_space reports lots of free space that is taken up by objects in the recyclebin - try executing (if you have the privilege):

    SQL> select ts_name, count(*), sum(space) from dba_recyclebin group by ts_name;
    

    The "space" column is in blocks in your version.

    You might care to read this note.


    I don't really think you should have a problem in either of these cases since your tablespace is reporting a max size and so (presumably) should have added space to allow the index to grow; and the recyclebin is supposed to discard objects to make space if there's a demand for space that isn't available. Possibly, however, your version of Oracle (you've tagged 11gr2) may have a bug that results in the error. (e.g. the code to extend thinks it doesn't have to because the recyclebin has lots of space, but the recyclebin code thinks it doesn't have to flush because the tablespace has extendable files.)

    Regards

    Jonathan Lewis

Answers

  • Riaz.
    Riaz. Member Posts: 36 Bronze Badge
    edited Jul 28, 2022 8:48AM

    What is the NEXT extent size of the index:

    SELECT NEXT_EXTENT FROM user_indexes

    WHERE index_name='YourIndex'


    Another possibility is you are doing some heavy DML activity or DDL (rebuild for example) activity that needs more space than the free one, during the activity.

  • ProDBA
    ProDBA Member Posts: 120 Blue Ribbon
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,004 Blue Diamond
    edited Aug 1, 2022 2:50PM Answer ✓

    Why does your title say "128/1024" - it is a clue that your tablespace has system allocated extents (i.e. will use extent sizes of 64K, 1MB, 8MB, and 64MB as objects grow)? If you can connect as a DBA:

    set long 20000
    select dbms_metadata.get_ddl('TABLESPACE','{your tablespace name here}') from dual;
    

    One possibility (though fairly unlikely) is that your activity has been creating and dropping lots of very small tables in a way that has left lots of holes which are multiples of 64KB, but none which are as large as 1MB at a position that Oracle considers to be a 1MB boundary (or 8MB/8MB for the 1024 error).

    Another possibility is that you have created and dropped lots of objects but never purged the recyclebin, so dba_free_space reports lots of free space that is taken up by objects in the recyclebin - try executing (if you have the privilege):

    SQL> select ts_name, count(*), sum(space) from dba_recyclebin group by ts_name;
    

    The "space" column is in blocks in your version.

    You might care to read this note.


    I don't really think you should have a problem in either of these cases since your tablespace is reporting a max size and so (presumably) should have added space to allow the index to grow; and the recyclebin is supposed to discard objects to make space if there's a demand for space that isn't available. Possibly, however, your version of Oracle (you've tagged 11gr2) may have a bug that results in the error. (e.g. the code to extend thinks it doesn't have to because the recyclebin has lots of space, but the recyclebin code thinks it doesn't have to flush because the tablespace has extendable files.)

    Regards

    Jonathan Lewis