Re: ORA-04030, create text index error

From: <prasad.epal_at_gmail.com>
Date: Mon, 9 Mar 2009 22:04:21 -0700 (PDT)
Message-ID: <4505572c-8673-41b0-932c-fdb8b6d148b6_at_e1g2000pra.googlegroups.com>

On Mar 9, 9:53 pm, 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...

There are limits on number of processes and number of open files in an OS. It seems they have exceeded in the OS where you have installed the Server.

If you are using Unix/Linux ulimit command might help you.... Received on Tue Mar 10 2009 - 00:04:21 CDT