Laurent Hinoul

“Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.” - John F. Woods

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:

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.

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

Hopefully your problem is solved now. Enjoy!

5 comments for “How to fix ORA-01654 unable to extend index in tablespace

  1. 15/05/2015 at 11:56

    Great! -> “If that doesn’t work for you, than you have not enough disk space left. Adding some disk space will solve your problem too.”

    • Robert
      22/07/2016 at 20:49

      it worked for me after all. 😛

  2. admin
    13/06/2016 at 03:15

    jajajajaj there is a smart solution. jajajajajaja

  3. abhiram
    19/09/2016 at 11:29

    may i know the exact solution … @admin

  4. Ravi Ranjan Kumar
    24/10/2016 at 12:18

    You have to attach the larger disk volume..

Leave a Reply