Oracle text scheduling sync on single index
I need to sync an oracle text index. But job fails to create:
declare
v_job_id number(19,0);
begin
dbms_job.submit(
JOB => v_job_id,
WHAT => 'alter index NAME_IDX开发者_如何学JAVA rebuild parameters (''sync'');',
NEXT_DATE => SYSDATE + (1/24),
INTERVAL => 'SYSDATE + (1/24) + 7'
);
end;
/
Or to run:
declare
v_job_id number(19,0);
begin
dbms_job.submit(
JOB => v_job_id,
WHAT => 'CTX_DDL(''NAME_IDX'');',
NEXT_DATE => SYSDATE + (1/24),
INTERVAL => 'SYSDATE + (1/24) + 7'
);
end;
/
But if I run any of those works:
alter index NAME_IDX rebuild parameters ('sync');
call CTX_DDL('NAME_IDX');
Any idea of the correct syntax?
Thank you.
PD: Ive been searching, but the only answer I found doesnt fit my requirements. I also apologize for my english.
You can run an anonymous block, CALL is not in PL/SQL, ALTER INDEX is DDL, and you need to specify which procedure in CTX_DDL you want to run:
WHAT => 'BEGIN EXECUTE IMMEDIATE ''alter index NAME_IDX rebuild parameters (''''sync'''')''; CTX_DDL.sync_index(''NAME_IDX''); END',
However, personally I prefer to encapsulate it in a procedure (or, even better, a package) and call the procedure from the job:
CREATE PROCEDURE rebuild_name_idx IS
BEGIN
EXECUTE IMMEDIATE 'alter index NAME_IDX rebuild parameters (''sync'')';
CTX_DDL.sync_index('NAME_IDX');
END;
/
declare
v_job_id number(19,0);
begin
dbms_job.submit(
JOB => v_job_id,
WHAT => 'rebuild_name_idx;',
NEXT_DATE => SYSDATE + (1/24),
INTERVAL => 'SYSDATE + (1/24) + 7'
);
end;
/
Also, I'm pretty sure you don't actually need to rebuild the index - you only need to call CTX_DDL.sync_index
to refresh it from any DML on the table.
精彩评论