How to fix ORA-01654 unable to extend index in tablespace


When you get an error like: How to fix ORA-01654 unable to extend index in tablespace, then this article is for you!.

The problem in Oracle is that when you delete a record, Oracle will leave it blank. That’s a real waste of space when you delete thousands of records. So deleting records will not help to solve this problem.

Here is an example:

Adding 6 records:

After deleting records 2 and 3:

After adding value 7 and 8:

Fortunately, there is a solution to fix this. You can shrink your tables. By shrinking your tables, Oracle will remove all blank records. To shrink a table, use the following SQL statement.

alter table <mytable> enable row movement;
alter table <mytable> shrink space;
alter table <mytable> disable row movement;

analyze table <mytable> compute statistics;

If that doesn’t work for you, then you have not enough disk space left. Adding some disk space will solve your problem too.

Hopefully your problem is solved now. Enjoy!


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Tag Cloud