# Oracle DBMS_SCHEDULER

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

## Table of Contents
- [Overview](#overview)
- [Opening Oracle Scheduler](#opening-oracle-scheduler)
- [Scheduler Interface](#scheduler-interface)
- [Job Management](#job-management)
  - [Viewing Jobs](#viewing-jobs)
  - [Creating Jobs](#creating-jobs)
  - [Job Details](#job-details)
  - [Running Jobs Manually](#running-jobs-manually)
  - [Enabling/Disabling Jobs](#enablingdisabling-jobs)
  - [Deleting Jobs](#deleting-jobs)
- [Schedule Management](#schedule-management)
  - [Viewing Schedules](#viewing-schedules)
  - [Creating Schedules](#creating-schedules)
  - [Schedule Types](#schedule-types)
  - [Schedule Details](#schedule-details)
  - [Deleting Schedules](#deleting-schedules)
- [Program Management](#program-management)
  - [Viewing Programs](#viewing-programs)
  - [Creating Programs](#creating-programs)
  - [Program Types](#program-types)
  - [Program Details](#program-details)
  - [Deleting Programs](#deleting-programs)
- [Window Management](#window-management)
  - [Viewing Windows](#viewing-windows)
  - [Creating Windows](#creating-windows)
  - [Window Details](#window-details)
  - [Deleting Windows](#deleting-windows)
- [Window Group Management](#window-group-management)
  - [Viewing Window Groups](#viewing-window-groups)
  - [Creating Window Groups](#creating-window-groups)
  - [Managing Group Members](#managing-group-members)
   - [Deleting Window Groups](#deleting-window-groups)
- [Chain Management](#chain-management)
   - [Viewing and Creating Chains](#viewing-and-creating-chains)
   - [Adding Chain Steps](#adding-chain-steps)
- [Job Log Querying](#job-log-querying)
  - [Viewing Job Logs](#viewing-job-logs)
  - [Filtering Logs](#filtering-logs)
  - [Log Details](#log-details)
- [Tips and Best Practices](#tips-and-best-practices)
- [Troubleshooting](#troubleshooting)

---

## 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:**
```sql
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:**
```sql
-- 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:**
```sql
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:**
```sql
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:**
   - [Database Document Viewer Guide](11-database-document-viewer.md)

2. **Customize preferences:**
   - [Preferences Guide](12-change-preferences.md)

3. **Manage backups:**
   - [Backup and Recovery Guide](13-backup-and-recovery.md)

---

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

For more help, see:
- [Oracle DBMS_SCHEDULER Documentation](https://docs.oracle.com/en/database/oracle/)
- [SQLife Support](https://bitifyware.com/sqlife/help)

Happy scheduling! 🐰