← Back to Table of Contents

Oracle DBMS_SCHEDULER

This comprehensive guide covers Oracle's DBMS_SCHEDULER management in SQLife.

Table of Contents

Overview

Oracle's DBMS_SCHEDULER is a powerful enterprise scheduler that replaces the legacy DBMS_JOB package. SQLife provides a comprehensive GUI for managing all aspects of the scheduler.

Key Concepts

Job:

  • The main schedulable unit
  • Combines what to run (program/action) with when to run (schedule)
  • Can be enabled/disabled

Schedule:

  • Defines when to run jobs
  • Can be reused across multiple jobs
  • Various repeat intervals supported

Program:

  • Defines what to run
  • PL/SQL block, stored procedure, or executable
  • Can be reused across multiple jobs

Window:

  • Time period for resource management
  • Allocates resources for certain times
  • Can have different priority levels

Window Group:

  • Collection of windows
  • Simplifies window management
  • Used with jobs

Why Use DBMS_SCHEDULER

Advantages over DBMS_JOB:

  • More flexible scheduling
  • Job chains and dependencies
  • Resource management
  • Better monitoring and logging
  • Event-based scheduling

Use Cases:

  • Nightly batch processes
  • Regular data updates
  • Report generation
  • Database maintenance
  • Data warehouse ETL

Opening Oracle Scheduler

Prerequisites:

  • Oracle database connection
  • Oracle 10g or later
  • Sufficient privileges (CREATE JOB or DBA role)

How to Open:

  1. Connect to Oracle database
  2. Go to Database → Oracle Scheduler
  3. Oracle Scheduler window opens

Interface Appears:

  • Left panel: Tree view of objects
  • Right panel: Details and forms
  • Bottom panel: Job logs

Scheduler Interface

Tree View (Left Panel): Hierarchical view of scheduler objects:

  • 📋 Jobs
  • 📅 Schedules
  • ⚙️ Programs
  • 🪟 Windows
  • 📁 Window Groups

Details Panel (Right Panel): Shows details for selected object:

  • Properties
  • Settings
  • Status information

Action Buttons:

  • Create: Create new object
  • Delete: Remove selected object
  • Refresh: Reload list
  • Enable/Disable: Toggle object state
  • Run Now: Execute job immediately

Job Management

Viewing Jobs

Jobs Tree:

  • Click Jobs folder to expand
  • Lists all jobs in your schema
  • Shows job name and status

Job Status Icons:

  • ✓ Enabled: Job active
  • ⊘ Disabled: Job inactive
  • ⏸ Paused: Temporarily suspended
  • ⚠ Broken: Job has errors

Job Information Displayed:

  • Job Name
  • Enabled/Disabled status
  • Schedule name
  • Program name
  • Next run date
  • Last run date
  • Run count

Creating Jobs

How to Create Job:

  1. Click Jobs folder
  2. Click Create Job button
  3. Job creation dialog opens

Job Creation Form:

Basic Information:

  • Job Name: Unique name (e.g., NIGHTLY_BACKUP_JOB)

    • Must follow Oracle naming rules
    • Up to 30 characters
    • Letters, numbers, underscores

  • Job Type: Choose one:

    • PL/SQL Block: Inline PL/SQL code
    • Stored Procedure: Call existing procedure
    • Executable: Run OS command/script
    • Use Program: Reference existing program

Job Action (if not using program):

For PL/SQL Block:

BEGIN
    -- Your PL/SQL code here
    DBMS_OUTPUT.PUT_LINE('Job executed');
    
    -- Example: Cleanup old records
    DELETE FROM log_table WHERE log_date < SYSDATE - 30;
    COMMIT;
END;

For Stored Procedure:

-- Procedure name only
my_package.my_procedure

-- Or with parameters
my_procedure(p_param1 => 'value1')

Schedule Options:

Option 1: Use Existing Schedule

  • Select from schedule dropdown
  • Reuses predefined schedule

Option 2: Define Inline Schedule

  • Start Date: When job should first run
  • Repeat Interval: How often to repeat
    • Examples: FREQ=DAILY; BYHOUR=2; BYMINUTE=0
    • FREQ=HOURLY; INTERVAL=4 (every 4 hours)
  • End Date: When to stop (optional)

