Re: ORA-04030, create text index error

From: jgar the jorrible <joel-garry_at_home.com>
Date: Mon, 9 Mar 2009 09:53:16 -0700 (PDT)
Message-ID: <fecfd781-461f-47c7-bd82-2f872d4303ed_at_l16g2000yqo.googlegroups.com>

On Mar 8, 1:02 am, lsllcm <lsl..._at_gmail.com> wrote:
> I try to create one text index on mutlple column and multiple tables,
> and meet outofmemory isue.
>
> Below is my test case:
>
> 1.
> connect jacky/jacky
>
> 2.
> grant select on b1permit to ctxsys;
> grant select on b3addres to ctxsys;
> grant select on b3parcel to ctxsys;
>
> 3. connect ctxsys/ctxsys, create procedure
> create or replace procedure full_text_index( p_id in rowid, p_lob IN
> OUT clob )
> as
> begin
>     for x in ( select dept_code,id1,id2,id3,text1
>                  from jacky.ta
>                 where rowid = p_id )
>     loop
>         dbms_lob.writeappend( p_lob, length(x.text1), x.text1);
>         for y in ( select text2
>                      from jacky.tb
>                     where dept_code = x.dept_code
>        and id1 = x.id1
>        and id2 = x.id2
>        and id3 = x.id3
>      )
>         loop
>             dbms_lob.writeAppend( p_lob, length(y.text2), y.text2 );
>         end loop;
>         for y in ( select text3
>                      from jacky.tc
>                     where dept_code = x.dept_code
>        and id1 = x.id1
>        and id2 = x.id2
>        and id3 = x.id3
>      )
>         loop
>             dbms_lob.writeAppend( p_lob, length(y.text3), y.text3);
>         end loop;
>
>     end loop;
> end;
> /
>
> 4. connect jacky/jacky
> exec ctx_ddl.drop_preference('foo_user_datastore');
> exec ctx_ddl.drop_preference('my_lexer');
> begin
>        ctx_ddl.create_preference( 'foo_user_datastore',
> 'user_datastore' );
>        ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure',
> 'ctxsys.full_text_index' );
> end;
> /
> begin
>        ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
>        ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
>        ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
> end;
> /
>
> 5. create text index
> create index t1_idx on b1permit(B1_ACCESS_BY_ACA)
> indextype is ctxsys.context
> parameters( 'datastore foo_user_datastore lexer my_lexer memory
> 1073741824');
>
> 6. get out of memory error
>
> ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
> ORA-20000: Oracle Text error:
> DRG-50857: oracle error in drueixe
> ORA-04030: out of process memory when trying to allocate 65548 bytes
> (CTX PRM heap,draccbx:message buffer)
> ORA-06512: at "CTXSYS.DRUE", line 191
> ORA-06512: at "CTXSYS.DRUE", line 49
> ORA-06512: at "CTXSYS.DRUE", line 147
> ORA-06512: at "CTXSYS.D
> ORA-04030: out of process memory when trying to allocate 16940 bytes
> (pga heap,kgh stack)
> ORA-06512: at "CTXSYS.DRUE", line 160
> ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364

Mladen gave the likely cause, but see metalink document 233869.1 for more information and some other possibilities. Note the OS and Oracle version/patch level make a difference, you should always include this information in posts.

jg

--
_at_home.com is bogus.
http://www3.signonsandiego.com/stories/2009/mar/08/1m8embezzle00209-employee-may-have-skimmed-million/?uniontrib

Received on Mon Mar 09 2009 - 11:53:16 CDT