/*
|| Oracle 10g Scheduler - Listing 1
||
|| Contains various DBMS_SCHEDULER examples for demonstration of 
|| basic Oracle 10g Scheduler features.
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g 
|| Scheduler and should be carefully proofread before executing it against 
|| any existing Oracle database to insure that no potential damage can occur.
||
*/

CONNECT SYS / AS SYSDBA;
 
-----
-- Listing 1.1: Simple scheduling
-----
BEGIN
	DBMS_SCHEDULER.DROP_JOB (
		 job_name => 'HR_STATS_REFRESH'
		,force => TRUE);
	DBMS_SCHEDULER.CREATE_JOB (
		 job_name => 'HR_STATS_REFRESH'
		,job_type => 'PLSQL_BLOCK'
		,job_action => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(''HR'');DBMS_STATS.GATHER_SCHEMA_STATS(''SLSMGR'');DBMS_STATS.GATHER_SCHEMA_STATS(''SH'');END;'
		,start_date => '09/01/2004 09:00:00.000000'
		,repeat_interval => 'FREQ=DAILY'
		,enabled => FALSE
		,comments => 'Refreshes the HR Schema every night at 9 PM'
    );
END;
/

 
-----
-- Listing 1.2: Creating a Schedule Object
-----
BEGIN
    DBMS_SCHEDULER.DROP_SCHEDULE(
         schedule_name => 'FreshenSchemas'
        ,force => TRUE
    );
	DBMS_SCHEDULER.CREATE_SCHEDULE (
         schedule_name => 'FreshenSchemas'
        ,start_date => '10/03/2004 18:00:00'
		,repeat_interval => 'FREQ=WEEKLY'
		,comments => 'Weekly schema statistics refresh'
    );
END;
/

 
-----
-- Listing 1.3: Creating a Simple Program Object 
-----
BEGIN
	DBMS_SCHEDULER.CREATE_PROGRAM (
         program_name => 'FreshenHRSchema'
        ,program_type => 'PLSQL_BLOCK'
        ,program_action => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(''HR'');END;'
        ,number_of_arguments => 0
        ,enabled => TRUE
		,comments => 'Freshens statistics for all objects in HR schema only'
    );
END;
/

 
-----
-- Listing 1.4: Creating a Program Object with arguments
-----
-- Create stored procedure to perform schema refreshes
CREATE OR REPLACE PROCEDURE sp_gather_stats (
     schema      IN  VARCHAR2
    ,cascade     IN  INTEGER
)
IS
    b_cascade BOOLEAN := SYS.DIUTIL.INT_TO_BOOL(cascade);
BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS(ownname => schema, cascade => b_cascade);
END;
/

BEGIN
    -- Create a new Program in disabled status
	DBMS_SCHEDULER.CREATE_PROGRAM (
         program_name => 'FreshenSchema'
        ,program_type => 'STORED_PROCEDURE'
        ,program_action => 'SP_GATHER_STATS'
        ,number_of_arguments => 2
        ,enabled => FALSE
		,comments => 'Freshens statistics for all objects in the specified schema'
    );
    -- Create the program's arguments
	DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
         program_name => 'FreshenSchema'
        ,argument_position => 1
        ,argument_name => 'schema'
        ,argument_type => 'VARCHAR2'
        ,default_value => 'HR'
    );
	DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
         program_name => 'FreshenSchema'
        ,argument_position => 2
        ,argument_name => 'cascade'
        ,argument_type => NUMBER
        ,default_value => 1
    );
    -- Enable the Program for scheduling
	DBMS_SCHEDULER.ENABLE (
         name => 'FreshenSchema'
    );
END;
/

 
-----
-- Listing 1.5: Creating a Job Using Existing Schedule and Program Objects
-----
BEGIN
	DBMS_SCHEDULER.DROP_JOB (
		 job_name => 'HR_FRESHENSCHEMA'
		,force => TRUE);

	DBMS_SCHEDULER.CREATE_JOB (
		 job_name => 'HR_FRESHENSCHEMA'
		,program_name => 'FRESHENSCHEMA'
		,schedule_name => 'FRESHENSCHEMAS'
		,enabled => FALSE
		,comments => 'Refreshes the HR Schema every week'
    );
END;
/

 
-----
-- Listing 1.6: Creating additional jobs using COPY_JOB
-----
BEGIN
    -- Create OE schema refresh job
	DBMS_SCHEDULER.COPY_JOB (
		 old_job => 'HR_FRESHENSCHEMA'
		,new_job => 'SLSMGR_FRESHENSCHEMA'
    );
    -- Create SH schema refresh job
	DBMS_SCHEDULER.COPY_JOB (
		 old_job => 'HR_FRESHENSCHEMA'
		,new_job => 'SH_FRESHENSCHEMA'
    );
END;
/

 
-----
-- Listing 1.7: Creating job arguments using SET_JOB_ARGUMENT_VALUE
-----
BEGIN
	DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
		 job_name => 'HR_FRESHENSCHEMA'
		,argument_position => 1
		,argument_value => 'HR'
    );
	DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
		 job_name => 'HR_FRESHENSCHEMA'
		,argument_position => 2
		,argument_value => 1
    );
	DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
		 job_name => 'SLSMGR_FRESHENSCHEMA'
		,argument_position => 1
		,argument_value => 'SLSMGR'
    );
	DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
		 job_name => 'SLSMGR_FRESHENSCHEMA'
		,argument_position => 2
		,argument_value => 1
    );
	DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
		 job_name => 'SH_FRESHENSCHEMA'
		,argument_position => 1
		,argument_value => 'SH'
    );
	DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
		 job_name => 'SH_FRESHENSCHEMA'
		,argument_position => 2
		,argument_value => 1
    );
END;
/

 
-----
-- Listing 1.8: Enable jobs to execute
-----
BEGIN
    DBMS_SCHEDULER.ENABLE('HR_FRESHENSCHEMA');
    DBMS_SCHEDULER.ENABLE('SLSMGR_FRESHENSCHEMA');
    DBMS_SCHEDULER.ENABLE('SH_FRESHENSCHEMA');
END;
/

 
-----
-- Listing 1.9: Change the Schedule to run the jobs on a daily basis
-----

-- Change the Schedule's attributes to run the job every five minutes
-- starting and ending at a new date and time
BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
		 name => 'FRESHENSCHEMAS'
		,attribute => 'start_date'
		,value => '09/23/2004 19:05:00'
    );
    DBMS_SCHEDULER.SET_ATTRIBUTE(
		 name => 'FRESHENSCHEMAS'
		,attribute => 'repeat_interval'
		,value => 'FREQ=MINUTELY;INTERVAL=5'
    );
    DBMS_SCHEDULER.SET_ATTRIBUTE(
		 name => 'FRESHENSCHEMAS'
		,attribute => 'end_date'
		,value => '09/23/2004 19:25:00'
    );
END;
/

-- Disable the jobs, and then re-enable them to get modified Schedule 
-- start date, end date, and interval propagated to the jobs
BEGIN
    DBMS_SCHEDULER.DISABLE('HR_FRESHENSCHEMA');
    DBMS_SCHEDULER.DISABLE('SLSMGR_FRESHENSCHEMA');
    DBMS_SCHEDULER.DISABLE('SH_FRESHENSCHEMA');
    -- and then 
    DBMS_SCHEDULER.ENABLE('HR_FRESHENSCHEMA');
    DBMS_SCHEDULER.ENABLE('SLSMGR_FRESHENSCHEMA');
    DBMS_SCHEDULER.ENABLE('SH_FRESHENSCHEMA');
END;
/