Option 3: One-Time Job

  • Set start date only
  • No repeat interval
  • Runs once

Additional Options:

  • Enabled: Start enabled or disabled
  • Auto Drop: Delete after completion (one-time jobs)
  • Comments: Job description

Example Job:

Job Name: CLEANUP_OLD_LOGS
Job Type: PL/SQL Block
Job Action: BEGIN DELETE FROM logs WHERE created < SYSDATE-90; COMMIT; END;
Schedule: Every day at 2 AM
Start Date: 2025-12-21 02:00:00
Repeat Interval: FREQ=DAILY; BYHOUR=2; BYMINUTE=0
Enabled: Yes

After Creation:

  • Job appears in jobs list
  • Status shown (Enabled/Disabled)
  • Next run date calculated

Job Details

Viewing Job Details:

  1. Click job in tree
  2. Details panel shows:

General Tab:

  • Job name
  • Owner
  • Job class
  • Enabled/Disabled
  • Auto drop setting
  • Comments

Schedule Tab:

  • Schedule name (if using schedule)
  • Start date
  • Repeat interval
  • End date
  • Next run date
  • Last run date

Action Tab:

  • Job type
  • Job action (code or procedure name)
  • Program name (if using program)
  • Number of arguments

Statistics Tab:

  • Run count
  • Failure count
  • Last run duration
  • Average run duration

Refresh Details:

  • Click Refresh button
  • Updates with latest information

Running Jobs Manually

Run Job Immediately:

  1. Select job in tree
  2. Click Run Now button
  3. Or right-click job → Run Now
  4. Confirmation prompt appears
  5. Job executes immediately

What Happens:

  • Job runs regardless of schedule
  • Does not affect next scheduled run
  • Status updates in real-time
  • Check job log for results

Use Cases:

  • Testing new job
  • Emergency data fix
  • Verify job works before scheduling

Enabling/Disabling Jobs

Disable Job:

  1. Select enabled job
  2. Click Disable button
  3. Or right-click → Disable
  4. Job status changes to disabled
  5. Will not run on schedule

Enable Job:

  1. Select disabled job
  2. Click Enable button
  3. Or right-click → Enable
  4. Job status changes to enabled
  5. Will run per schedule

Use Cases:

  • Disable: During maintenance, testing, or temporary suspension
  • Enable: Resume normal operation

Deleting Jobs

How to Delete:

  1. Select job in tree
  2. Click Delete button
  3. Or right-click → Delete Job
  4. Confirmation prompt:

    "Are you sure you want to delete job JOB_NAME?"

  5. Click Yes to confirm

Options:

  • Force: Drop even if running
  • Commit: Commit immediately (default)

What Gets Deleted:

  • Job definition
  • Job logs (if configured)
  • Job does NOT run again

Important:

  • Cannot be undone
  • Backup job definition if needed
  • Consider disabling instead of deleting

Schedule Management

Viewing Schedules

Schedules Tree:

  • Click Schedules folder
  • Lists all schedules
  • Shows schedule name and frequency

Schedule Information:

  • Schedule name
  • Start date
  • Repeat interval
  • End date
  • Number of jobs using this schedule

Creating Schedules

How to Create Schedule:

  1. Click Schedules folder
  2. Click Create Schedule button
  3. Schedule creation dialog opens

Schedule Form:

Basic Information:

  • Schedule Name: Unique name (e.g., DAILY_2AM)
  • Comments: Description (optional)

Timing:

  • Start Date: When schedule becomes active
  • End Date: When schedule expires (optional)
  • Repeat Interval: How often to repeat

Schedule Types

Daily:

FREQ=DAILY
FREQ=DAILY; BYHOUR=2; BYMINUTE=0       -- Every day at 2:00 AM
FREQ=DAILY; BYHOUR=2,14; BYMINUTE=0    -- Every day at 2 AM and 2 PM

Weekly:

FREQ=WEEKLY; BYDAY=MON,WED,FRI         -- Mon, Wed, Fri
FREQ=WEEKLY; BYDAY=MON; BYHOUR=9       -- Monday at 9 AM

Monthly:

