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) [EMAIL PROTECTED] Guang Mei <[EMAIL PROTECTED]> To: Multiple recipients of list ORACLE-L Sent by: <[EMAIL PROTECTED]> [EMAIL PROTECTED] 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. [EMAIL PROTECTED]> 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 [EMAIL PROTECTED]> select count(*) from DRUGDATA; COUNT(*) ---------- 0 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).