DBMS_SHEDUER
Step-by-Step Example
Let's assume:
You want to log blocking sessions into a table.
Later, you could extend this to automatically kill sessions.
🔹 1. Create the Logging Table
sql
Copy
Edit
BLOCKING_SESSIONS_LOG
CREATE TABLE blocking_sessions_log (
log_time TIMESTAMP,
blocker_sid NUMBER,
blocker_serial# NUMBER,
blocker_username VARCHAR2(30),
blocked_sid NUMBER,
blocked_username VARCHAR2(30)
);
SQL> set lines 200 pages 200;
SQL> col OWNER for a20;
SQL> col TABLE_NAME for a20;
SQL> col TABLESPACE_NAME for a20;
OWNER TABLE_NAME TABLESPACE_NAME
-------------------- -------------------------------------------------- --------------------
SYS SYSTEM
🔹 2. Create the Procedure to Detect Blocking Sessions
sql
Copy
Edit
CREATE OR REPLACE PROCEDURE log_blocking_sessions IS
BEGIN
FOR rec IN (
SELECT
s1.sid AS blocker_sid,
s1.serial# AS blocker_serial#,
s1.username AS blocker_username,
s2.sid AS blocked_sid,
s2.username AS blocked_username
FROM
v$lock l1
JOIN v$session s1 ON l1.sid = s1.sid
JOIN v$lock l2 ON l1.id1 = l2.id1 AND l1.id2 = l2.id2
JOIN v$session s2 ON l2.sid = s2.sid
WHERE
l1.block = 1
AND l2.request > 0
) LOOP
INSERT INTO blocking_sessions_log (
log_time,
blocker_sid,
blocker_serial#,
blocker_username,
blocked_sid,
blocked_username
) VALUES (
SYSTIMESTAMP,
rec.blocker_sid,
rec.blocker_serial#,
rec.blocker_username,
rec.blocked_sid,
rec.blocked_username
);
END LOOP;
COMMIT;
END;
/
🔹 3. Create the Program Using DBMS_SCHEDULER.CREATE_PROGRAM
sql
Copy
Edit
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'log_blocking_sessions_prog',
program_type => 'STORED_PROCEDURE',
program_action => 'LOG_BLOCKING_SESSIONS',
number_of_arguments => 0,
enabled => FALSE
);
END;
/
🔹 4. Create the Schedule (e.g., every 5 minutes)
sql
Copy
Edit
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'every_5_min_schedule',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',
comments => 'Runs every 5 minutes'
);
END;
/
🔹 5. Create the Job That Uses Program and Schedule
sql
Copy
Edit
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'log_blocking_sessions_job',
program_name => 'log_blocking_sessions_prog',
schedule_name => 'every_5_min_schedule',
enabled => TRUE
);
END;
/
🔍 Monitoring and Troubleshooting
View job runs:
sql
Copy
Edit
SELECT job_name, status, run_duration, actual_start_date FROM dba_scheduler_job_run_details WHERE job_name = 'LOG_BLOCKING_SESSIONS_JOB';
View the job setup:
SELECT * FROM dba_scheduler_jobs WHERE job_name = 'LOG_BLOCKING_SESSIONS_JOB';
Step-by-Step: Enable Email Notification for Job Status
🔹 1. Set Up Scheduler Email Settings (Once Per DB)
You need to configure Scheduler email settings for your Oracle database.
sql
Copy
Edit
BEGIN
DBMS_SCHEDULER.set_scheduler_attribute('email_server', 'smtp.gmail.com:25');
DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'sadan.dba@gmail.com');
END;
/
Replace:
smtp.yourdomain.com:25 with your SMTP server
oracle_scheduler@yourdomain.com with a valid "from" email address
🔹 2. Add Email Notification to a Specific Job
You can set a notification for job success, failure, or both.
sql
Copy
Edit
BEGIN
DBMS_SCHEDULER.add_job_email_notification (
job_name => 'LOG_BLOCKING_SESSIONS_JOB',
recipients => 'sadan.dba@gmail.com','puthapratap@gmail.com'
subject => 'Scheduler Job Notification',
events => 'job_failed, job_succeeded' -- You can also use 'job_failed' only
);
END;
/
Supported event types:
job_succeeded
job_failed
job_broken
job_disabled
job_stopped
🔹 3. Confirm Notification Attributes
You can check the configuration with:
sql
Copy
Edit
SELECT * FROM DBA_SCHEDULER_NOTIFICATIONS
WHERE job_name = 'LOG_BLOCKING_SESSIONS_JOB';
🔒 Required Privileges
Make sure the Oracle user has appropriate privileges:
MANAGE SCHEDULER
Network ACLs to send email (from Oracle 11g+)
🧪 Test Email Notification
You can force a job failure by temporarily modifying it to raise an exception, then manually running it:
sql
Copy
Edit
-- Temporary procedure to simulate failure
CREATE OR REPLACE PROCEDURE force_fail_proc IS
BEGIN
RAISE_APPLICATION_ERROR(-20001, 'Test failure');
END;
/
BEGIN
DBMS_SCHEDULER.set_scheduler_attribute('email_server', 'smtp.gmail.com:587');
DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'sadan.dba@gmail.com');
END;
/
job_name => 'LOG_BLOCKING_SESSIONS_JOB',
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl(
acl => 'gmail_acl.xml',
description => 'Allow SMTP access to Gmail',
principal => 'SYS',
is_grant => TRUE,
privilege => 'connect',
start_date => NULL,
end_date => NULL
);
DBMS_NETWORK_ACL_ADMIN.assign_acl(
acl => 'gmail_acl.xml',
host => 'smtp.gmail.com',
lower_port => 587,
upper_port => 587
);
END;
/
Comments
Post a Comment