Oracle-L: Re: create interMedia index problem

From: <JApplewhite_at_austin.isd.tenet.edu>
Date: Fri, 28 Mar 2003 05:24:01 -0800
Message-ID: <F001.00575211.20030328052401@fatcity.com>

Guang,

Is your RESINDEX tablespace a Locally-Managed Tablespace with Uniform Extents of less than 3 database blocks? If so, that's your problem. InterMedia indexes consist of some LOB segments and those require extents of at least 3 database blocks - at least in 8i.

If not, I don't know.

Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
JApplewhite_at_austin.isd.tenet.edu

                                                                                                           
                      Guang Mei                                                                            
                      <gmei_at_incyte.com>        To:       Multiple recipients of list ORACLE-L              
                      Sent by:                  <ORACLE-L_at_fatcity.com>                                     
                      root_at_fatcity.com         cc:                                                         
                                               Subject:  create interMedia index problem                   
                                                                                                           
                      03/27/2003 10:53                                                                     
                      PM                                                                                   
                      Please respond to                                                                    
                      ORACLE-L

Hi:

I have this problem on our production server and I don't know too much about InterMedia stuff. We have oracle 8173 on Sun Solaris 2.8.

ISI_at_remax-SQL> desc DRUGDATA;

 Name                                      Null?    Type
 ----------------------------------------- --------


----------------------------

 ID                                        NOT NULL NUMBER
 DRUGREPID                                 NOT NULL NUMBER
 FIELD                                     NOT NULL NUMBER
 TEXT                                               VARCHAR2(4000)
 DRUGTERMID                                         NUMBER
 DATATYPEID                                NOT NULL NUMBER
 ADD_FILE                                  NOT NULL VARCHAR2(32)
 ADD_DATE                                  NOT NULL DATE
 DEL_FILE                                           VARCHAR2(32)
 DEL_DATE                                           DATE
 STATUS                                    NOT NULL CHAR(1)
 ORDERBY                                   NOT NULL NUMBER

ISI_at_remax-SQL> select count(*) from DRUGDATA;

COUNT(*)

TABLESPACE_NAME USED-Kb ALLOC-Kb USED% SEGS >EXT >NEXTEXT
-------------------- ----------- ----------- ------ ----- ----- --------

DATA                       3,000   1,048,576     .3     1     1      504
INDEXES               10,664,424  14,680,064   72.6   480    11  693,080
PERFSTAT                 105,728   2,097,152    5.0    56   150      128
PROTEOME                 164,872   1,048,576   15.7    52    19   25,600
RBS                    5,131,360   8,388,608   61.2     6   800    2,048
RESCTX                    43,832   6,803,456     .6    66    28      504
RESDATA               34,470,408  46,137,344   74.7   361  2319 ########
RESINDEX              41,046,376  69,206,016   59.3   300  1063  512,000
SYSTEM                    74,320     153,600   48.4   401   115    1,120
TEMP                  10,224,960  10,240,000   99.9     1  2045    5,000
YPD                   12,769,224  16,777,216   76.1   249 #####  292,976

TABLESPACE_NAME      USED-Kb     ALLOC-Kb    USED%  SEGS  >EXT  >NEXTEXT


-------------------- ----------- ----------- ------ ----- ----- --------

YPDCUST                        0   1,048,576     .0     0     0        0

Then I ran

create index DRUGDATAINDEX_TEXT on DRUGDATA (TEXT) indextype is ctxsys.context
parameters ('LEXER ctxsys.ISILEX WORDLIST ctxsys.ISIWORDLIST STOPLIST ctxsys.ISISTOP storage isistore memory 50M');

I got

create index DRUGDATAINDEX_TEXT on DRUGDATA (TEXT) *
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_tables
ORA-01658: unable to create INITIAL extent for segment in tablespace

RESINDEX

ORA-06512: at "CTXSYS.DRUE", line 126
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 78
ORA-06512: at line 1

and I got from alert_log file:

ORA-1652: unable to extend temp segment by 128000 in tablespace RESINDEX I did coalesce on all tablespace and added another 2G datafile on RESINDEX tablespace, I still got the same error. BTW, I could run the same sql on two other DEV instances without any problem. The RESINDEX ts on them are much more filled (like 90% full).

Any idea what might be the problem? TIA.

Guang

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Guang Mei
INET: gmei_at_incyte.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author:
INET: JApplewhite_at_austin.isd.tenet.edu

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message