FREQ=MONTHLY; BYMONTHDAY=1             -- First day of month
FREQ=MONTHLY; BYMONTHDAY=1,15          -- 1st and 15th
FREQ=MONTHLY; BYDAY=MON; BYSETPOS=1    -- First Monday
FREQ=MONTHLY; BYDAY=FRI; BYSETPOS=-1   -- Last Friday

Hourly:

FREQ=HOURLY                            -- Every hour
FREQ=HOURLY; INTERVAL=4                -- Every 4 hours
FREQ=HOURLY; BYHOUR=9,10,11,14,15,16   -- Business hours

Minutely:

FREQ=MINUTELY; INTERVAL=15             -- Every 15 minutes
FREQ=MINUTELY; INTERVAL=30             -- Every 30 minutes

Yearly:

FREQ=YEARLY; BYMONTH=JAN; BYMONTHDAY=1 -- Every January 1st
FREQ=YEARLY; BYMONTH=DEC; BYMONTHDAY=31-- Every December 31st

Complex Examples:

-- Weekdays at 8 AM
FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=8; BYMINUTE=0

-- First Monday of each quarter
FREQ=MONTHLY; BYMONTH=JAN,APR,JUL,OCT; BYDAY=MON; BYSETPOS=1

-- Every 10 minutes during business hours on weekdays
FREQ=MINUTELY; INTERVAL=10; BYHOUR=9,10,11,12,13,14,15,16,17; 
BYDAY=MON,TUE,WED,THU,FRI

Schedule Details

Viewing Schedule Details:

  1. Click schedule in tree
  2. Details panel shows:

Information:

  • Schedule name
  • Owner
  • Start date
  • End date (if set)
  • Repeat interval (decoded)
  • Next run dates (calculated preview)

Jobs Using This Schedule:

  • List of jobs
  • Click to navigate to job

Deleting Schedules

How to Delete:

  1. Select schedule
  2. Click Delete button
  3. Confirmation prompt appears
  4. Click Yes

Important:

  • Cannot delete if jobs are using it
  • Disable or reassign jobs first
  • Or delete jobs using this schedule

Program Management

Viewing Programs

Programs Tree:

  • Click Programs folder
  • Lists all programs
  • Shows program name and type

Program Types:

  • PL/SQL Block
  • Stored Procedure
  • Executable (OS command)

Creating Programs

How to Create Program:

  1. Click Programs folder
  2. Click Create Program button
  3. Program creation dialog opens

Program Form:

Basic Information:

  • Program Name: Unique name (e.g., BACKUP_PROGRAM)
  • Program Type: Select type
  • Enabled: Create enabled or disabled
  • Comments: Description

Program Action:

For PL/SQL Block:

BEGIN
    -- Your reusable PL/SQL code
    my_package.backup_procedure;
END;

For Stored Procedure:

my_schema.my_procedure_name

For Executable:

/usr/local/bin/backup_script.sh

Arguments (if needed):

  • Define arguments for program
  • Argument name, type, default value
  • Used when calling program

Program Types

PL/SQL Block Program:

  • Contains inline PL/SQL code
  • Best for: Simple logic, testing
  • Example: Data cleanup, simple calculations

Stored Procedure Program:

  • References existing procedure/function
  • Best for: Complex logic, reuse
  • Example: Package procedures, complex operations

Executable Program:

  • Runs OS command or script
  • Best for: System tasks, external programs
  • Example: Shell scripts, batch files
  • Requires: Job scheduler agent

Program Details

Viewing Program Details:

  1. Click program in tree
  2. Details show:

Information:

  • Program name
  • Program type
  • Program action
  • Enabled/Disabled
  • Number of arguments
  • Jobs using this program

Deleting Programs

How to Delete:

  1. Select program
  2. Click Delete button
  3. Confirmation prompt
  4. Click Yes

Constraint:

  • Cannot delete if jobs use it
  • Modify jobs first

Window Management

Viewing Windows

Windows Tree:

  • Click Windows folder
  • Lists all windows
  • Shows window name and status

Window Concepts:

  • Time periods for resource allocation
  • Can override resource plans
  • Used for maintenance windows

Creating Windows

