RE: index refresh as job
- From: "Nelson Flores" <nflores@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 4 Mar 2004 15:09:28 -0800
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@xxxxxxxxxxx [mailto:Jared.Still@xxxxxxxxxxx]
Sent: Thursday, March 04, 2004 1:59 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
03/04/2004 12:44 AM
Please respond to oracle-l
To: <oracle-l@xxxxxxxxxxxxx>
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
Other related posts:
