Oracle 11g每日定时任务重复执行问题排查求助

Hey there, let's dig into why you're getting 6-7 duplicate emails between 13:00-16:00 instead of the single 17:00 email you expected. We'll cover both Oracle scheduling configuration and stored procedure code angles to pinpoint the issue.

First: Check Oracle Scheduled Job Configuration #

Your scheduled task is supposed to run at 17:00, but the execution time is off—and it might be running multiple times. Here's how to verify:

1. List All Jobs Calling Your Procedure #

Run these queries to check if there are duplicate jobs, or if the existing job has unexpected repeat rules:

-- For Oracle 11g Scheduler Jobs (modern approach)
SELECT job_name, repeat_interval, last_start_date, next_run_date, enabled, failure_count
FROM dba_scheduler_jobs
WHERE job_action LIKE '%SP_PO_INFO%';

-- For legacy DBMS_JOB tasks
SELECT job, what, next_date, interval, failures, broken
FROM dba_jobs
WHERE what LIKE '%SP_PO_INFO%';

• Look for multiple jobs targeting SP_PO_INFO
• Check if the repeat_interval is set incorrectly (e.g., running hourly instead of daily)
• Note the failure_count—if the job fails, Oracle might retry it multiple times, causing duplicate emails

2. Verify Job Execution Logs #

Check the actual run history to confirm when the job executed:

SELECT job_name, log_date, status, run_duration
FROM dba_scheduler_job_run_details
WHERE job_name LIKE '%SP_PO_INFO%'
ORDER BY log_date DESC;

This will show you exactly when the job ran, how many times, and if any runs failed.

3. Check Time Zone Mismatches #

If your database timezone doesn't match the server timezone, the scheduled 17:00 could shift to an earlier time. Verify with:

SELECT dbtimezone, sessiontimezone FROM dual;

Compare this to your server's system timezone to rule out a shift causing the 13-16:00 execution window.

Second: Audit Your Stored Procedure Code #

Even if the job runs once, your code might be sending multiple emails by design (or accident). Here are key spots to check:

1. The MAIL_ADD Cursor is Driving Email Sends #

Your code loops over the MAIL_ADD cursor, and sends one email per row in this cursor. Run this query manually to see how many rows it returns:

SELECT distinct TO_CHAR(PMN33, 'yyyy-mm-dd') PMN33,gen01, GEN02, GEN06 
FROM ZING.PMN_FILE, ZING.PMK_FILE, ZING.GEN_FILE, ZING.GEM_FILE 
WHERE PMN24 = PMK01 
  AND PMK25 = '2' 
  AND PMK12 = GEN01(+) 
  AND TO_CHAR(PMN33, 'yyyy-mm-dd')= TO_CHAR(SYSDATE, 'yyyy-mm-dd');

If this returns 6-7 rows, that's exactly why you're getting 6-7 emails. Your current logic sends one email per recipient (or per unique row in this cursor), not a single email to all recipients.

2. Weekday Check Might Be Broken #

Your code skips emails on weekends using TO_CHAR(SYSDATE, 'd') = '1' OR '7', but this depends on the database's NLS_TERRITORY setting:

• For territories like AMERICA, Sunday = 1, Saturday =7
• For territories like CHINA, Monday=1, Sunday=7 (so Saturday would be 6, and your code would still send emails on Saturdays)

Verify your territory setting with:

SELECT value FROM nls_database_parameters WHERE parameter='NLS_TERRITORY';

If your territory uses a different numbering, your weekend filter won't work as expected.

3. Missing Error Handling #

Your procedure has no exception block. If the code fails mid-execution (e.g., SMTP connection issue), the job might retry, and partial email sends could happen. Add error handling to log issues and prevent retries from causing duplicates:

EXCEPTION
    WHEN OTHERS THEN
        M_SQLERRM := SQLERRM;
        -- Insert error into a log table (create one if needed)
        INSERT INTO PROC_ERROR_LOG (proc_name, error_msg, error_timestamp)
        VALUES ('SP_PO_INFO', M_SQLERRM, SYSDATE);
        COMMIT;
        RETURN;

4. Cursor Logic Redundancies #

Your PO_INFO and MAIL_ADD cursors have nearly identical where clauses, but PO_INFO joins to PMM_FILE while MAIL_ADD doesn't. This could lead to mismatches between the count check (L_TOTAL) and actual data being emailed. Ensure your count query matches the data you're including in emails.

Next Steps to Pinpoint the Issue #

1. First confirm job execution: Use the scheduler logs to see if the job is running multiple times between 13-16:00, or if it's running once but your code sends multiple emails.
2. Check MAIL_ADD row count: If the cursor returns 6-7 rows, your code is working as written (sending one email per row)—you'll need to adjust the logic to send a single email to all recipients instead.
3. Validate timezone and weekend settings: Ensure the job runs at the correct time and skips weekends as intended.

内容的提问来源于stack exchange，提问作者kk luo