Oracle 10g schedule job的常用操作

馥954

　　-- job 权限
            grant create job to somebody;
            -- job 创建
            begin
            dbms_scheduler.create_job (
            job_name => 'AGENT_LIQUIDATION_JOB',
            job_type => 'STORED_PROCEDURE',
            job_action => 'AGENT_LIQUIDATION.LIQUIDATION', --存储过程名
            start_date => sysdate,
            repeat_interval => 'FREQ=MONTHLY; INTERVAL=1;
            BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0;BYSECOND=0', --
            按月，间隔为1个(月),每月1号,凌晨1点
            comments => '执行代理商清分程序'
            );
            end;
            /
            -- job 执行时间测试
            DECLARE
            start_date date;
            return_date_after date;
            next_run_date date;
            BEGIN
            start_date := sysdate;--to_timestamp_tz('10-OCT-2004
            10:00:00','DD-MM-YYYY HH24:MI:SS');
            return_date_after := start_date;
            FOR i IN 1..10 LOOP
            DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=MONTHLY; INTERVAL=1;
            BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0;BYSECOND=0',start_date,
            return_date_after, next_run_date);
            DBMS_OUTPUT.PUT_LINE('next_run_date: ' ||
            to_char(next_run_date,'yyyy-mm-dd HH24:MI:SS'));
            return_date_after := next_run_date;
            END LOOP;
            END;
            /
            -- job 查询
            select owner, job_name, state from dba_scheduler_jobs;
            select job_name, state from user_scheduler_jobs;
            -- job 启用
            begin
            dbms_scheduler.enable('BACKUP_JOB');
            end;
            /
            -- job 运行
            begin
            dbms_scheduler.run_job('COLA_JOB',TRUE); -- true代表同步执行
            end;
            /
            -- job 停止(不太好用)
            begin
            dbms_scheduler.stop_job(job_name => 'COLA_JOB',force => TRUE);
            end;
            /
            -- job 删除(对停job来说好用)
            begin
            dbms_scheduler.drop_job(job_name => 'COLA_JOB',force => TRUE);)
            end;
            /