dbms_scheduler example
prompt Show Scheduler database objects
TTITLE 'Scheduler Objects:'
COL owner FORMAT A20 HEADING 'Owner'
COL object_type FORMAT A12 HEADING 'Object|Type'
COL object_name FORMAT A30 HEADING 'Name'
COL created FORMAT A20 HEADING 'Created On'
COL status FORMAT A12 HEADING 'Status'
SELECT
owner ,object_type ,object_name ,created ,status
FROM dba_objects
WHERE object_type IN ('PROGRAM', 'JOB', 'JOB CLASS', 'SCHEDULE', 'WINDOW')
ORDER BY object_type, OBJECT_name;
Object
Owner Type Name Created On Status
-------------------- ------------ ------------------------------ -------------------- ------------
SYS JOB BSLN_MAINTAIN_STATS_JOB 04-JAN-19 VALID
SYS JOB CLEANUP_NON_EXIST_OBJ 04-JAN-19 VALID
prompt Show Schedule objects
prompt 'Schedules:'
COL owner FORMAT A08 HEADING 'Owner'
COL schedule_name FORMAT A30 HEADING 'Schedule'
COL start_date FORMAT A20 HEADING 'Starts|On'
COL end_date FORMAT A20 HEADING 'Ends|On'
COL repeat_interval FORMAT A45 HEADING 'Interval'
SELECT
owner ,schedule_name ,to_char(start_date, 'mm/dd/yyyy hh24:mi:ss') start_date ,to_char(end_date, 'mm/dd/yyyy hh24:mi:ss') end_date ,repeat_interval
FROM dba_scheduler_schedules;
prompt Show Program objects : What program objects are available?
TTITLE 'Programs:'
COL owner FORMAT A08 HEADING 'Owner'
COL program_name FORMAT A30 HEADING 'Program'
COL program_type FORMAT A16 HEADING 'Type'
COL program_action FORMAT A60 HEADING 'Action'
SELECT
owner
,program_name
,program_type
,program_action
FROM dba_scheduler_programs;
Owner Program Type Action
-------- ------------------------------ ---------------- ------------------------------------------------------------
SYS PURGE_LOG_PROG STORED_PROCEDURE dbms_scheduler.auto_purge
SYS FILE_WATCHER_PROGRAM STORED_PROCEDURE dbms_isched.file_watch_job
SYS PMO_DEFERRED_GIDX_MAINT PLSQL_BLOCK dbms_part.cleanup_gidx_internal(
prompt What program's arguments are attached?
TTITLE 'Program Arguments:'
COL owner FORMAT A20 HEADING 'Owner'
COL program_name FORMAT A30 HEADING 'Program'
COL argument_name FORMAT A12 HEADING 'Arg Name'
COL argument_position FORMAT 999 HEADING 'Arg|Pos'
COL argument_type FORMAT A25 HEADING 'Arg Type'
COL default_value FORMAT A12 HEADING 'Default|Value'
COL out_argument FORMAT A06 HEADING 'Out|Arg?'
SELECT owner ,program_name ,argument_name ,argument_position ,argument_type ,default_value ,out_argument FROM dba_scheduler_program_args;
Arg Default Out
Owner Program Arg Name Pos Arg Type Value Arg?
-------------------- ------------------------------ ------------ ---- ------------------------- ------------ ------
SYS HS_PARALLEL_SAMPLING 9 SYS.HSBLKNAMLST FALSE
SYS HS_PARALLEL_SAMPLING 10 SYS.HSBLKNAMLST FALSE
prompt Show Job objects
COL owner FORMAT A20 HEADING 'Owner'
COL job_name FORMAT A30 HEADING 'Job'
COL program_name FORMAT A20 HEADING 'Program|Name'
COL job_class FORMAT A35 HEADING 'Job Class'
COL job_type FORMAT A12 HEADING 'Job|Type'
COL job_action FORMAT A50 HEADING 'Job|Action'
prompt 'Jobs:---- '
SELECT owner ,job_name ,state ,job_class ,job_type ,job_action FROM dba_scheduler_jobs;
Job Job
Owner Job State Job Class Type Action
-------------------- ------------------------------ --------------- ----------------------------------- ------------ --------------------------------------------------
SYS PURGE_LOG SCHEDULED DEFAULT_JOB_CLASS
SYS FILE_WATCHER DISABLED SCHED$_LOG_ON_ERRORS_CLASS
COL owner FORMAT A08 HEADING 'Owner'
COL job_name FORMAT A30 HEADING 'Job'
COL program_name FORMAT A30 HEADING 'Program|Name'
COL schedule_name FORMAT A30 HEADING 'Schedule|Name'
COL enabled FORMAT A08 HEADING 'Enabled?'
COL state FORMAT A15 HEADING 'State'
COL restartable FORMAT A08 HEADING 'Restart|-able?'
COL start_date FORMAT A37 HEADING 'Start|Date'
prompt 'Job Components:'
SELECT
owner ,job_name ,program_name ,schedule_name ,enabled ,state ,restartable ,start_date
FROM dba_scheduler_jobs;
Program Schedule Restart Start
Owner Job Name Name Enabled? State -able? Date
-------- ------------------------------ ------------------------------ ------------------------------ -------- --------------- -------- -------------------------------------
SYS PURGE_LOG PURGE_LOG_PROG DAILY_PURGE_SCHEDULE TRUE SCHEDULED FALSE 08-JUL-19 03.00.00.584504 AM ETC/GMT
SYS FILE_WATCHER FILE_WATCHER_PROGRAM FILE_WATCHER_SCHEDULE FALSE DISABLED FALSE
prompt What are a Job's arguments?
COL job_name FORMAT A30 HEADING 'Job'
COL argument_name FORMAT A12 HEADING 'Arg Name'
COL argument_position FORMAT 999 HEADING 'Arg|Pos'
COL value FORMAT A32 HEADING 'Argument Value'
prompt 'Job Arguments:'
SELECT job_name ,argument_name ,argument_position ,value FROM dba_scheduler_job_args;
Arg
Job Arg Name Pos Argument Value
------------------------------ ------------ ---- --------------------------------
DRA_REEVALUATE_OPEN_FAILURES 1 TRUE
DRA_REEVALUATE_OPEN_FAILURES 2 TRUE
DRA_REEVALUATE_OPEN_FAILURES 3 TRUE
DRA_REEVALUATE_OPEN_FAILURES 4 900
prompt Show Scheduled Tasks History: Show a high-level view of scheduled task execution history
COL log_id FORMAT 9999 HEADING 'Log#'
COL log_date FORMAT A37 HEADING 'Log Date'
COL owner FORMAT A06 HEADING 'Owner'
COL job_name FORMAT A30 HEADING 'Job'
COL status FORMAT A10 HEADING 'Status'
prompt 'Scheduled Tasks History:'
SELECT
log_id ,log_date ,owner ,job_name ,status
FROM dba_scheduler_job_log
where 1=1
and log_date>sysdate -1 ;
Log# Log Date Owner Job Status
----- ------------------------------------- ------ ------------------------------ ----------
7344 14-MAR-23 10.00.00.355353 PM +00:00 SYS ORA$AT_OS_OPT_SY_230623
7346 14-MAR-23 10.00.00.357186 PM +00:00 SYS ORA$AT_OS_OPT_SY_230623
7348 14-MAR-23 10.00.00.358230 PM +00:00 SYS ORA$AT_OS_OPT_SY_230623
7350 14-MAR-23 10.00.00.360661 PM +00:00 SYS ORA$AT_SA_SPC_SY_230624
prompt What scheduled tasks failed during execution, and Error details
COL log_id FORMAT 9999 HEADING 'Log#'
COL log_date FORMAT A32 HEADING 'Log Date'
COL owner FORMAT A06 HEADING 'Owner'
COL job_name FORMAT A20 HEADING 'Job'
COL status FORMAT A10 HEADING 'Status'
COL actual_start_date FORMAT A32 HEADING 'Actual|Start|Date'
COL error# FORMAT 999999 HEADING 'Error|Nbr'
prompt 'Scheduled Tasks That Failed:'
SELECT
log_id ,log_date ,owner ,job_name ,status ,actual_start_date ,error# FROM dba_scheduler_job_run_details
WHERE status <> 'SUCCEEDED'
ORDER BY actual_start_date;
prompt Which jobs are running right now ?
COL owner FORMAT A06 HEADING 'Owner'
COL job_name FORMAT A20 HEADING 'Job'
COL session_id FORMAT 999999 HEADING 'Session'
COL running_instance FORMAT A10 HEADING 'Running|Instance'
COL elapsed_time FORMAT A12 HEADING 'Elapsed|Time'
TTITLE 'Scheduled Tasks Running Right Now:'
SELECT owner ,job_name ,session_id ,running_instance ,elapsed_time FROM dba_scheduler_running_jobs;
column job_name format a20
column status format a12
column actual_start_date format a36
column run_duration format a14
select
job_name, status, actual_start_date, run_duration
from
dba_scheduler_job_run_details
where
job_name = 'GATHER_STATS_JOB'
order by
actual_start_date
;
select
error#,
status
from
dba_scheduler_job_run_details
where
status like '%FAIL%';
set feedback off
set echo off
set lines 205
column owner format a15
column job_name format a22
column program_name format a24
column status format a10
column state format a10
column last_start format a25
column last_start_norm format a18
column "DURATION (d:hh:mm:ss)" format a21
column next_run format a25
column next_run_norm format a18
var local_offset number
begin
select extract(timezone_hour from systimestamp) into :local_offset from dual;
end;
/
select dsj.owner,
dsj.job_name,
dsj.program_name,
dsjlmax.status,
dsj.state,
-- to_char(dsj.last_start_date,'dd-mon-yyyy hh24:mi TZH:TZM') last_start,
to_char(dsj.last_start_date + (:local_offset-extract(timezone_hour from dsj.last_start_date))/24,'dd-mon-yyyy hh24:mi') last_start_norm,
extract(day from dsj.last_run_duration) ||':'||
lpad(extract(hour from dsj.last_run_duration),2,'0')||':'||
lpad(extract(minute from dsj.last_run_duration),2,'0')||':'||
lpad(round(extract(second from dsj.last_run_duration)),2,'0') "DURATION (d:hh:mm:ss)",
-- to_char(dsj.next_run_date,'dd-mon-yyyy hh24:mi TZH:TZM') next_run,
to_char(dsj.next_run_date + (:local_offset-extract(timezone_hour from dsj.next_run_date))/24,'dd-mon-yyyy hh24:mi') next_run_norm
from dba_scheduler_jobs dsj,
(select job_name, status
from dba_scheduler_job_log dsjl
where log_date =
(select max(log_date)
from dba_scheduler_job_log dsjl2
where dsjl2.job_name = dsjl.job_name)
) dsjlmax
where dsj.owner in ('SYSTEM','SYS')
and dsj.job_name = dsjlmax.job_name;
==========
dbms_scheduler example
begin
dbms_scheduler.create_job(
job_name => 'SCOTT_JOB_SCHEDULE'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin dbms_stats.gather_schema_stats(ownname =>"SCOTT",
estimate_percent => 30,
cascade=>true,
method_opt=> 'for all columns size 1',
degree => 5 ,
options => 'GATHER STALE '); end; '
,start_date => trunc(sysdate + 1) + 6/24
,repeat_interval => 'FREQ=DAILY'
,enabled => TRUE
,comments => 'customized stats gathering for risk engine');
end;
begin
dbms_scheduler.create_job (
Job_name=> 'anuj_job',
Job_type=> 'PLSQL_BLOCK',
Job_action=> 'update employees set salary = salary * 1.5;',
Start_date=> '10-dec-2011 06:00:00 pm',
Repeat_interval=> 'FREQ=YEARLY',
Comments=> 'testing..... '
);
end;
/
PL/SQL procedure successfully completed.
SQL> select job_name, enabled, run_count from user_scheduler_jobs;
JOB_NAME ENABL RUN_COUNT
------------------------------ ----- ----------
SM$CLEAN_AUTO_SPLIT_MERGE TRUE 1
RSE$CLEAN_RECOVERABLE_SCRIPT TRUE 1
PURGE_LOG TRUE 1
ORA$AUTOTASK_CLEAN TRUE 1
HM_CREATE_OFFLINE_DICTIONARY FALSE 0
FGR$AUTOPURGE_JOB FALSE 0
DRA_REEVALUATE_OPEN_FAILURES TRUE 0
BSLN_MAINTAIN_STATS_JOB TRUE 1
ANUJ_JOB FALSE 0
FILE_WATCHER FALSE 0
XMLDB_NFS_CLEANUP_JOB FALSE 0
11 rows selected.
exec dbms_scheduler.copy_job('anuj_job','anuj_job_copy');
SQL> exec dbms_scheduler.copy_job('anuj_job','anuj_job_copy');
PL/SQL procedure successfully completed.
exec dbms_scheduler.stop_job(job_name=> 'anuj_job', force=> true);
SQL> exec dbms_scheduler.drop_job('anuj_job');
PL/SQL procedure successfully completed.
SQL> select job_name, enabled, run_count from user_scheduler_jobs;
JOB_NAME ENABL RUN_COUNT
------------------------------ ----- ----------
ANUJ_JOB_COPY FALSE 0
SM$CLEAN_AUTO_SPLIT_MERGE TRUE 1
RSE$CLEAN_RECOVERABLE_SCRIPT TRUE 1
FGR$AUTOPURGE_JOB FALSE 0
BSLN_MAINTAIN_STATS_JOB TRUE 1
DRA_REEVALUATE_OPEN_FAILURES TRUE 0
HM_CREATE_OFFLINE_DICTIONARY FALSE 0
ORA$AUTOTASK_CLEAN TRUE 1
PURGE_LOG TRUE 1
FILE_WATCHER FALSE 0
XMLDB_NFS_CLEANUP_JOB FALSE 0
11 rows selected.
exec dbms_scheduler.run_job('ANUJ_JOB_COPY', true);
exec dbms_scheduler.run_job('ANUJ_JOB_COPY', true);
SQL> exec dbms_scheduler.set_attribute ( name => 'ANUJ_JOB_COPY',attribute => 'job_priority', value => 1 );
PL/SQL procedure successfully completed.
EXEC dbms_scheduler.run_job('ANUJ_JOB_COPY') ;
BEGIN
DBMS_SCHEDULER.ENABLE('ANUJ_JOB_COPY');
END;
PL/SQL procedure successfully completed.
BEGIN
DBMS_SCHEDULER.DISABLE('ANUJ_JOB_COPY');
END;
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'anuj_full_job1',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined entirely by the CREATE JOB procedure.');
END;
PL/SQL procedure successfully completed.
BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => 'anuj_full_job1');
END;
DECLARE
l_job NUMBER := 0;
BEGIN
DBMS_JOB.SUBMIT(l_job,'anuj_full_job;',sysdate,'TRUNC(sysdate)+1+1/288');
END;
BEGIN
DBMS_SCHEDULER.ENABLE('ANUJ_FULL_JOB');
END;
EXEC dbms_scheduler.run_job('ANUJ_FULL_JOB');
EXEC dbms_scheduler.run_job('ANUJ_FULL_JOB');
PL/SQL procedure successfully completed.
SQL> select job_name, enabled, run_count from user_scheduler_jobs;
JOB_NAME ENABL RUN_COUNT
------------------------------ ----- ----------
ANUJ_JOB_COPY TRUE 0
ANUJ_FULL_JOB TRUE 0
SM$CLEAN_AUTO_SPLIT_MERGE TRUE 1
RSE$CLEAN_RECOVERABLE_SCRIPT TRUE 1
FGR$AUTOPURGE_JOB FALSE 0
BSLN_MAINTAIN_STATS_JOB TRUE 1
DRA_REEVALUATE_OPEN_FAILURES TRUE 0
HM_CREATE_OFFLINE_DICTIONARY FALSE 0
ORA$AUTOTASK_CLEAN TRUE 1
PURGE_LOG TRUE 1
FILE_WATCHER FALSE 0
XMLDB_NFS_CLEANUP_JOB FALSE 0
12 rows selected.
SELECT job_name, operation, status, user_name
FROM dba_scheduler_job_log
WHERE operation != 'RUN'
and owner = 'SCOTT';
SELECT * FROM dba_scheduler_jobs
Monitoring job-scheduling
Jobs can be monitored using Oracle Enterprise Manager 10g. It's also possible to use a number of views that have been created in
Oracle 10g. We will discuss some of these views here.
To show details on job run:
select log_date
, job_name
, status
, req_start_date
, actual_start_date
, run_duration
from dba_scheduler_job_run_details
To show running jobs:
select job_name
, session_id
, running_instance
, elapsed_time
, cpu_used
from dba_scheduler_running_jobs;
To show job history:
select log_date
, job_name
, status
from dba_scheduler_job_log;
show all schedules:
select schedule_name, schedule_type, start_date, repeat_interval
from dba_scheduler_schedules;
show all jobs and their attributes:
http://www.oracledatabase12g.com/archives/%E4%BA%86%E8%A7%A3sysdate%E5%87%BD%E6%95%B0.html
select * from dba_scheduler_jobs
show all program-objects and their attributes
select * from dba_scheduler_programs;
show all program-arguments:
select * from dba_scheduler_program_args;
select to_char(sysdate,'YEAR-MON-DAY HH24:MI:SS') from dual;
SQL> select to_char(sysdate,'YEAR-MON-DAY HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YEAR-MON-DAYHH24:MI:SS')
-----------------------------------------------------------------
TWENTY ELEVEN-DEC-SATURDAY 22:59:13
set linesize 200 pagesize 1400;
col sessiontimezone for a20
SQL> select current_date, sysdate,dbtimezone,sessiontimezone from dual;
CURRENT_DATE SYSDATE DBTIME SESSIONTIMEZONE
------------------- ------------------- ------ --------------------
2011-08-24 19:53:09 2011-08-24 19:53:09 +08:00 +08:00
select current_date, sysdate,dbtimezone,sessiontimezone from dual;
CURRENT_D SYSDATE DBTIME SESSIONTIMEZONE
--------- --------- ------ ---------------------------------------------------------------------------
10-DEC-11 10-DEC-11 +00:00 +00:00
Window issues:
Note 742683.1 Scheduled Job Works Does Not Start In Window.
Purging Diagnostics
Note 443364.1 How To Purge DBA_SCHEDULER_JOB_LOG and DBA_SCHEDULER_WINDOW_LOG
Note 749440.1 Dbms_scheduler.Purge Not Removing Entries from dba_scheduler_job_run_details
Schedule issues:
Note 312547.1 Advanced use of DBMS_JOB.SUBMIT 'interval' Parameter
Note 428872.1 Scheduled Jobs Do Not Run After A Re-Start With Repeat_interval => Null.
Issues with Statistic Jobs:
Note 430636.1 GATHER_STATS_JOB Is Not Running
Note 377143.1 How to check what automatic statistics collection is scheduled on 10g
Note 803191.1 Checklist To Diagnose Issues Related To Automatic Run Of
Job GATHER_STATS_JOB
Issues with external Jobs:
http://forums.oracle.com/forums/thread.jspa?threadID=555102
NOTE:389685.1 – Items to Check when Problems Running Executables through the Scheduler
NOTE:976049.1 DBMS_SCHEDULER fails with ORA-27369 and
STANDARD_ERROR=”Launching external job failed: Invalid username or password”
NOTE:1300215.1 ORA-27369: job of type EXECUTABLE failed with exit code: 274667
Please note that this article will guide through the correct analysis for Scheduler/Job Problems.
It is also practicable to go through a list of known issues / bugs which will be listed in
NOTE:1311355.1 Known Issues And Bugs With Using the Scheduler
This article is currently under edit and will be made publish in the near future
Keywords
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
JOB_NAME => 'MY_MV_REFRESH',
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION => 'BEGIN mv_refresh_job(''EXEC DBMS_MVIEW.REFRESH(''MV_NAME_HERE'', ''C'')''); END;',
START_DATE => SYSTIMESTAMP,
REPEAT_INTERVAL => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
END_DATE => NULL,
ENABLED => TRUE,
COMMENTS => 'Refreshes Materialized Views on a Scheduled Basis (12:00AM each day).');
END;
/
SELECT job_name, program_name FROM dba_scheduler_jobs WHERE job_style='LIGHTWEIGHT';
begin
dbms_scheduler.create_job(job_name => 'ENGINE_JOB_SCHEDULE'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin
dbms_stats.gather_schema_stats(ownname =>"SCOTT", estimate_percent => 100
, cascade=>true,method_opt=> "for all columns size 1"
,degree => 5 , options => "GATHER STALE"); end;'
,start_date => trunc(sysdate + 1) + 6/24
,repeat_interval => 'FREQ=DAILY'
,enabled => TRUE
,comments => 'customized stats gathering for risk engine');
end;
SQL> begin
2
3 dbms_scheduler.create_job(job_name => 'ENGINE_JOB_SCHEDULE'
4 ,job_type => 'PLSQL_BLOCK'
5 ,job_action => 'begin
6 dbms_stats.gather_schema_stats(ownname =>"SCOTT", estimate_percent => 100
7 , cascade=>true,method_opt=> "for all columns size 1"
8 ,degree => 5 , options => "GATHER STALE"); end;'
9 ,start_date => trunc(sysdate + 1) + 6/24
10 ,repeat_interval => 'FREQ=DAILY'
,enabled => TRUE
11 12 ,comments => 'customized stats gathering for risk engine');
13
14 end;
15 /
PL/SQL procedure successfully completed.
grant execute on dbms_scheduler to scott;
exec dbms_scheduler.run_job('ENGINE_JOB_SCHEDULE');
set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;
session_jobs.sql
set linesize 250
col sid for 9999 head 'Session|ID'
col spid head 'O/S|Process|ID'
col serial# for 9999999 head 'Session|Serial#'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;
running_jobs.sql
set linesize 250
col sid for 9999 head 'Session|ID'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;
exec dbms_scheduler.drop_job('ENGINE_JOB_SCHEDULE');
SQL> exec dbms_scheduler.drop_job('ENGINE_JOB_SCHEDULE');
PL/SQL procedure successfully completed.
'begin
dbms_stats.gather_schema_stats(ownname =>'''SCOTT''', estimate_percent => 100
, cascade=>true,method_opt=> '''for all columns size 1'''
,degree => 5 , options => '''GATHER STALE''');
end;'
To show details on job run:
select log_date
, job_name
, status
, req_start_date
, actual_start_date
, run_duration
from dba_scheduler_job_run_details
To show running jobs:
select job_name
, session_id
, running_instance
, elapsed_time
, cpu_used
from dba_scheduler_running_jobs;
To show job history:
select log_date
, job_name
, status
from dba_scheduler_job_log;
show all schedules:
select schedule_name, schedule_type, start_date, repeat_interval
from dba_scheduler_schedules;
show all jobs and their attributes:
select *
from dba_scheduler_jobs
show all program-objects and their attributes
select *
from dba_scheduler_programs;
show all program-arguments:
select *
from dba_scheduler_program_args;
select 'exec dbms_scheduler.disable(' || ''''||OWNER || '.' || job_name || '''' || ');' Disable_Below_Jobs from dba_scheduler_jobs where STATE='RUNNING' and owner ='SYS';
CREATE TABLE reminders(message VARCHAR2(4000),msg_ts TIMESTAMP );
DECLARE
l_job_action VARCHAR2(500) :=
'BEGIN '
||'INSERT INTO reminders( message, msg_ts) '
||'VALUES(''What time do you call this ?'', SYSTIMESTAMP); '
||'END;';
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'wakeup_sun',
job_type => 'PLSQL_BLOCK',
job_action => l_job_action,
start_date => SYSDATE,
repeat_interval => 'freq=weekly; byday=SUN; byhour=8; byminute=30',
end_date => NULL,
enabled => TRUE,
comments => 'Do something automatically on Sunday morning');
END;
/
BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => 'wakeup_sun');
END;
/
SELECT * FROM reminders;
DECLARE
l_job_action VARCHAR2(500) :=
'BEGIN '
||'INSERT INTO reminders( message, msg_ts) '
||'VALUES(''What time do you call this ?'', SYSTIMESTAMP); '
||'END;';
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'wakeup_sun',
job_type => 'PLSQL_BLOCK',
job_action => l_job_action,
start_date => SYSDATE,
repeat_interval => 'freq=weekly; byday=SUN; byhour=8; byminute=30',
end_date => NULL,
enabled => TRUE,
comments => 'Do something automatically on Sunday morning');
END;
/
begin
dbms_scheduler.create_job(job_name => 'ENGINE_JOB_SCHEDULE1'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin
dbms_stats.gather_schema_stats(ownname =>''SCOTT'', estimate_percent => 100
, cascade=>true,method_opt=> ''for all columns size 1''
,degree => 5 , options => ''GATHER STALE''); end;'
,start_date => trunc(sysdate + 1) + 6/24
,repeat_interval => 'FREQ=DAILY'
,enabled => TRUE
,comments => 'customized stats gathering for risk engine');
end;
/
BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => 'ENGINE_JOB_SCHEDULE1');
END;
/
BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => 'ENGINE_JOB_SCHEDULE1');
END;
/
PL/SQL procedure successfully completed.
begin
dbms_scheduler.create_schedule(
schedule_name => 'DEMO_SCHEDULE'
, start_date => '01/01/2006 22:00:00'
, repeat_interval => 'FREQ=WEEKLY'
, comments => 'Weekly at 22:00');
END;
To drop the schedule:
begin
dbms_scheduler.drop_schedule( schedule_name => 'DEMO_SCHEDULE' , force => TRUE );
end;
/
begin
dbms_scheduler.create_job(
job_name => 'DEMO_JOB1'
, program_name =>'DEMO_JOB_SCHEDULE'
, schedule_name =>'DEMO_SCHEDULE'
, enabled => FALSE
, comments => 'Run demo program every week at 22:00');
dbms_scheduler.set_job_argument_value(
job_name => 'DEMO_JOB1'
, argument_position => 1
, argument_value => 'param1');
dbms_scheduler.enable('DEMO_JOB1');
commit;
end;
/
begin
dbms_scheduler.create_job
(
job_name => 'RUN_SHELL1',
schedule_name => 'DEMO_SCHEDULE',
job_type => 'EXECUTABLE',
job_action => '/home/test/run_script.sh',
enabled => true,
comments => 'Run shell-script'
);
end;
/
Create a Job
/* Run this job every 5 minutes except between 11am and 5pm) */
begin
dbms_scheduler.create_job (
job_name => 'test_job_01',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN update test1 set col1 = col1 + 1; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=minutely; byhour=17,18,19,20,21,22,23,00,01,02,03,04,05,06,07,08,09,10; interval=5',
end_date => NULL,
enabled => TRUE,
comments => 'Job to test use of dbms_scheduler');
end;
/
Sunday, 4 March 2012
dbms_scheduler example
ORA-00845: MEMORY_TARGET not supported on this system
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 968M
memory_target big integer 968M
shared_memory_address integer 0
SQL>
alter system set memory_max_target=1200m scope=spfile;
SQL> alter system set memory_max_target=1200m scope=spfile;
System altered.
SQL> alter system set memory_target=1200m scope=spfile;
System altered.
SQL> startup force;
ORA-00845: MEMORY_TARGET not supported on this system
SQL> !df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
21G 17G 2.9G 86% /
tmpfs 1000M 420K 1000M 1% /dev/shm <<<<------------------ increase this value
/dev/sda1 485M 41M 419M 9% /boot
from root
[root@localhost ~]# mount -t tmpfs shmfs -o size=2048m /dev/shm
/etc/fstab
/dev/mapper/VolGroup-lv_root / ext4 defaults 1 1
UUID=9a9b0c22-cacf-491c-ad27-b636c69da3cf /boot ext4 defaults 1 2
/dev/mapper/VolGroup-lv_swap swap swap defaults 0 0
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
shmfs /dev/shm tmpfs size=2048m 0 0 <<<<<<<<<---- add following line
then init 6 <<<<<--- reboot
SQL> startup ;
ORACLE instance started.
Total System Global Area 1255473152 bytes
Fixed Size 1344652 bytes
Variable Size 754977652 bytes
Database Buffers 486539264 bytes
Redo Buffers 12611584 bytes
Database mounted.
============================
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 1200M
memory_target big integer 1200M
shared_memory_address integer 0
Oracle database all file info
@all_file.sql
@dba_all_file.sql
All file in database
@dba_all_file.sql
All file in database
SET LINESIZE 147
SET PAGESIZE 9999
SET VERIFY OFF
COLUMN tablespace FORMAT a29 HEADING 'Tablespace Name / File Class'
COLUMN filename FORMAT a64 HEADING 'Filename'
COLUMN filesize FORMAT 99,999,999,999 HEADING 'File Size'
COLUMN autoextensible FORMAT a4 HEADING 'Auto'
COLUMN increment_by FORMAT 99,999,999,999 HEADING 'Next'
COLUMN maxbytes FORMAT 99,999,999,999 HEADING 'Max'
BREAK ON report
COMPUTE SUM OF filesize ON report
SELECT /*+ ordered */
d.tablespace_name tablespace
, d.file_name filename
, d.bytes filesize
, d.autoextensible autoextensible
, d.increment_by * e.value increment_by
, d.maxbytes maxbytes
FROM
sys.dba_data_files d , v$datafile v , (SELECT value FROM v$parameter WHERE name = 'db_block_size') e
WHERE
(d.file_name = v.name)
UNION
SELECT
d.tablespace_name tablespace
, d.file_name filename
, d.bytes filesize
, d.autoextensible autoextensible
, d.increment_by * e.value increment_by
, d.maxbytes maxbytes
FROM
sys.dba_temp_files d , (SELECT value FROM v$parameter WHERE name = 'db_block_size') e
UNION
SELECT '[ ONLINE REDO LOG ]' , a.member , b.bytes , null , TO_NUMBER(null), TO_NUMBER(null)
FROM v$logfile a , v$log b
WHERE a.group# = b.group#
UNION
SELECT '[ CONTROL FILE ]' , a.name , TO_NUMBER(null) , null , TO_NUMBER(null), TO_NUMBER(null)
FROM v$controlfile a ORDER BY 1,2
/
SQL> /
Tablespace Name / File Class Filename File Size Auto Next Max
----------------------------- ---------------------------------------------------------------- --------------- ---- --------------- ---------------
SYSAUX +DATA/vihaan/datafile/sysaux.257.776974139 241,172,480 YES 10,485,760 34,359,721,984
SYSTEM +DATA/vihaan/datafile/system.256.776974135 503,316,480 YES 10,485,760 34,359,721,984
TEMP +DATA/vihaan/tempfile/temp.264.776974399 20,971,520 YES 655,360 34,359,721,984
UNDOTBS1 +DATA/vihaan/datafile/undotbs1.258.776974143 26,214,400 YES 5,242,880 34,359,721,984
USERS +DATA/vihaan/datafile/users.259.776974145 5,242,880 YES 1,310,720 34,359,721,984
[ CONTROL FILE ] +DATA/vihaan/controlfile/current.260.776974343
[ ONLINE REDO LOG ] +DATA/vihaan/onlinelog/group_1.261.776974351 52,428,800
[ ONLINE REDO LOG ] +DATA/vihaan/onlinelog/group_2.262.776974357 52,428,800
[ ONLINE REDO LOG ] +DATA/vihaan/onlinelog/group_3.263.776974361 52,428,800
---------------
sum 954,204,160
9 rows selected.
Subscribe to:
Posts (Atom)
