需要用到Oracle的job,于是看了文档,很长,和详细,可惜没有看到一个简单的从头至尾的例子。
27 Using the Scheduler
Oracle Database provides database job capabilities through Oracle Scheduler (the Scheduler). This chapter explains how touse the various Scheduler components, and discusses the following topics:
本地做了一个小测试,很简单。
-- 1. The procedure
create or replace procedure sp_test is
begin
insert into testtab(ddl_txt)
values ('a');
commit;
end sp_test;
/
-- 2. The job and schedule.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'test_job',
job_type => 'STORED_PROCEDURE',
job_action => 'sp_test',
start_date => sysdate,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
comments => 'test job');
END;
/
exec DBMS_SCHEDULER.ENABLE('test_job');
就可以了。
===== 后记
需要“MANAGE SCHEDULER”的系统权限
查看如下:
select * from role_sys_privs where privilege = 'MANAGE SCHEDULER';
授权如下:
SQL> grant SCHEDULER_ADMIN to xxxx;
Grant succeeded
SQL>
SQL> select * from role_sys_privs a where a.privilege = 'MANAGE SCHEDULER';
ROLE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
SCHEDULER_ADMIN MANAGE SCHEDULER YES
SQL>
关于权限角色的查询,引用如下:
http://bbs.csdn.net/topics/350162742
--1.查看所有用户:
select * from dba_users;
select * from all_users;
select * from user_users;
--2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限):
select * from dba_sys_privs;
select * from user_sys_privs;
--3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
sql>select * from role_sys_privs;
--4.查看用户对象权限:
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
--5.查看所有角色:
select * from dba_roles;
--6.查看用户或角色所拥有的角色:
select * from dba_role_privs;
select * from user_role_privs;
--7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS
--注意:
--1、以下语句可以查看Oracle提供的系统权限
select name from sys.system_privilege_map
--2、查看一个用户的所有系统权限(包含角色的系统权限)
SELECT privilege
FROM dba_sys_privs
WHERE grantee = 'DATAUSER'
UNION
SELECT privilege
FROM dba_sys_privs
WHERE grantee IN (SELECT granted_role FROM dba_role_privs WHERE grantee = 'DATAUSER');
另有一个相关的:
http://blog.csdn.net/shuangyan5230/article/details/7186811
- --授权 SCHEDULER和JOB
- GRANT SCHEDULER_ADMIN TO username;
- GRANT CREATE JOB TO scott;
- GRANT ALTER myjob1 TO scott;
- GRANT MANAGE SCHEDULER TO adam;
- Grant SELECT_CATALOG_ROLE to username;
- GRANT SCHEDULER_ADMIN TO username;
- grant create job to username;
- grant create PROCEDURE to username;
- grant CREATE TRIGGER to username;
- grant CREATE View to username;
- grant EXECUTE ANY PROCEDURE to username;
- grant MANAGE SCHEDULER to username;
- -- 开始一个JOB
- BEGIN
- DBMS_SCHEDULER.CREATE_JOB (
- job_name => 'TEST_JOB',
- job_type => 'STORED_PROCEDURE', --job的类型是执行sql语句
- job_action => 'PROC_TEST',
- start_date => sysdate,
- repeat_interval => 'freq = minutely; interval=1', --每分钟执行一次
- enabled => true,
- comments => 'MY JOB');
- END;
- /
- -- 运行JOB
- exec dbms_scheduler.enable ('TEST_JOB');
- -- 删除JOB
- BEGIN
- DBMS_SCHEDULER.DROP_JOB ( --删除job;多个job间用逗号隔开
- job_name => 'TEST_JOB',
- force => TRUE);
- END;
- ----------------------------------------------------
- -- 创建调度,每隔1个小时调度一次
- BEGIN
- DBMS_SCHEDULER.CREATE_SCHEDULE ( --创建计划任务
- schedule_name => 'KK_SCHEDULE',
- repeat_interval => 'FREQ=HOURLY; INTERVAL=1', --执行间隔:每1小时
- comments => 'Every 1 HOURS');
- END;
- /
- -- 创建job 并把它加入到scheduler里面
- BEGIN
- DBMS_SCHEDULER.CREATE_JOB (
- job_name => 'JOB_CLLCX',
- job_type => 'STORED_PROCEDURE',
- job_action => 'PROC_CLLTJ',
- schedule_name => 'KK_SCHEDULE');
- END;
- /
- -- 查询JOB
- select job_name, schedule_name from user_scheduler_jobs;
- -- JOB是否运行
- SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'JOB_CLLCX';
- -- 运行JOB
- BEGIN
- DBMS_SCHEDULER.ENABLE ('JOB_CLLCX'); /* sys.jobclass1下的所有jobs都会被enable */
- END;
--DBMS_SCHEDULER 运行信息
select job_name,state,enabled,to_char(last_start_date,'yyyy-mm-dd hh24:mi:ss'), schedule_name
from dba_scheduler_jobs;
--DBMS_SCHEDULER运行成功与否信息
SELECT log_id, job_name, status,TO_CHAR(ACTUAL_START_DATE,'yyyy-mm-dd HH24:MI:ss') start_date,
TO_CHAR (log_date, 'yyyy-mm-dd HH24:MI:ss') log_date
FROM dba_scheduler_job_run_details
WHERE job_name = 'GATHER_STATS_JOB'
order by 4 DESC;
--查询执行时间情况
select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name='MAINTENANCE_WINDOW_GROUP';
--修改执行时间
begin
dbms_scheduler.set_attribute('WEEKEND_WINDOW','REPEAT_INTERVAL','freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0');
dbms_scheduler.set_attribute('WEEKEND_WINDOW','DURATION','+002 00:00:00');
end;