DBA Hub

📋Steps in this guide1/8

How to Create and Monitor DBMS_SCHEDULER Jobs

How to Create and Monitor DBMS_SCHEDULER Jobs Step 0: Overview Step 1: Create DBMS_SCHEDULER JOB Step 2: Find the current status of job Step 3: Find the SID Step 4: How to find job is enabled(or)disabled Step 5: How to Disbale DBMS_SCHEDULER job Step 6: How to Enable DBMS_SCHEDULER job Step 7: How to stop … Continue reading DBMS_SCHEDULER →

oracle clusteringintermediate
by OracleDba
15 views
1

Overview

Step 0: Overview In Oracle 10g the DBMS_JOB package is replaced by the DBMS_SCHEDULER package. The DBMS_JOB package is now depricated and in Oracle 10g it's only provided for backward compatibility. From Oracle 10g the DBMS_JOB package should not be used any more, because is could not exist in a future version of Oracle. With DBMS_SCHEDULER Oracle procedures and functions can be executed. Also binary and shell-scripts can be scheduled. Step 1: Create DBMS_SCHEDULER JOB

Code/Command (click line numbers to comment):

1
2
3
4
In Oracle 10g the DBMS_JOB package is replaced by the DBMS_SCHEDULER package.
The DBMS_JOB package is now depricated and in Oracle 10g it's only provided for backward compatibility.
From Oracle 10g the DBMS_JOB package should not be used any more, because is could not exist in a future version of Oracle.
With DBMS_SCHEDULER Oracle procedures and functions can be executed. Also binary and shell-scripts can be scheduled.
2

Section 2

Step 2: Find the current status of job Step 3: Find the SID

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
-- Create Procedure --
SQL> show user
USER is "EP"
SQL> CREATE PROCEDURE SP1
AS
C INTEGER;
BEGIN
select count(*) INTO C
from test t1, test t2 where t1.c=t2.c and t1.c=1;
DBMS_OUTPUT.PUT_LINE(C);
END;
/
  2    3    4    5    6    7    8    9
Procedure created.

SQL>
-- How to create DBMS_SCHEDULER JOB ---
SQL> BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
  2    3         job_name => '"EP"."SP_RAJ"',
<-- Job name
4              job_type => 'STORED_PROCEDURE',
<-- procedure
5              job_action => 'EP.SP1',
<-- exec Procedure
6              number_of_arguments => 0,
  7              start_date => sysdate,
<--- Run immediately after
execution
  8              repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
  9              end_date => NULL,
 10              enabled => FALSE,
 11              auto_drop => FALSE,
 12              comments => 'Testing');
 13
 14
DBMS_SCHEDULER.enable
(
 15               name => '"EP"."SP_RAJ"');
 16  END;
 17  /

PL/SQL procedure successfully completed.

SQL>

-- Please get the job_name from application team
SQL>
select OWNER, JOB_NAME, STATE from dba_scheduler_jobs where JOB_NAME='SP_RAJ';
OWNER      JOB_NAME                       STATE
---------- ------------------------------ ---------------
EP         SP_RAJ                         RUNNING

SQL>
3

Section 3

39 Step 4: How to find currently job is enabled(or)disabled Step 5: How to Disbale DBMS_SCHEDULER job

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
SQL> col OWNER for a10
SQL>
SELECT OWNER,SESSION_ID,JOB_NAME,RUNNING_INSTANCE FROM dba_scheduler_running_jobs where JOB_NAME='SP_RAJ';
OWNER      SESSION_ID JOB_NAME  RUNNING_INSTANCE
---------- ---------- --------- ----------------
EP
39
SP_RAJ                   1

SQL>

SQL> col USERNAME for a10
SQL> col EVENT for a30
SQL>
select sid, serial#, username, status, sql_id,to_char(LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS') AS LOGON, event from v$session where SID='39';
SID    SERIAL# USERNAME   STATUS   SQL_ID        LOGON                EVENT
---------- ---------- ---------- -------- ------------- -------------------- ------------------------------
39
99 EP         ACTIVE   2p6db5q97bjvt 28-OCT-2016 23:47:02 asynch descriptor resize

SQL>

SQL>
select program, module from v$session where sid='39';
PROGRAM                                          MODULE
------------------------------------------------ -----------------
[email protected]
(J000)
DBMS_SCHEDULER
SQL>

SQL>
select vs.program, spid from v$session vs, v$process vp where vs.sid =
39
and vs.paddr = vp.addr;
PROGRAM                                          SPID
------------------------------------------------ ------------------------
[email protected] (J000)
2278
SQL>

SQL>
!ps -ef | grep 2278
oracle    2278     1 48 Oct28 ?        00:09:31
ora_j000_w148p <---
oracle    2559  4445  0 00:06 pts/2    00:00:00 /bin/bash -c ps -ef | grep 2278
oracle    2561  2559  0 00:06 pts/2    00:00:00 grep 2278

SQL>

SQL>
select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'SP_RAJ';
JOB_NAME                       ENABL
------------------------------ -----
SP_RAJ
TRUE
SQL>
4

Section 4

Step 5: How to Disbale DBMS_SCHEDULER job Step 6: How to Enable DBMS_SCHEDULER job Step 7: How to stop DBMS_SCHEDULER job

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
BEGIN
      DBMS_SCHEDULER.disable(name=>'"EP"."SP_RAJ"', force => TRUE);