How to Create Window:

  1. Click Windows folder
  2. Click Create Window button
  3. Window creation dialog opens

Window Form:

Basic Information:

  • Window Name: Unique name (e.g., MAINTENANCE_WINDOW)
  • Resource Plan: Oracle resource plan name
  • Enabled: Create enabled or disabled

Schedule:

  • Start Date: When window opens
  • Duration: How long window stays open
    • Examples: INTERVAL '2' HOUR, INTERVAL '30' MINUTE
  • Repeat Interval: Window recurrence
    • Same format as job schedules
  • End Date: When window series ends

Example Window:

Window Name: WEEKEND_MAINTENANCE
Resource Plan: MAINTENANCE_PLAN
Start Date: Next Saturday 2 AM
Duration: INTERVAL '4' HOUR
Repeat Interval: FREQ=WEEKLY; BYDAY=SAT

Window Details

Viewing Window Details:

  1. Click window in tree
  2. Details show:

Information:

  • Window name
  • Resource plan
  • Enabled/Disabled
  • Schedule (start, duration, repeat)
  • Next open time
  • Last open time
  • Window priority

Deleting Windows

How to Delete:

  1. Select window
  2. Click Delete button
  3. Confirmation prompt
  4. Click Yes

Window Group Management

Viewing Window Groups

Window Groups Tree:

  • Click Window Groups folder
  • Lists all groups
  • Shows group name and member count

Group Purpose:

  • Logical grouping of windows
  • Simplify window management
  • Used with jobs

Creating Window Groups

How to Create:

  1. Click Window Groups folder
  2. Click Create Window Group button
  3. Dialog opens

Form:

  • Group Name: Unique name
  • Enabled: Create enabled or disabled
  • Comments: Description

Managing Group Members

Adding Windows to Group:

  1. Select window group
  2. Click Add Member button
  3. Select window from list
  4. Click OK

Removing Windows:

  1. Select window in group members list
  2. Click Remove Member button
  3. Confirmation prompt
  4. Click Yes

Member List:

  • Shows all windows in group
  • Window name
  • Window status

Deleting Window Groups

How to Delete:

  1. Select window group
  2. Click Delete button
  3. Confirmation prompt
  4. Click Yes

Note:

  • Deleting group doesn't delete windows
  • Only removes grouping

Job Log Querying

Viewing Job Logs

Job Log Tab:

  • Located at bottom of window
  • Shows execution history
  • Filterable and searchable

How to View:

  1. Click Job Log tab
  2. Logs displayed in table
  3. Most recent logs first

Log Columns:

  • Log Date: When job ran
  • Job Name: Name of job
  • Status: Success, Failed, Stopped
  • Error Code: If failed
  • Error Message: Error details
  • Run Duration: How long job ran
  • Additional Info: Other details

Filtering Logs

Filter Options:

  • Job Name: Filter by specific job
  • Status: Success, Failed, All
  • Date Range: From/To dates
  • Owner: Filter by job owner

How to Filter:

  1. Enter filter criteria
  2. Click Search or Filter button
  3. Log table updates with results

Clear Filter:

  • Click Clear button
  • Shows all logs again

Log Details

Viewing Detailed Log:

  1. Double-click log entry
  2. Or select and click View Details
  3. Detail dialog opens

Detail Information:

  • Full Error Message: Complete error text
  • Error Stack: If applicable
  • Additional Info: JSON or XML output (if configured)
  • Credentials Used: Job owner
  • Resource Consumption: CPU, memory (if available)

Exporting Logs:

  • Select log entries
  • Click Export button
  • Save to CSV or Excel
  • Use for analysis or reporting

Chain Management

Oracle Scheduler chains describe multi-step job flows. The current Scheduler view exposes chain and chain-step controls in addition to jobs, schedules, programs, windows, and window groups.

Viewing and Creating Chains

  1. Open Database -> Oracle Scheduler for an Oracle connection.
  2. Select the Chains view.
  3. Review existing chains or choose the add/create action.
  4. Enter the chain name and other required Oracle properties.
  5. Save the chain.

Adding Chain Steps

  1. Select a chain.
  2. Add a chain step.
  3. Select the program that the step should run.
  4. Configure the step rule or condition as required by Oracle.
  5. Save the chain and refresh the view.

