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