RE: index refresh as job

From: Nelson Flores <nflores_at_expand.cl>
Date: Thu, 4 Mar 2004 15:09:28 -0800
Message-ID: <00c301c4023d$c0712e40$cce8cd18@neltox>

Impeccable timing I must say :-) .  

Thanks Jared, this worked a treat. tell me where I can mail you a beer ;)  

Thanks again.
Nelson        

-----Original Message-----
From: Jared.Still_at_radisys.com [mailto:Jared.Still_at_radisys.com] Sent: Thursday, March 04, 2004 1:59 PM
To: oracle-l_at_freelists.org
Subject: Re: index refresh as job  

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 - 18:03:20 CST