Re: ORA-04030, create text index error

From: lsllcm <lsllcm_at_gmail.com>
Date: Tue, 10 Mar 2009 08:29:48 -0700 (PDT)
Message-ID: <75074b7a-eaa9-4096-aa9f-66f51fea2df6_at_n33g2000pri.googlegroups.com>

On Mar 9, 9:53 am, jgar the jorrible <joel-ga..._at_home.com> wrote:
> 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-e...- Hide quoted text -
>
> - Show quoted text -

The linux version is : Redhat Advanced Server 4 [oracle_at_pft ~]$ uname -a
Linux pft 2.6.9-5.ELsmp #1 SMP Wed Jan 5 19:30:39 EST 2005 i686 i686 i386 GNU/Linux

The oracle version is 10.2.0.4 32 bit

Could you paste the document 233869.1 here?

Thanks Received on Tue Mar 10 2009 - 10:29:48 CDT