Forum OpenACS Q&A: SWS CR indexing stoped working - max number of extents reached in table

Cause:  A table tried to extend past maxextents
Action: If maxextents is less than the system maximum, raise it. Otherwise, you must recreate with larger initial, next or pctincrease params.
Explanation: The max extents error occurs when the current number of extents equals the  maximum number of extents in the max_extents parameter for the object, or the  maximum number of extents allowable for the db_block_size, whichever is smaller, and an attempt is made to add another extent. Max extents can be set for an  object using the MAXEXTENTS option of the storage clause. These kind of problems could be avoided by pro-actively monitoring the object sizes that may reach their max_extents.  Diagnostic Steps: 1. Run the following script to identify the tables with extent problems.

SQL> select segment_name, owner, extents, max_extents      2  from dba_segments      3  where segment_type = 'TABLE'      4  and (extents +1) >= max_extents;
2. Follow up the scripts in:    [NOTE:1019721.6]  SCRIPT: SCRIPT TO REPORT TABLES APPROACHING MAXEXTENTS.  Possible solutions: ------------------- 1. If the value of max_extents for the affected  object is less than the limit    of maximum extents  for  the db_block_size of the database    (refer to [NOTE:1015356.4]), then more extents could be allocated to    the object.    Alter the STORAGE clause of the object to increase MAXEXTENTS.    In databases version  7.3.X or higher one could specify maxextents    UNLIMITED (i.e. 2147483645), but use this with caution.    ALTER TABLE <owner>.table STORAGE ( MAXEXTENTS x);    where x is greater than max_extents and lesser than unlimited (2147483645);    ALTER TABLE <owner>.table STORAGE ( MAXEXTENTS UNLIMITED);    Remark: [NOTE:50380.1] ALERT: Using UNLIMITED Extent Format  2. It is not advisable to have a lot (hundreds of thousands, etc.) of    extents that could result in large amounts of of space management    operations in dictionary managed tablespase.    In that case, or when the number of max_extents for the affected    object reached the limit of maximum extents for the db_block_size,    recreate the object with more efficient storage.    Export the table and precreate the table before import, using    larger storage parameters (initial, next, pctincrease) - to      result in fewer extents,    or    export  using COMPRESS=Y - to minimize number of extents after import.

ALTER TABLE owner>.table STORAGE ( MAXEXTENTS x); 

My CR_REVISIONS table has arount 10000 lines in it.

select count(*) from cr_revisions;
10560

===================================

SQL> ALTER INDEX MYUSER.CR_REV_CONTENT_INDEX REBUILD PARAMETERS('RESUME');
ALTER INDEX MYUSER.CR_REV_CONTENT_INDEX REBUILD PARAMETERS('RESUME')
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: interMedia Text error:
DRG-50857: oracle error in drueixe
oracle error in drueixe
ORA-06512: at "CTXSYS.DRUE", line 126
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 214
ORA-06512: at line 1

ora>  shutdown immediate
Copy drsys01.dbf to another location
cp ...

Startup mount
alter database rename file '/ora8/m01/app/oracle/oradata/ora8/drsys01.dbf' to 
'/new/location/drsys01.dbf';
>statement processed

create index cr_rev_content_index ...

and right after it started ...
alter table MYUSER.DR$CR_REV_CONTENT_INDEX$I
storage (maxextents 2000);

======================
after about 2h got an error:
----------------------

SQL> create index cr_rev_content_index on cr_revisions (content)
  2  indextype is ctxsys.context
  3  parameters ('FILTER content_filter_pref');
create index cr_rev_content_index on cr_revisions (content)
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: interMedia Text error:
DRG-50857: oracle error in drixtab.create_index_indexes
ORA-01630: max # extents (249) reached in temp segment in tablespace MYUSER
ORA-06512: at "CTXSYS.DRUE", line 126
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 78
ORA-06512: at line 1


SQL>

Now, what does it mean? and why?

Error: ora8.c:3930:ora_tcl_command: error in`OCIStmtExecute ()':
 ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE
ORA-06512: at "MYUSER.ACS_CONTENTS_SWS_UTR", line 6

There is no index marked as INVALID or Loading
Checked Acs_contents_sws_utr trigger,
Noticed that sws depends on sws_srch_cts_ds_iidx index.
Index is marked as valid but its field: DOMIDX_OPTSTATUS is marked as FAILED

Droped and recreated sws_srch_cts_ds_iidx index
Problem solved

SWS search with Intermedia works, but why is so slow? it takes around 2 minutes to complete a search! ( already done analyze with dbms_stats.gather_schema_stats..)
Is this normal? I hope not.