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

Request notifications

Running OpenACS on Oracle,
My SWS stoped working, I have realized today that the oracle job updating cr_rev_content_index has been failing for some time.
Tried to run and got this error:
CR_REV_CONTENT_INDEX           2004-02-06 ALTER INDEX
DRG-10595: ALTER INDEX CR_REV_CONTENT_INDEX failed
DRG-50857: oracle error in dreii0fsh
ORA-01631: max # extents (249) reached in table MYUSER.DR$CR_REV_CONTENT_INDEX

The SWS is integrated only with the BBoard package and there is around 10000 posts to that forum.

What is best way to fix and prevent from this accuring again?

Excerpt From Oracle MetaLink Doc ID 152475.1:

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.

Jay, I never put the time into it to fully solve it, but I've seen the same or similar problems. There's some info in "Intermedia:" section of my Oracle docs.
I will post here steps I did.
First I have applied the command suggested by C.R.Oldham
ALTER TABLE owner>.table STORAGE ( MAXEXTENTS x); 
on MYUSER.DR$CR_REV_CONTENT_INDEX table
I have set MAXEXTENTS to 500,

My CR_REVISIONS table has arount 10000 lines in it.

select count(*) from cr_revisions;
10560
then i have tried to continue with the inexing by using the 'resume' option, what took around 10h of time with 70-80% CPU use and filled around 1.8G of my HD and then at the end got an error.

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

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
any idea??

Next step I have tried to rebuild the index from scratch

I dropped the index

drop index cr_rev_content_index;
and recreated it.
The problem here is that create index for context index type does not allow you to enter storage option, and the default maxextents is applied. This default in my case 249 extents which depends on your db_block_size parameter (see: http://www.orapub.com/papers2/doc104.pdf )
To go around this limitation I have issued
create index cr_rev_content_index ....
and immediately after
ALTER TABLE MYUSER.DR$CR_REV_CONTENT_INDEX STORAGE ( UNLIMITED ); 
this helped to overcome maxextens issue but did not fix the problem as you will soon see:
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-01237: cannot extend datafile 7
ORA-01110: data file 7: '/ora8/m01/app/oracle/oradata/ora8/drsys01.dbf'
ORA-19502: write error on file "/ora8/m01/app/oracle/oradata/ora8/drsys01.dbf",
blockno 347393 (blocksize=4096)
ORA-27072: skgfdisp: I/O error
Linux Error: 9: Bad file descriptor
Additional information: 347393
ORA-06512: at "CTXSYS.DRUE", line 126
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 78
ORA-06512: at line 1

as you see drsys01.dbf grew so big that filled my disk partition ora8 and indexing failed

next step will be to move drsys01.dbf to another location and try again.

Moving drsys01.dbf to another location with more space.
======
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>
checked index cr_rev_content_index and it shows as valid, will investigate if this caused any damage.

Now, what does it mean? and why?

After some investigation I found that:

  • cr_rev_content_index is in VALID state and SWS is working,
  • DR$CR_REV_CONTENT_INDEX$I table has 15million rows in it (after indexing cr_revision with 10000 rows)
  • ERROR on INSERT in News package (this was working before, and News package was not touched)
  • Errow in News on insert new item.
    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
    ORA-04088: error during execution of trigger 'MYUSER.ACS_CONTENTS_SWS_UTR'
    ORA-06512: at "MYUSER.CONTENT_REVISION_UTRG", line 5
    ORA-04088: error during execution of trigger 'MYUSER.CONTENT_REVISION_UTRG'
    ORA-06512: at "MYUSER.NEWS", line 208
    ORA-06512: at line 4
    
    SQL:
    
            begin
            news.set_approve(
                approve_p       => 't',
                revision_id     => :id,
                publish_date    => :publish_date_ansi,
                archive_date    => :archive_date_ansi,
                approval_user   => :approval_user,
                approval_date   => :approval_date,
                approval_ip     => :approval_ip,
                live_revision_p => :live_revision_p
             );
        end;
    
    
    this is a little too much for 4 o'clock in the morning, what is domain index that is marked as FAILED? all indexes are shown as VALID in MYUSER schema. Ideas?

Finally to solve this
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
problem with Failed index, did some investigation:

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.

Correction:
it was CR_REV_CONTENT_INDEX that was so slow and not SWS as SWS does not use this index but its own sws_srch_cts_ds_iidx

I'm using Null filter and not Inso and this seemed to be the problem for CR_REV_CONTENT_INDEX as it tried to index everything including binary files generating million of rows in the index and filling up disc space. Instead SWS has a clean table for indexing containing only data you need for your search hence Null intermedia filter is working fine here.

Droped cr_rev_content_index entirely as not needed anywhere in the system and using sws ( sws_srch_cts_ds_iidx) search instead.