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:
[1][2][3][4][5][6]
 
After deleting records 2 and 3:
[1][][][4][5][6]
 
After adding value 7 and 8:
[1][][][4][5][6][7][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;
commit;

information contact: http://laurenthinoul.com/how-to-fix-ora-01654-unable-to-extend-index-in-tablespace/

2 comments

  1. thx for stealing my article!

  2. This is not thievery.

    I have write end comment :

    information contact: http://laurenthinoul.com/how-to-fix-ora-01654-unable-to-extend-index-in-tablespace/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: