Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: create interMedia index problem

Re: create interMedia index problem

From: gmei <gmei_at_incyte.com>
Date: Fri, 28 Mar 2003 11:53:44 -0800
Message-ID: <F001.005757B2.20030328115344@fatcity.com> 





Hi:



The problem has been fixed. It turned out that we have very big storage
clause for "isistore". All these Intermedia tables will be created when
InterMedia index gets created. So it would try to allocate six 1000M initial
extents. And we don't have that many empty block in RESINDEX tablespace.



begin


ctx_ddl.create_preference('isistore', 'BASIC_STORAGE');
ctx_ddl.set_attribute('isistore', 'I_TABLE_CLAUSE',
 'tablespace RESINDEX storage (initial 1000M next 500M pctincrease 0)');
ctx_ddl.set_attribute('isistore', 'K_TABLE_CLAUSE',
 'tablespace RESINDEX  storage (initial 1000M next 500M pctincrease 0)');
ctx_ddl.set_attribute('isistore', 'R_TABLE_CLAUSE',


'tablespace RESINDEX storage (initial 1000M next 500M pctincrease 0)');

ctx_ddl.set_attribute('isistore', 'N_TABLE_CLAUSE',


'tablespace RESINDEX storage (initial 1000M next 500M pctincrease 0)');

ctx_ddl.set_attribute('isistore', 'I_INDEX_CLAUSE',


'tablespace RESINDEX storage (initial 1000M next 500M pctincrease 0)');

ctx_ddl.set_attribute('isistore', 'P_TABLE_CLAUSE',


'tablespace RESINDEX storage (initial 1000M next 500M pctincrease 0)');

end;



So I run the following to reduce the initial setting to 100M. After that
everything is OK.



begin


-- ctx_ddl.create_preference('isistore', 'BASIC_STORAGE');
ctx_ddl.set_attribute('isistore', 'I_TABLE_CLAUSE',
 'tablespace RESINDEX storage (initial 100M next 100M pctincrease 0)');
ctx_ddl.set_attribute('isistore', 'K_TABLE_CLAUSE',
 'tablespace RESINDEX  storage (initial 100M next 100M pctincrease 0)');
ctx_ddl.set_attribute('isistore', 'R_TABLE_CLAUSE',


'tablespace RESINDEX storage (initial 100M next 100M pctincrease 0)');

ctx_ddl.set_attribute('isistore', 'N_TABLE_CLAUSE',


'tablespace RESINDEX storage (initial 100M next 100M pctincrease 0)');

ctx_ddl.set_attribute('isistore', 'I_INDEX_CLAUSE',


'tablespace RESINDEX storage (initial 100M next 100M pctincrease 0)');

ctx_ddl.set_attribute('isistore', 'P_TABLE_CLAUSE',


'tablespace RESINDEX storage (initial 100M next 100M pctincrease 0)');

end;




Guang



-----Original Message-----


JApplewhite_at_austin.isd.tenet.edu


Sent: Friday, March 28, 2003 8:24 AM


To: Multiple recipients of list ORACLE-L



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(*)





         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: gmei
  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).


Received on Fri Mar 28 2003 - 13:53:44 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US