DBMS_JOB API example to kill DB session of a concurrent request:
\nThe DBMS_JOB package schedules and manages jobs in the job queue.<\/p>\n
Main Procedures that will be used:<\/span> DBMS_JOB.RUN<\/span> runs the job specified. Here\u2019s an example on how to use this package to kill the session of a concurrent request from a PL\/SQL procedure. DBMS_JOB API example to kill DB session of a concurrent request: The DBMS_JOB package schedules and manages jobs in the job queue. Main Procedures that will be used: DBMS_JOB.SUBMIT submits the job to the job queue. You must issue a COMMIT statement immediately after the statement. Parameters: job: Number of the job being run. what: […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[500],"tags":[199,200,201],"class_list":["post-4596","post","type-post","status-publish","format-standard","hentry","category-sysadmin-api","tag-dbms_job-run","tag-dbms_job-submit","tag-kill-session-from-plsql"],"yoast_head":"\n
\nDBMS_JOB.SUBMIT<\/span> submits the job to the job queue. You must issue a COMMIT statement immediately after the statement.
\nParameters:<\/span>
\njob: Number of the job being run.
\nwhat: PL\/SQL procedure to run.
\nnext_date: Next date when the job will be run.
\ninterval: Date function that calculates the next time to run the job. The default is NULL. This must evaluate to a either a future point in time or NULL.
\nno_parse: A flag. The default is FALSE. If this is set to FALSE, then Oracle parses the procedure associated with the job. If this is set to TRUE, then Oracle parses the procedure associated with the job the first time that the job is run.
\nFor example, if you want to submit a job before you have created the tables associated with the job, then set this to TRUE.
\ninstance: When a job is submitted, specifies which instance can run the job.
\nforce: If this is TRUE, then any positive integer is acceptable as the job instance. If this is FALSE (the default), then the specified instance must be running; otherwise the routine raises an exception.<\/p>\n
\nParameters:<\/span>
\njob: Number of the job being run.
\nforce: If this is TRUE, then instance affinity is irrelevant for running jobs in the foreground process. If this is FALSE, then the job can be run in the foreground only in the specified instance.<\/p>\n
\n
\nDECLARE
\nl_mon_req_id NUMBER := fnd_global.conc_request_id;
\nl_jobnum NUMBER;
\nBEGIN
\n--Get session details of the concurrent request you want kill
\nSELECT d.sid,
\nd.inst_id,
\nd.serial#
\nFROM apps.fnd_concurrent_requests a,
\napps.fnd_concurrent_processes b,
\ngv$process c,
\ngv$session d
\nWHERE a.controlling_manager =
\nb.concurrent_process_id
\nAND c.pid = b.oracle_process_id
\nAND d.paddr = c.addr
\nAND a.request_id = l_mon_req_id;
\n--Submit the job to kill the session to the job queue
\nDBMS_JOB.submit (
\njob => l_jobnum,
\nwhat => 'begin execute immediate ''alter system kill session '''''
\n|| s.sid
\n|| ','
\n|| s.serial#
\n|| '''''''; end;',
\ninstance => s.inst_id);
\nCOMMIT;
\n--Run the job
\nDBMS_JOB.run (l_jobnum);
\nEND;
\n<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"