5 Replies Latest reply: Jan 7, 2013 3:14 PM by Henrie Cuijpers RSS

    Again another drg-51030 question

    Henrie Cuijpers
      Hi All,

      i've got a nice search engine built with Oracle Text. And of course i am getting a drg-51030 multiple times a day. I know what it means and i know that i can change this setting about the max words, but i think the error is a good thing. I do capture (exception) it and display a nice understandable message to the user. The thing is, i don't want this message to apear in the alert-file, because it is no panic situation. (The occurrence is logged in an application log anyway, so when i want i can find them.) Does anyone know of a method to suppress this message from the alert log?

      Kind regards

      Edited by: hcHoep on Dec 21, 2012 10:23 AM

      Edited by: hcHoep on Jan 7, 2013 1:37 PM
        • 2. Re: Again another drg-51030 question
          Ebalthes-Oracle
          DRG-51030 error is by default not reported in the alert logfile unless you have set an event in your init.ora/spfile to catch this error.

          Thanks
          • 3. Re: Again another drg-51030 question
            Henrie Cuijpers
            This is typically not true! In all default(!) installations (created with DBCA, default OLTP) this is logged in the alert file...


            [edit]
            Check:

            SQL> create pfile='/home/oracle/prod_init.ora' from memory;
            SQL> !cat /home/oracle/prod_init.ora|grep -i event

            gives nothing...
            [edit]

            Edited by: hcHoep on Jan 7, 2013 1:33 PM
            • 4. Re: Again another drg-51030 question
              Roger Ford-Oracle
              It certainly doesn't in my "out of the box" 11.2.0.2 installation.

              I forced the DRG error as follows:
              SQL> create table docs (text varchar2(30));
              
              Table created.
              
              SQL> declare
                2    cntr number := 1;
                3  begin
                4    for i in 1..51000 loop
                5      insert into docs values ('foo'||cntr);
                6      cntr := cntr + 1;
                7    end loop;
                8  end;
                9  /
              
              PL/SQL procedure successfully completed.
              
              SQL> create index docstext on docs(text) indextype is ctxsys.context;
              
              Index created.
              
              SQL> select * from docs where contains( text, 'foo%' ) > 0;
              select * from docs where contains( text, 'foo%' ) > 0
              *
              ERROR at line 1:
              ORA-29902: error in executing ODCIIndexStart() routine
              ORA-20000: Oracle Text error:
              DRG-51030: wildcard query expansion resulted in too many terms
              Then looked in my alert log and there was no sign of the error.

              I think my database was created as "General Purpose" rather than OLTP, but I'd be surprised if that made a difference.

              How are your queries being run? From a standard client, or perhaps from a scheduled job? Can you try the test exactly as above and see if it still logs the error to your alert log?
              • 5. Re: Again another drg-51030 question
                Henrie Cuijpers
                Hi Thank you!!
                I think the DB type does not make any difference.
                You are very right about the dbms-job!
                See below.
                But even with an exception "handler" (when others then what-ever!) the error shows up in my alert.. So my question stays: "how do i suppress this one"...



                when i create your example and execute:
                declare  s varchar2(32000); begin   select text into s from docs where contains( text, 'foo%' ) > 0 and rownum < 1; end;
                /
                i do not get the error in my alert but indeed with this:
                declare dummynr integer; begin
                  sys.dbms_job.submit
                    ( job       => dummyNr
                     ,what      => ' declare s varchar2(32000); begin  select text into s from docs where contains( text, ''foo%'' ) > 0 and rownum < 1; end; '
                     ,next_date => sysdate
                     ,no_parse  => true
                  );
                 commit;
                end;
                /
                i get this in my alert:
                Mon Jan 07 16:28:47 2013
                Errors in file ......../diag/rdbms/..........._j000_29273.trc:
                ORA-12012: error on auto execute of job 6673
                ORA-29902: error in executing ODCIIndexStart() routine
                ORA-20000: Oracle Text error:
                DRG-51030: wildcard query expansion resulted in too many terms
                ORA-06512: at line 1
                but when i execute the following there is also an error in the alert:
                declare dummynr integer; begin
                  sys.dbms_job.submit
                    ( job       => dummyNr
                     ,what      => ' declare s varchar2(32000); begin  select text into s from docs where contains( text, ''foo%'' ) > 0 and rownum < 1; exception when others then null; end; '
                     ,next_date => sysdate
                     ,no_parse  => true
                  );
                 commit;
                end;
                /
                Alert:
                Mon Jan 07 16:32:47 2013
                Errors in file /.............._j000_29329.trc:
                ORA-12012: error on auto execute of job 6673
                ORA-29902: error in executing ODCIIndexStart() routine
                ORA-20000: Oracle Text error:
                DRG-51030: wildcard query expansion resulted in too many terms
                ORA-06512: at line 1
                Edited by: hcHoep on Jan 7, 2013 10:14 PM