END;
/
-- OR --

exec sys.DBMS_SCHEDULER.disable(name=>'"EP"."SP_RAJ"', force => TRUE);


SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'SP_RAJ';

JOB_NAME                       ENABL
------------------------------ -----
SP_RAJ
FALSE
SQL>

BEGIN
      DBMS_SCHEDULER.enable(name=>'"EP"."SP_RAJ"');
END;
/
-- OR --

exec sys.DBMS_SCHEDULER.enable(name=>'"EP"."SP_RAJ"');

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'SP_RAJ';

JOB_NAME                       ENABL
------------------------------ -----
SP_RAJ
TRUE
SQL>
5

Section 5

Step 7: How to stop DBMS_SCHEDULER job Step 8: How to start DBMS_SCHEDULER job Step 9: How to change repeat_interval

Code/Command (click line numbers to comment):

1
2
3
exec sys.dbms_scheduler.STOP_JOB(job_name=>'EP.SP_RAJ', force=>true);

exec sys.dbms_scheduler.RUN_JOB(job_name=>'EP.SP_RAJ');
6

Section 6

Step 9: How to change repeat_interval -- Current status is running, once job completed. Then do the below or if is it ok stop job then stop job. 15 30

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
SQL> set lines 180
SQL> col REPEAT_INTERVAL for a30
SQL>
select OWNER, JOB_NAME, STATE, REPEAT_INTERVAL from dba_scheduler_jobs where JOB_NAME='SP_RAJ';
OWNER      JOB_NAME        STATE           REPEAT_INTERVAL
---------- --------------- --------------- ------------------------------
EP         SP_RAJ
RUNNING
FREQ=MINUTELY;INTERVAL=15

SQL>
-- Current status is running, once job completed. Then do the below or if is it ok stop job then stop job.
SQL> select OWNER, JOB_NAME, STATE, REPEAT_INTERVAL from dba_scheduler_jobs where JOB_NAME='SP_RAJ';

OWNER      JOB_NAME        STATE           REPEAT_INTERVAL
---------- --------------- --------------- ------------------------------
EP         SP_RAJ
SCHEDULED
FREQ=MINUTELY;INTERVAL=
15
SQL> 

SQL>
exec sys.DBMS_SCHEDULER.disable(name=>'"EP"."SP_RAJ"', force => TRUE);
PL/SQL procedure successfully completed.

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'SP_RAJ';

JOB_NAME        ENABL
--------------- -----
SP_RAJ          FALSE

SQL>

SQL>
BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE (
        name => '"EP"."SP_RAJ"',
        attribute => 'repeat_interval',
        value => 'FREQ=MINUTELY;INTERVAL=
30
');
END;
/
2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL>
select OWNER, JOB_NAME, STATE, REPEAT_INTERVAL from dba_scheduler_jobs where JOB_NAME='SP_RAJ';
OWNER      JOB_NAME        STATE           REPEAT_INTERVAL
---------- --------------- --------------- ------------------------------
EP         SP_RAJ          DISABLED        FREQ=MINUTELY;INTERVAL=
30
SQL>

SQL>
exec sys.DBMS_SCHEDULER.enable(name=>'"EP"."SP_RAJ"');
PL/SQL procedure successfully completed.

SQL>
select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'SP_RAJ';
JOB_NAME        ENABL
--------------- -----
SP_RAJ          TRUE

SQL>
select OWNER, JOB_NAME, STATE, REPEAT_INTERVAL from dba_scheduler_jobs where JOB_NAME='SP_RAJ';
OWNER      JOB_NAME        STATE           REPEAT_INTERVAL
---------- --------------- --------------- ------------------------------
EP         SP_RAJ
SCHEDULED
FREQ=MINUTELY;INTERVAL=
30
SQL>
7

Section 7

30 Step 10: DBMS_SCHEDULER tables for Job monitoring Caution : Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using. Caution

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
- To review the execution times of a job
SELECT JOB_NAME, STATUS, ERROR# FROM DBA_SCHEDULER_JOB_RUN_DETAILS;
- To review the errors of a Job:
select status, error#, substr(additional_info,1,500)
from dba_scheduler_job_run_details where job_name='SP_RAJ';
For jobs run by current user:
To view all running jobs --> USER_SCHEDULER_RUNNING_JOBS
To view job statuses --> USER_SCHEDULER_JOB_RUN_DETAILS
To view log details --> USER_SCHEDULER_JOB_LOG
For all jobs :
To view all running jobs --> DBA_SCHEDULER_RUNNING_JOBS
To view job statuses --> DBA_SCHEDULER_JOB_RUN_DETAILS
To view log details --> DBA_SCHEDULER_JOB_LOG

Monitoring job-scheduling
-- 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;
-- For more help "desc dbms_scheduler;"
desc dbms_scheduler;
8

Section 8

: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using. Reference: http://www.oradev.com/dbms_scheduler.html http://www.vsevolod.com/magz/ORplSQLl/0596005873/oracleplsqldba-chp-8-sect-7.html http://www.pafumi.net/Scheduler_DBMS_SCHEDULER.html

Comments (0)

Please to add comments

No comments yet. Be the first to comment!