We help IT Professionals succeed at work.
Get Started

ORA-01654: unable to extend index - on Oracle9i

Shannon Adams
on
6,918 Views
Last Modified: 2013-12-19
Running Oracle9i

Today, we started receiving the following errors in a production Oracle database:

ESQL ERR vSqlError(). RC=-1654 - ORA-01654: unable to extend index ISOFT.NOTICE_TONAME_INDEX by 128 in tablespace ISOFT_TABLES      

ESQL ERR vSqlError(). RC=-1654 - ORA-01654: unable to extend index ISOFT.NOTICE_FROMNAME_INDEX by 128 in tablespace ISOFT_TABLES  

Based on an Oracle kb article, I obtained the following:

TABLESPACE NAME                CONTIGUOUS BYTES
------------------------------ ----------------
ISOFT_TABLES                      2,097,086,464

TABLESPACE_NAME                # OF EXTENTS     TOTAL BYTES
------------------------------ ------------ ---------------
CWMLITE                                   2      11,141,120
DRSYS                                     1      10,813,440
EXAMPLE                                   1         131,072
INDX                                      1      26,148,864
ISOFT_INDEXES                             1      10,420,224
ISOFT_TABLES                              1   2,097,086,464
ODM                                       1      11,206,656
SYSTEM                                   13      88,670,208
TOOLS                                     1      10,420,224
UNDOTBS1                                 14     187,367,424
USERS                                     1      26,148,864
XDB                                       1         196,608



SQL> select file_name, bytes, autoextensible, maxbytes from dba_data_files where tablespace_name='ISOFT_TABLES';

FILE_NAME             BYTES             AUT         MAXBYTES
isoft_tables01.dbf      2097152000       YES       2097152000

I am not an Oracle DBA and I am not sure how to overcome these errors.  Based on what I have read, here are two options:

1.  Run the tollowing command ALTER DATABASE DATAFILE 'isoft_tables01.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED;

2.  Get rid of fragmentation is drop and recreate the object. I am not sure how to export and import the table.  If this is the best approach, I would appreciate someone leading me through it.

Can someone lead me in the right direction?

Thanks for any help,
Shannon
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 2 Answers and 9 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE