I found out that my interMedia syncing dbms_job was failing. I defined the
job like this:
connect yon/yon;
declare
v_job number;
begin
dbms_job.submit(
job => v_job,
what => 'ctx_ddl.sync_index(''forums_content_idx'');',
interval => 'sysdate + 1/24'
);
end;
/
show errors
I was getting the following errors in my oracle alert log:
Thu Jul 11 09:21:35 2002
Errors in file /home/oracle/ora8/u01/app/oracle/admin/ora8/bdump/snp4_31130.trc:
ORA-12012: error on auto execute of job 7801
ORA-06550: line 1, column 96:
PLS-00201: identifier 'CTXSYS.CTX_DDL' must be declared
ORA-06550: line 1, column 96:
PL/SQL: Statement ignored
I spent enough time debugging this that I thought it merited sharing what i learned.
It turns out that dbms_jobs only inherit your schema user's default privileges, not
any privileges it might inherit from roles granted to that user. This means that the job
will not run with the ctxsys privilege that you should have granted to your schema
user. So what does this mean? It means that you have to run the job as the ctxsys
user, this is how you do it (you can also look at the
forums/sql/oracle/forums-search-create.sql script):
-- as ctxsys
connect ctxsys/ctxsys;
declare
v_job number;
begin
dbms_job.submit(
job => v_job,
what => 'ctxsys.ctx_ddl.sync_index(''yon.forums_content_idx'');',
interval => 'sysdate + 1/24'
);
end;
/
show errors
You might also want to make sure that you have the following parameters
configured correctly in you init.ora: job_queue_processes (set to at least 1)
and job_queue_interval (default is 60 seconds). Without these your
dbms_jobs won't run at all.