Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Configuring Oracle Job Scheduling
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by four of the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

This section will present information on how to schedule Oracle jobs using the dbms_scheduler package.  The dbms_scheduler package was introduced in Oracle 10g, so the example code associated with these sections will not work on previous versions.  Where appropriate, Enterprise Manager (EM) screen shots will be used to illustrate the GUI/Web alternative to using the PL/SQL API. Job Scheduling in regards to its benefit for the DBA is covered in Chapter 6 with emphasis on the dbms_job and dbms_scheduler packages.

 

The example code shows how objects can be created, manipulated and dropped.  In a number of cases, code examples rely on previously created objects, which may have already been dropped, so they will have to be recreated before it will be possible to move on.

 

The following section will detail how to set up a test environment to enable the running of any example code.

Setting up a Test Environment

In order to use the examples in this chapter, it is necessary to create a user ID to work with and define a task to schedule.  The following code creates a user called job_user and grants it the necessary privileges.  Some privileges used are specific for Oracle 11g and should be ignored if a prior version is used.

 

conn sys/password as sysdba

 

-- Create user.

create user job_user identified by job_user default tablespace users quota unlimited on users;

grant connect to job_user;

grant select_catalog_role to job_user;

 

-- Privileges for task, not for dbms_job.

grant create procedure to job_user;

grant execute on dbms_lock to job_user;

grant execute on dbms_system to job_user;

 

-- Oracle 10g only.

grant create job to job_user;

grant manage scheduler to job_user;

 

conn job_user/job_user

 

The MANAGE SCHEDULER privilege should only be granted when a user must administer job classes, windows and window groups.  These objects provide a link between the scheduler and the resource manager, a feature which had traditionally required the DBA role.  The roles and privileges associated with the 11g scheduler will be presented in the following text.

 

In the previous script, a system privilege and an object privilege were granted to job_user to allow the creation of a task to schedule.  The following script creates a database procedure that will be used throughout this book when creating jobs.  This procedure uses the dbms_system package to write a user defined string to the alert log at the start and end of the job. 

 

The body of the procedure loops 100 times with a sleep of one second in each loop.  It also uses the dbms_application_info  package to write information to the v$session and v$session_longops views.  The use of the dbms_system and dbms_application_info packages will be covered in more detail later in this text. 

 

 my_job_proc.sql

 

-- *************************************************

-- Parameters:

--    1) Text to identify this test job.

-- *****************************************************************

 

CREATE OR REPLACE PROCEDURE my_job_proc (p_text  IN  VARCHAR2) AS

  l_rindex  PLS_INTEGER;

  l_slno    PLS_INTEGER;

  l_total   NUMBER;

  l_obj     PLS_INTEGER;

BEGIN

  SYS.DBMS_SYSTEM.ksdwrt(2, 'MY_JOB_PROC Start: ' || p_text);

 

  DBMS_APPLICATION_INFO.set_module(

    module_name => 'my_job_proc',

    action_name => p_text || ': Start.');

 

  l_rindex    := Dbms_Application_Info.Set_Session_Longops_Nohint;

  l_total := 100;

                                  

  FOR i IN 1 .. l_total LOOP

    DBMS_APPLICATION_INFO.set_action(

      action_name => p_text || ': Sleep ' || i || ' of ' || l_total || '.');

 

    DBMS_APPLICATION_INFO.set_session_longops(

      rindex      => l_rindex,

      slno        => l_slno,

      op_name     => 'MY_JOB_PROC',

      target      => l_obj,

      context     => 0,

      sofar       => i,

      totalwork   => l_total,

      target_desc => 'MY_JOB_PROC',

      units       => 'loops');

     

    DBMS_LOCK.sleep(1);

  END LOOP;

 

  DBMS_APPLICATION_INFO.set_action(

    action_name => p_text || ': End.');

 

  SYS.DBMS_SYSTEM.ksdwrt(2, 'MY_JOB_PROC End: ' || p_text);

END;

/

SHOW ERRORS

 

The procedure can be tested by calling it from SQL*Plus as follows:

 

SQL> exec my_job_proc('Test It!');

 

Once the procedure has completed, the alert log should contain an entry that looks similar to the following:

 

Sat Jun 19 12:29:16 2004

MY_JOB_PROC Start: Test It!

Sat Jun 19 12:30:59 2004

MY_JOB_PROC End: Test It!

 

Obviously, these entries may be separated by other messages depending on what else has happened on the instance during the time it took for the job to run.

 

Now that the user named job_user has been created and granted privileges, it is time to schedule jobs.  The first step is the examination of the dbms_job package.

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.