Re: ORA-04030, create text index error

From: lsllcm <lsllcm_at_gmail.com>
Date: Sun, 8 Mar 2009 00:02:27 -0800 (PST)
Message-ID: <6746308e-c01c-413e-9f3f-029a2e4c5e0b_at_q9g2000yqc.googlegroups.com>

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 Received on Sun Mar 08 2009 - 03:02:27 CDT