Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Oracle DBMS JOBS

1)linuxLevel – crontab here is the Oracle server level

2)from 9i to 10g dbms jobs

3)Dbms scheduler from 10g

4)The DBMS_JOB package allows a user to schedule a job to run at a specified time.  A job is submitted to a job queue and runs at the specified time.  The user can also input a parameter that specifies how often the job should run.  A job can consist of any PL/SQL code.

5)Source: {ORACLE_HOME}/rdbms/admin/dbmsjob.sq

6)SNP processes run in the background and implement database snapshots and job queues.  If an SNP process fails, Oracle restarts it without affecting the rest of the database

7)The INIT.ORA initialization file contains three parameters that control the behavior of the SNP processes
JOB_QUEUE_PROCESSES:-           
How many processes to start.  If set to zero, no jobs are executed. Default is 0.  Range is 0..1000.
JOB_QUEUE_INTERVAL (obsolete in 9i):-
How long an interval the process will sleep before checking for a new job. Default is 60 sec.  Range is 1..3600 sec
JOB_QUEUE_KEEP_CONNECTIONS:-
Controls whether an SNP process closes any remote database connections. Default is False.  Range is True/False.
The person who submits a job to the job queue is considered the job’s owner.

8)Views
dba_jobs      
all_jobs      
user_jobs
dba_jobs_running      
all_jobs_running      
user_jobs_running

9)job timing(intervals)
Execute daily         --> 'SYSDATE + 1'
Execute once per week --> 'SYSDATE + 7'
Execute hourly        -->'SYSDATE + 1/24'
Execute every 10 min. --> 'SYSDATE + 10/1440'
Execute every 30 sec. --> 'SYSDATE + 30/86400'
Do not re-execute     --> NULL

10)Syntax:
DBMS_JOB.CHANGE (
job       IN  BINARY_INTEGER,
what      IN  VARCHAR2,
next_date IN  DATE,
interval  IN  VARCHAR2,
instance  IN  BINARY_INTEGER DEFAULT NULL,
force     IN  BOOLEAN DEFAULT FALSE);

explanation
job- Number of the job being run.
What – PL/SQL procedure to run.
next_date – Date of the next refresh.
interval – Date function evaluated immediately before the job starts running.
instance – When a job is submitted, specifies which instance can run the job.
This defaults to NULL, which indicates that instance affinity is not changed.
force – If this is FALSE, then the specified instance must be running.
Otherwise, the routine raises an exception.
If this is TRUE, then any positive integer is acceptable as the job instance.

practicals
Change the WHAT
The definition of a job can be altered by calling the DBMS_JOB.WHAT procedure.
Syntax:
DBMS_JOB.CHANGE (
job    IN  BINARY_INTEGER,
what   IN  VARCHAR2);
Code:
execute DBMS_JOB.WHAT(3,Begin SEND_MESSAGE(); End;');

Changing the Next Date
The DBMS_JOB.NEXT_DATE procedure alters the next execution time for a specified job.
 Syntax:
 DBMS_JOB.NEXT_DATE (JOB IN BINARY_INTEGER,
NEXT_DATE  IN DATE);
 Code:
 execute DBMS_JOB.NEXT_DATE(4,SYSDATE+3);

Changing the Interval
The DBMS_JOB.INTERVAL procedure alters the interval between executions for a specified job. 
 Syntax:
DBMS_JOB.INTERVAL (job         IN  BINARY_INTEGER,
interval  IN  VARCHAR2); 

Assign job to RAC instance
Assign a specific RAC instance to execute a job:
 Syntax:
dbms_job.instance(
job      IN BINARY_INTEGER,
instance IN BINARY_INTEGER,
force    IN BOOLEAN DEFAULT FALSE); 
SELECT instance_number FROM gv$instance;
Code:
EXECUTE  DBMS_JOB.INSTANCE(job=>123, instance=>1);
SELECT JOB,SUBSTR(WHAT,1,35),NEXT_DATE, NEXT_SEC,INTERVAL
FROM DBA_JOBS;

ISUBMIT
Submit a job with a user specified job number
 Syntax:
dbms_job.isubmit (
job       IN BINARY_INTEGER,
what      IN VARCHAR2,
next_date IN DATE,
interval  IN VARCHAR2 DEFAULT 'NULL',
no_parse  IN BOOLEAN DEFAULT FALSE);
N.B: no_parse indicates whether to parse job PL/SQL at time of submission (FALSE) or execution (TRUE)
 exec dbms_job.isubmit(4242, 'MYPROC', SYSDATE);

IS_JOBQ
dbms_job.is_jobq RETURN BOOLEAN;
Code:
set serveroutput on
DECLARE
b BOOLEAN;
BEGIN
IF dbms_job.is_jobq THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/

BROKEN Jobs
How a job becomes ‘broken’
Oracle has failed to successfully execute the job after 16 attempts.
                                       or
You have marked the job as broken, using the procedure DBMS_JOB.BROKEN
 N.B:  Once a job has been marked as broken, Oracle will not attempt to execute the job until it is either marked not broken, or forced to be execute by calling the DBMS_JOB.RUN.
Syntax:
dbms_job.broken (
job       IN BINARY_INTEGER,
broken    IN BOOLEAN,
next_date IN DATE DEFAULT SYSDATE);
Run a job now

To force immediate job execution:
 EXEC dbms_job.run(job_no);
Mark a job as not broken

If a job fails to successfully execute it will go into a broken state after 16 attempts. To reset the job use following command
 EXEC dbms_job.broken(jobno, FALSE);

Monitor User jobs
See created jobs:
 Code:
SELECT  job, next_date,
next_sec,
failures,
broken,
SUBSTR(what,1,40) DESCRIPTION
FROM user_jobs;
Jobs that are currently running:
 Code:
select
job                               j,
to_char ( last_date, 'hh24:mi:ss' ) last,
to_char ( this_date, 'hh24:mi:ss' ) this,
to_char ( next_date, 'hh24:mi:ss' ) next,
broken                            b,
failures                          f,
interval,
what
from user_jobs;

Remove a submitted job
Syntax:
dbms_job.remove(job IN BINARY_INTEGER);
EXECUTE DBMS_JOB.REMOVE(jobno);

Comments

Popular posts from this blog

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools