Not sure why your jobs are failing, but indexes being rebuilt every 15
minutes seems extremely excessive.
--
Robert Fazio, Oracle DBA
rfazio_at_home.com
remove nospam from reply address
http://24.8.218.197/
<todd_at_cardinalgroup.com> wrote in message
news:8fpp2n$45a$1_at_nnrp1.deja.com...
> I am trying to schedule a job that will rebuild indexes every 15
> minutes or so. It appears that it is failing when I look at user_jobs
> for this scheduled job. I am able to run the job using execute
> dbms_job.run (job_num) but when I job is run normally, it fails. I can
> also run the stored procedure without incident. Any help would be much
> appreciated.
>
> Here is a snippet of the code...
> create or replace procedure rebuildIndexes is
> l_stmt varchar2(4000);
> v_JobNum NUMBER;
> begin
>
> insert into intermedia_rebuild(time_stamp,textField) values
> (sysdate, 'START');
> DBMS_OUTPUT.PUT_LINE ('BUILDING AB TITLE');
> begin
> l_stmt := 'ALTER INDEX indx_ab_title REBUILD online
> PARAMETERS(''sync'')';
> EXECUTE IMMEDIATE l_stmt;
> end;
>
> DBMS_OUTPUT.PUT_LINE ('BUILDING CONTENT');
> insert into intermedia_rebuild(time_stamp,textField) values
> (sysdate, 'TITLE');
>
> begin
> l_stmt := 'ALTER INDEX indx_ab_content REBUILD
> online PARAMETERS(''sync'')';
> EXECUTE IMMEDIATE l_stmt;
> end;
>
> DBMS_OUTPUT.PUT_LINE ('BUILDING SHERLOCK');
> insert into intermedia_rebuild(time_stamp,textField) values
> (sysdate, 'CONTENT');
>
> begin
> l_stmt := 'ALTER INDEX indx_sherlock REBUILD online
> PARAMETERS(''sync'')';
> EXECUTE IMMEDIATE l_stmt;
> end;
> DBMS_OUTPUT.PUT_LINE ('BUILDING SOURCE');
> insert into intermedia_rebuild(time_stamp,textField) values
> (sysdate, 'SHERLOCK');
> begin
> l_stmt := 'ALTER INDEX indx_source REBUILD online
> PARAMETERS(''sync'')';
> EXECUTE IMMEDIATE l_stmt;
> end;
>
> insert into intermedia_rebuild(time_stamp,textField) values
> (sysdate, 'SOURCE');
>
> commit;
> end;
>
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue May 16 2000 - 00:00:00 CDT
