Re: ORA-04030, create text index error
From: lsllcm <lsllcm_at_gmail.com>
Date: Tue, 10 Mar 2009 08:28:01 -0700 (PDT)
Message-ID: <9baab1d3-ef93-4158-809f-f80e2da5f07c_at_v13g2000pro.googlegroups.com>
On Mar 9, 10:04 pm, "prasad.e..._at_gmail.com" <prasad.e..._at_gmail.com> wrote:
> 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....- Hide quoted text -
>
> - Show quoted text -
Date: Tue, 10 Mar 2009 08:28:01 -0700 (PDT)
Message-ID: <9baab1d3-ef93-4158-809f-f80e2da5f07c_at_v13g2000pro.googlegroups.com>
On Mar 9, 10:04 pm, "prasad.e..._at_gmail.com" <prasad.e..._at_gmail.com> wrote:
> 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....- Hide quoted text -
>
> - Show quoted text -
[oracle_at_pft ~]$ ulimit -a
core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited file size (blocks, -f) unlimited pending signals (-i) 1024 max locked memory (kbytes, -l) 3145728 max memory size (kbytes, -m) unlimited open files (-n) 63536 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 77824 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited[oracle_at_pft ~]$
from the result:
open files (-n) 63536 max user processes (-u) 77824
The two parameter have enough values, right?
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
Thanks Received on Tue Mar 10 2009 - 10:28:01 CDT