Re: ORA-04030, create text index error
From: lsllcm <lsllcm_at_gmail.com>
Date: Tue, 10 Mar 2009 08:32:18 -0700 (PDT)
Message-ID: <8835736b-6d3a-4852-b481-51073e2cad9b_at_s38g2000prg.googlegroups.com>
On Mar 10, 8:28 am, lsllcm <lsl..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Date: Tue, 10 Mar 2009 08:32:18 -0700 (PDT)
Message-ID: <8835736b-6d3a-4852-b481-51073e2cad9b_at_s38g2000prg.googlegroups.com>
On Mar 10, 8:28 am, lsllcm <lsl..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
I have add memory parameter 1g, When we create regular index, it will use temporary tablespace. But why does not it use when creating text index? It is very strange.
I have noticed the memory used in pga is about 3g.
Thanks Received on Tue Mar 10 2009 - 10:32:18 CDT