Use Oracle's scheduler privileges and database documentation to validate rules and evaluation conditions before enabling a chain.

Tips and Best Practices

Job Design

Keep Jobs Simple:

  • One job, one task
  • Break complex tasks into multiple jobs
  • Use job chains for dependencies

Error Handling:

  • Always include exception handling in PL/SQL
  • Log errors appropriately
  • Email notifications on failure

Example with Error Handling:

BEGIN
    -- Job logic here
    my_procedure;
    
EXCEPTION
    WHEN OTHERS THEN
        -- Log error
        INSERT INTO error_log (error_date, error_msg)
        VALUES (SYSDATE, SQLERRM);
        COMMIT;
        
        -- Re-raise to mark job as failed
        RAISE;
END;

Scheduling Strategy

Choose Appropriate Times:

  • Off-peak hours for heavy jobs
  • Consider time zones
  • Avoid conflicts with backups

Frequency:

  • As frequent as needed, not more
  • Balance freshness vs. load
  • Monitor and adjust

Use Schedules:

  • Reuse schedules across jobs
  • Central schedule management
  • Easier updates

Monitoring

Regular Review:

  • Check job logs weekly
  • Identify failing jobs
  • Investigate slow jobs

Set Up Alerts:

  • Email on failure
  • Slack/Teams notifications
  • Monitoring system integration

Performance Monitoring:

  • Track job duration trends
  • Identify growing runtime
  • Optimize as needed

Security

Principle of Least Privilege:

  • Jobs run as job owner
  • Grant only necessary privileges
  • Use dedicated job user if possible

Sensitive Data:

  • Don't hardcode passwords
  • Use secure credential store
  • Encrypt sensitive data

Auditing:

  • Review job definitions periodically
  • Monitor unauthorized changes
  • Document job purposes

Maintenance

Document Jobs:

  • Comment field usage
  • Separate documentation
  • Contact person for each job

Version Control:

  • Keep job definitions in scripts
  • Use Git or similar
  • Track changes over time

Cleanup:

  • Delete obsolete jobs
  • Archive old logs
  • Remove unused programs/schedules

Troubleshooting

Job Not Running

Possible Causes:

Job Disabled:

  • Check job status
  • Enable if needed

Schedule Issues:

  • Verify schedule settings
  • Check start/end dates
  • Test schedule calculation

Insufficient Privileges:

  • Job owner must have necessary privileges
  • Check target objects permissions
  • Verify role grants

Scheduler Not Running:

  • Check JOB_QUEUE_PROCESSES parameter
  • Should be > 0
  • Restart scheduler if needed

Job Fails

Check Error Message:

  1. View job log
  2. Read error message
  3. Check error code

Common Errors:

ORA-27370: Job slave failed to launch:

  • Scheduler issues
  • Check alert log
  • Contact DBA

ORA-06512: PL/SQL error:

  • Error in job code
  • Test code outside job
  • Add error handling

ORA-01403: No data found:

  • Job expects data that doesn't exist
  • Add NULL handling
  • Check data dependencies

Job Runs Too Long

Diagnose:

  • Check job log for duration
  • Compare to previous runs
  • Identify trend

Possible Causes:

  • Data volume growth
  • Missing indexes
  • Database performance
  • Query inefficiency

Solutions:

  • Optimize SQL
  • Add indexes
  • Partition tables
  • Run during off-peak

Cannot Create/Modify Jobs

Possible Causes:

Insufficient Privileges:

  • Need CREATE JOB privilege
  • Or ALTER privilege for existing jobs
  • Contact DBA

Name Already Exists:

  • Job/schedule/program name taken
  • Choose different name
  • Or drop existing object

Invalid Schedule:

  • Syntax error in repeat interval
  • Check schedule format
  • Use schedule builder

Next Steps

Now that you understand Oracle Scheduler:

  1. Generate database documentation:

  2. Customize preferences:

  3. Manage backups:

You're now an expert at managing Oracle scheduled jobs!

For more help, see:

Happy scheduling! 🐰

← Previous Chapter
Table of Contents
Next Chapter →