Use DBMS_SCHEDULER to replace DBMS_JOB

DBMS_JOB has been around forever, and now it is deprecated. Although DBMS_JOB still exists in 10g and 11g, but only for backward compatibility. No new features are being added to dbms_job and you will likely quickly run into its limitations. Oracle recommends the use of DBMS_SCHEDULER in releases 10g and up. DBMS_SCHEDULER is a much more robust package and fully-featured than DBMS_JOB. To use the DBMS_SCHEDULER package a user must be granted the CREATE JOB privilege.

DBMS_SCHEDULERincludes the following features that DBMS_JOB does not have :

  • logging of job runs (job history)
  • simple but powerful scheduling syntax (similar to but more powerful than cron syntax)
  • running of jobs outside of the database on the operating system
  • resource management between different classes of jobs
  • use of job arguments including passing of objects into stored procedures
  • privilege-based security model for jobs
  • naming of jobs and comments in jobs
  • stored, reusable schedules

Features in releases after 10g Release 1 include :

  • dependencies between job units (10gR2 and up)
  • scheduling based on financial calendars and fiscal quarters (10gR2 and up)
  • event based jobs which run when an event is received (10gR2 and up)
  • running of jobs on remote machines (11gR1 and up)
  • e-mail notifications on job events of interest (10gR2 and up)
  • starting a job based on arrival of a file (10gR2 and up)

Here simple comparison for the codes :

  • Old using DBMS_JOB scheduler.VARIABLE l_job NUMBER;
    BEGIN
    DBMS_JOB.submit (
    job => :l_job,
    what => ‘BEGIN NULL; /* code to execute*/ END;’,
    next_date => SYSDATE,
    interval => ‘SYSDATE + 1 /* 1 Day */’);COMMIT;
    END;
    /
    PRINT l_job
  • New with DBMS_SCHEDULER scheduler.BEGIN
    DBMS_SCHEDULER.create_job (
    job_name => ‘dummy_job’,
    job_type => ‘PLSQL_BLOCK’,
    job_action => ‘BEGIN NULL; /* code to execute */ END;’,
    start_date => SYSTIMESTAMP,
    repeat_interval => ‘SYSTIMESTAMP + 1 /* 1 Day */’);
    END;
    /

After replace DBMS_JOB with DBMS_SCHEDULER for all jobs successful, the job_queue_processes parameter can now be set to zero.

alter system set job_queue_processes=0;

There is also a forum dedicated to questions about dbms_scheduler here : http://forums.oracle.com/forums/forum.jspa?forumID=193

Advertisements

ORA-06553: PLS-801: internal error [56319]

When i have finish update oracle 32-bit to 64-bit, then i try to compile procs and I got this message,

Error accessing package DBMS_APPLICATION_INFO
ERROR:
ORA-06553: PLS-801: internal error [56319]

—————————————————

ORA-00604: error occurred at recursive SQL level 2
ORA-06544: PL/SQL: internal error, arguments: [56319],[], [], [], [], [], [],[]
ORA-06553: PLS-801: internal error [56319]

i have tried steps to fix this problem
– run STANDARD.SQL to reinstall DBMS_APPLICATON_INFO
– run the DBMSUTL.SQL to create DBMS_APPLICATON_INFO
– run CATALOG.SQL,CATPPROC.SQL AND UTLPR.SQL
(sql file usually located in ORACLE_HOME/rdbms/admin)

but while running those script i got the same error.

the other steps i found on http://seilerwerks.wordpress.com/2007/03/06/fixing-a-32-to-64-bit-migration-with-utlirpsql/,

SQL> shutdown immediate;
SQL> startup upgrade;
SQL> @utlirp;

SQL> shutdown immediate;
SQL> startup;
SQL> @utlrp;

but it’s not VOILA! and errors still there laughing at me, >.<

I have search through internet and ended with answer from forum
that utlirp.sql to convert from 32 bit to 64 bit should be run when database is in migration mode.
This error is due to the 32 bit datafiles being running on a 64 bit version of oracle.

If anyone have other solution, please leave message for me. >.<

Oracle DBMS_JOB

· Introduction

The DBMS_JOB package is known as the job queue.

The Oracle job queue provides programs for submitting and executing jobs, changing job execution parameters,

and removing or temporarily suspending job execution at predefined times and/or repeated job execution at regular intervals.

Users on Oracle 10g and above should use the newer DBMS_SCHEDULER package.

DBMS SCHEDULER is a more sophisticated job scheduler introduced in Oracle 10g.

