Schedule a job using DBMS_SCHEDULER

In this article I show how you can setup automatic rebuild of Portal indexes every day at 04.00 am.

Create your PL/SQL procedure

sqlplus portal/<password>

CREATE OR REPLACE PROCEDURE my_analyze_portal IS
BEGIN
wwsbr_stats.delete_stats;
wwsbr_stats.gather_stats;
wwsbr_stats.enable_monitoring;
wwsbr_stats.gather_stale;
commit;
END;
/

Submit the job

sqlplus portal/<password>

BEGIN
	DBMS_SCHEDULER.create_job (
    job_name        => 'job_my_analyze_portal',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'my_analyze_portal',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=4; byminute=0; bysecond=0',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Refresh Portal indexes.');
END;
/

COMMIT;

EXIT

View the job

You can view the current status of this job at any time.

SELECT job_name, enabled, last_start_date,next_run_date,comments
FROM user_scheduler_jobs
WHERE job_action='my_analyze_portal';