To check whether a particular procedure is scheduled (used) in DBMS_SCHEDULER in Oracle, use the following reliable methods.
✅ 1️⃣ Check Scheduler Jobs Calling That Procedure (MOST COMMON)
SELECT owner,
job_name,
job_type,
job_action
FROM dba_scheduler_jobs
WHERE UPPER(job_action) LIKE '%PROCEDURE_NAME%';
🔹 Replace PROCEDURE_NAME with your actual procedure name.
👉 If rows are returned → Procedure is scheduled
👉 If no rows → Not directly scheduled
✅ 2️⃣ Check Jobs Using PL/SQL Blocks (BEGIN … END)
Some jobs call procedures inside PL/SQL blocks.
SELECT owner,
job_name,
job_action
FROM dba_scheduler_jobs
WHERE job_type = 'PLSQL_BLOCK'
AND UPPER(job_action) LIKE '%PROCEDURE_NAME%';
✅ 3️⃣ Check Scheduler Programs (Indirect Calls)
Sometimes jobs call programs, and programs call procedures.
Step 1: Find Programs Calling the Procedure
SELECT owner,
program_name,
program_action
FROM dba_scheduler_programs
WHERE UPPER(program_action) LIKE '%PROCEDURE_NAME%';
Step 2: Find Jobs Using That Program
SELECT job_name,
program_name
FROM dba_scheduler_jobs
WHERE program_name = 'PROGRAM_NAME';
✅ 4️⃣ Check Job Run History (Past Executions)
SELECT job_name,
status,
actual_start_date,
run_duration
FROM dba_scheduler_job_run_details
WHERE UPPER(additional_info) LIKE '%PROCEDURE_NAME%'
ORDER BY actual_start_date DESC;
✅ 5️⃣ Check Only in Your Schema (No DBA Access)
SELECT job_name,
job_action
FROM user_scheduler_jobs
WHERE UPPER(job_action) LIKE '%PROCEDURE_NAME%';
🔎 6️⃣ Check If Procedure Exists at All (Sanity Check)
SELECT owner,
object_name,
object_type
FROM dba_objects
WHERE object_type = 'PROCEDURE'
AND object_name = 'PROCEDURE_NAME';
📌 Quick Decision Table
| Scenario | Meaning |
|---|---|
Procedure found in job_action | Directly scheduled |
Found in program_action | Indirectly scheduled |
| Found nowhere | Not scheduled |
| Found but job disabled | Exists but not running |
⚠️ Common Pitfall
If the job uses:
BEGIN pkg_name.proc_name; END;
Search for both package and procedure:
LIKE '%PKG_NAME%'
LIKE '%PROC_NAME%'
Tags:
Oracle