The older job scheduler, DBMS_JOB, is still available.

· Data dictionary views

Data dictionary views that display information about the Oracle job queue :

DBA_JOBS DBA view describes all the jobs in the database.

ALL_JOBS ALL view describes all jobs that are accessible to the current user.

USER_JOBS USER view describes all jobs owned by the current user

DBA_JOBS_RUNNING Lists all jobs in the database that are currently running. This view can be joined with V$LOCK to identify jobs that have locks.

Displaying Information About a Job

The following query creates a listing of the job number, next execution time, failure count, and broken status for each job you have submitted:

SELECT job, next_date, next_sec, failures, broken

FROM DBA_JOBS;

Displaying Information About Running Jobs

You can also display information about only the jobs currently running.

The following query lists the session identifier, job number, user who submitted the job, and the start times for all currently running jobs:

SELECT SID, r.job, log_user, r.this_date, r.this_sec

FROM DBA_JOBS_RUNNING r, DBA_JOBS j

WHERE r.job = j.job;

Job Queue Locks

Oracle uses job queue locks to ensure that a job is executed in only one session at a time. When a job is being run, its session acquires a job queue (JQ) lock for that job.

You can use the locking views in the data dictionary to examine information about locks currently held by sessions.

The following query lists the session identifier, lock type, and lock identifiers for all sessions holding JQ locks:

SELECT SID, TYPE, id1, id2

FROM v$lock

WHERE TYPE = ‘JQ’;

SID TY ID1 ID2

——— ——— ———

12 JQ 0 14144

1 row selected.

In the query above, the identifier for the session holding the lock is 12. The ID1 column is always 0 for JQ locks. The ID2 column is the job number of the job the session is running.

This view can be joined with the DBA_JOBS_RUNNING view to obtain more information about the job.

· Initialization Parameter

An initialization parameter in the INIT.ORA file for the database. The parameter is,

1. JOB_QUEUE_PROCESSES Controls how many jobs can run, maximum number of job queue slave processes.

If this parameter is set to 0, no coordinator job queue process is started at database startup, and consequently no job queue jobs are executed.

The JOB_QUEUE_PROCESSES initialization parameter also specifies the maximum number of Jnnn processes that can concurrently run on an instance.

The maximum number of processes that can be specified is 1000.

SQL> show parameter job_queue_processes

SQL> alter system set job_queue_processes = 2 SCOPE=BOTH;

2. _JOB_QUEUE_INTERVAL This is an internal Oracle parameter. Do NOT use it unless instructed to do so by Oracle Support. Playing with this parameter may be harmful.

3. JOB_QUEUE_KEEP_CONNECTIONS This parameter became obsolete above Oracle 8.1.7.

· Procedures of the DBMS_JOB package

DBMS_JOB.SUBMIT Submits a job to the job queue

DBMS_JOB.REMOVE Removes a specified job from the job queue

DBMS_JOB.CHANGE Alters a specified job that has already been submitted to the job queue (job description, the time at which the job will be run, or the interval between executions of the job)

DBMS_JOB.WHAT Alters the job description for a specified job

DBMS_JOB.NEXT_DATE Alters the next execution time for a specified job

DBMS_JOB.INTERVAL Alters the interval between executions for a specified job

DBMS_JOB.BROKEN Sets or resets the job broken flag. If a job is marked as broken, Oracle does not attempt to execute it

DBMS_JOB.RUN Forces a specified job to run

· Submitting a Job to the Job Queue

It chooses job from the sequence sys.jobseq.

Syntax

DBMS_JOB.SUBMIT (

job OUT BINARY_INTEGER,

what IN VARCHAR2,

next_date IN DATE DEFAULT sysdate,

interval IN VARCHAR2 DEFAULT ‘null’,

no_parse IN BOOLEAN DEFAULT FALSE,

instance IN BINARY_INTEGER DEFAULT any_instance,

force IN BOOLEAN DEFAULT FALSE);

Parameter Description

job Number of the job being run. The job number is automatically generated from the JOBSEQ sequence owned by user SYS.

Once a job is assigned a job number, that number does not change.

what Job defenition or PL/SQL procedure to run. The procedure call can have any number of parameters.

next_date Next date when the job will be run. The default value is SYSDATE.

interval Date function that calculates the next time to run the job. The default is NULL. This must evaluate to a either a future point in time or NULL.

no_parse A flag. The default is FALSE. If this is set to FALSE, then Oracle parses the procedure associated with the job.

If this is set to TRUE, then Oracle parses the procedure associated with the job the first time that the job is run.

