dbms_jobs
dbms_scheduler
JOBs:
job_name must be specified.
plsql_block an anonymous PL/SQL Block
stored_procedure a named PL/SQL Procedure
executable scripting within Operation System
chain a named job chain object
external_script a script launched by the O.S. Command
sql_scripts a SQL*plus script
backup_script an RMAN script
Job_actions a command, chain to be run
Job_class has to do with priority and integrity with of the scheduler with resource manager.
Repeat_Interval:
yearly, monthly, weekly, daily, hourly, minutely, secondly
bymonth, byweek, byyearday, bymothday, byhour, byminute, bysecond
e.g.
Repeat_Interval=>'freq=yearly; bymonth=jan,mar,may; bymonthday=2'
Repeat_Interval=>'freq=weekly; interval=2, byday=mon; byhour=6; byminute=10'
To grant or revoke provileges
GRANT SCHEDULER_ADMIN TO username;
GRANT CREATE JOB TO username;
GRANT MANAGE SCHEDULER TO adam;
to set chain provileges
BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( DBMS_RULE_ADM.CREATE_RULE_OBJ, 'username'),
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE ( DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 'username'),
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE ( DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, 'username')
END;
/
To see all running jobs
vi select_all_scheduler_running_jobs.sql
SELECT * FROM ALL_SCHEDULER_RUNNING_JOBS;To see jobs;
to see chain of a job
vi select all_scheduler_running_chains.sql
SELECT * FROM ALL_SCHEDULER_RUNNING_CHAINS WHERE JOB_NAME='&MY_JOB1';
to see windows log
vi select dba_scheduler_windows_log.sql
col WINDOW_NAME format a20
SELECT log_id, to_char(log_date, 'DD-MON-YY HH24:MM:SS') timestamp,
window_name, operation FROM DBA_SCHEDULER_WINDOW_LOG;
to see windows detail
vi select dba_scheduler_windows_details.sql
SELECT LOG_ID, WINDOW_NAME, ACTUAL_START_DATE, ACTUAL_DURATION
FROM DBA_SCHEDULER_WINDOW_DETAILS;
exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','90');
To see jobs
col OWNER format a15col JOB_NAME format a35
col JOB_TYPE format a20
col JOB_ACTION format a70
set line 300
set pagesize 100
select OWNER, JOB_NAME, JOB_TYPE, JOB_ACTION from DBA_SCHEDULER_JOBS;
col OWNER format a15
col JOB_NAME format a35
col JOB_TYPE format a20
col JOB_ACTION format a70
set line 300
set pagesize 100
select OWNER, JOB_NAME, LAST_START_DATE, LAST_RUN_DURATION, NEXT_RUN_DATE, MAX_RUN_DURATION from DBA_SCHEDULER_JOBS;
To Create a Job
alter session set nls_date_format='DD-MON-YYYY:HH:MI:SS';
select sysdate from dual;
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'RMAN.DB2',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(''RMAN'',''DB''); END;',
start_date => '10-NOV-2017:03:55:27',
repeat_interval => 'FREQ=DAILY',
end_date => '14-NOV-2017:03:55:27',
enabled => TRUE,
comments => 'Gather table statistics rman.db');
END;
/
col OWNER format a15
col JOB_NAME format a35
col JOB_TYPE format a20
col JOB_ACTION format a70
set line 300
set pagesize 100
select OWNER, JOB_NAME, JOB_TYPE, JOB_ACTION from DBA_SCHEDULER_JOBS
where JOB_NAME='&JOB_NAME' AND OWNER='&OWNER';
to change priority
select OWNER, JOB_NAME, JOB_PRIORITY from dba_scheduler_jobs
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
name => '&my_emp_job1',
attribute => 'job_priority',
value => &1);
END;
/
to create class of jobs
note: to check consumer resource
col owner format a15
col JOB_CLASS_NAME format a30
col RESOURCE_CONSUMER_GROUP format a20
col COMMENTS format a50
col SERVICE format a20
set line 300
SELECT * FROM DBA_SCHEDULER_JOB_CLASSES;
BEGIN
DBMS_SCHEDULER.CREATE_JOB_CLASS (
job_class_name => '&my_jobclass1',
resource_consumer_group => '&my_res_group1',
comments => '&This is my first job class.');
END;
/
to create windows
alter session set nls_date_format='DD-MON-YYYY:HH:MI:SS';
select sysdate from dual;
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'oe.my_job1',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(''oe'',
''sales''); END;',
start_date => '10-NOV-2017:03:55:27',
repeat_interval => 'FREQ=DAILY',
end_date => '10-NOV-2017:03:55:27',
enabled => TRUE,
comments => 'Gather table statistics');
END;
/
