Re: index refresh as job

From: <Jared.Still_at_radisys.com>
Date: Thu, 4 Mar 2004 13:58:41 -0800
Message-ID: <OF14739E99.340AA7E5-ON88256E4D.0078997A-88256E4D.0078A526@radisys.com>

How's this for timing?

I did this just today, works fine.

HTH, Jared

PS. You need to login as CTXSYS and run 'grant select on ctx_indexes to cquser;'

procedure to sync --------------------
- ctx_sync.sql
- setup procedure to sync ctx/intermedia/oracle_text/whatever_it_is_this_month
- indexes

create or replace procedure ctx_sync
is
begin

for irec in (

      select idx_name
      from ctxsys.ctx_indexes
      where idx_owner = USER
   ) loop
      ctx_ddl.sync_index(irec.idx_name);
      --dbms_output.put_line(irec.idx_name);

end loop;

end;
/

show error procedure ctx_sync

create the job ----------------------------------
- job_submit.sql
- run every 10 minutes

variable jobno number;
begin

dbms_job.submit(

      :jobno
      , 'ctx_sync;'
      -- every 10 minutes at 00,10,20,30,40 and 50
      , trunc(sysdate,'hh24') +  ( ( 10 + ( 10 * 
floor(to_number(to_char(sysdate,'mi')) / 10))) / ( 24 * 60 ))
      , 'trunc(sysdate,''hh24'') +  ( ( 10 + ( 10 *

floor(to_number(to_char(sysdate,''mi'')) / 10))) / ( 24 * 60 ))'

);
commit;
end;
/

print :jobno

"Nelson Flores" <nflores_at_expand.cl>
Sent by: oracle-l-bounce_at_freelists.org
03/04/2004 12:44 AM
Please respond to oracle-l

        To:     <oracle-l_at_freelists.org>
        cc: 
        Subject:        index refresh as job

Hi list,
It's late and I'm in a little bit of a pickle ? The problem is synchronizing a text index using a job. I create the job fine, but I query user_jobs, and I keep on getting failures ?

I create the job (with suitable account privs) with the following?

           DECLARE
            v_jobno number;
            BEGIN 
                      dbms_job.submit(job=>v_jobno, 
                      what=>'ctx_ddl.sync_index(''"FOOBAR"."SEARCH_IDX"'' 
);', 
                      next_date=>sysdate+1/24,
                      interval=>'sysdate+1/24');
            END;

The job is created fine.

I run exec ctx_ddl.sync_index('"FOOBAR"."SEARCH_IDX"'); and it works fine and dandy, but every time the job is invoked it returns an error. Anyone have any ideas ?

Is there a better way to do this???? (I hate having to depend on a job for this)

BTW I'm doing this on my laptop which is 9.2.0.1.0 on Win XP?.

Thanks in advance
Nelson

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Mar 04 2004 - 17:33:32 CST