For example, if you want to submit a job before you have created the tables associated with the job, then set this to TRUE.

instance When a job is submitted, specifies which instance can run the job.

force If this is TRUE, then any positive integer is acceptable as the job instance.

If this is FALSE (the default), then the specified instance must be running; otherwise the routine raises an exception.

Example

DECLARE

X NUMBER;

BEGIN

SYS.DBMS_JOB.SUBMIT

( job => X

,what => ‘MIS.CV_BILLING_POS_TEST;’

,next_date => TO_DATE(’01/01/4000 00:00:00′,’dd/mm/yyyy hh24:mi:ss’)

,INTERVAL => ‘SYSDATE+1440/1440’

,no_parse => TRUE

);

SYS.DBMS_JOB.BROKEN

(job => X,

broken => TRUE);

SYS.DBMS_OUTPUT.PUT_LINE(‘Job Number is: ‘ || TO_CHAR(x));

END;

/

COMMIT;

The commit here is important to assure the newly created job will run

Job Definition

In the job definition, use two single quotation marks around strings. Always include a semicolon at the end of the job definition.

The following are examples of valid job definitions:

* ,what => ‘PTR.TARGET_REAL(”002”);’

* ,what => ‘get_target(”HANS”, 2007);’

Running a job from a job is not supported. You will receive an error message

“ORA-32317 cannot run a job from another job” error message in the alert file:

DECLARE

jobno number;

BEGIN

DBMS_JOB.SUBMIT(jobno, ‘DBMS_JOB.RUN(1234);’);

DBMS_JOB.RUN(jobno);

END;

Job Intervals

1. Every Second.

INTERVAL => ‘SYSDATE +1/86400’;

2. Every Minute.

INTERVAL => ‘SYSDATE +1/1440’;

3. Every Ten Minute.

INTERVAL => ‘SYSDATE +10/1440’;

4. Every Half Hour.

INTERVAL => ‘SYSDATE +30/1440’;

INTERVAL => ‘SYSDATE + 1/48’;

5. Every Hour.

INTERVAL => ‘SYSDATE +60/1440’;

6. Every Day.

INTERVAL => ‘SYSDATE +1’;

7. Every Day with Fixed Time. ex: every night 6:00pm.

INTERVAL => ‘trunc(SYSDATE +1) + 18/24’;

8. Every Monday at 3.00pm.

INTERVAL => ‘NEXT_DAY(TRUNC(SYSDATE), ”MONDAY”) + 15/24’;

9. Every Midnight 12:00.

INTERVAL => ‘trunc(SYSDATE +1)’;

10. Every Week

INTERVAL => ‘trunc(SYSDATE +7)’;

11. Every Month

INTERVAL => ‘add_months(SYSDATE, 1)’;

12. First Thursday of each quarter

INTERVAL => ‘NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ”Q”), 3), ”THURSDAY”)’;

If NULL is specified instead of an interval the job will be run once at the specified first execution time then be removed from the job queue.

· Removing a Job from the Job Queue

To remove a job from the job queue, use the REMOVE procedure in the DBMS_JOB package.

The following statements remove job number 123 from the job queue:

BEGIN

DBMS_JOB.REMOVE(123);

END;

/

Restrictions:

* You can remove currently executing jobs from the job queue. However, the job will not be interrupted, and the current execution will be completed.

* You can remove only jobs you own. If you try to remove a job that you do not own, you receive a message that states the job is not in the job queue.

Job Execution Errors

The following can cause queued jobs fail :

* a network or instance failure

* an exception when executing the job

If a job returns an error while Oracle is attempting to execute it, Oracle tries to execute it again.

The first attempt is made after one minute.

The second attempt after two minutes

The third after four minutes, and so on, with the interval doubling between each attempt.

If the job fails 16 times, Oracle automatically marks the job as broken and no longer tries to execute it.

However, between attempts, you have the opportunity to correct the problem that is preventing the job from running.

This will not disturb the retry cycle, and Oracle will eventually attempt to run the job again.

· Running a job manually

To run a job manually, you can call the RUN procedure, it is especially useful if you need to run a job which has been marked as broken.

BEGIN

DBMS_JOB.RUN(JOB => 21);

END;

/

The value for NEXT_DATE will be updated based on the current date and time and your formula.

This becomes important if you have a job running at, say 08:00 pm with an interval of SYSDATE + 1, but then you run it manually at 1:23pm,

the next_date will be recalculated at that time and it will now be run at 1:23pm each day.