logo1
OCP TECHNOLOGY

ORA-01654: unable to extend index solution

Posted on by Shripal
ora-01654

Hi guys, I'm getting an "ora-01654 unable to extend index" error during the production database shutdown in the system tablespace. Actually, in my case, the database is created manually, the tablespace size is 200MB, and auto extend also off.

What the ora 01654?

It comes if the tablespace doesn't have sufficient space or its autoextend is disabled.

My Tablespace Details:

SQL> select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 "size MB",AUTOEXTENSIBLE from dba_data_files;

TABLESPACE_NAME 	       FILE_NAME				   size MB AUT
------------------------------ ---------------------------------------- ---------- ---
SYSTEM			       /u01/app/oracle/OCP/system.dbf		       200 NO
SYSAUX			       /u01/app/oracle/OCP/sysaux.dbf		       100 NO
UNDOTBS1		       /u01/app/oracle/OCP/undo1.dbf		       100 NO

How do you resolve ORA 01654?

The solution of ora-01654 is only there just extend the datafile size or add a new datafile using the below commands.

Resizing, Adding, and Enable Autoextend

Here I'm going to resize the system tablespace datafile.

How do I resize a tablespace?

SQL> alter database datafile '/u01/app/oracle/OCP/system.dbf' resize 500M;

Adding New Datafile

SQL> alter tablespace system add datafile '/u01/app/oracle/OCP/system02.dbf' size 500M;

Tablespace altered.

Enabling Autoextend

SQL> alter database datafile '/u01/app/oracle/OCP/system.dbf' autoextend on;

Database altered.

I hope you found this article helpful, please write your fillings in the comment box.

Note: ORA-12919: Can not drop the default permanent tablespace

ora-01654 unable

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to Top