I faced a very interesting question today, An oracle database 11.2.0.3 RAC database ON hpux one had to many scheduler jobs , And the job’s owner is sys, All jobs name all like ‘KWQICPOSTMSGDEL_’, All these jobs has no start date as well no interval.
SQL>
select
job_name jobs_job_name
, program_name jobs_program_name
, state jobs_state
, to_char(start_date, 'YYYY-MM-DD HH24:MI') start_date
, to_char(next_run_date, 'YYYY-MM-DD HH24:MI') next_run_date
, enabled
from
dba_scheduler_jobs
...
KWQICPOSTMSGDEL_1411382598 SCHEDULED TRUE
KWQICPOSTMSGDEL_1411382393 SCHEDULED TRUE
KWQICPOSTMSGDEL_1411381387 SCHEDULED TRUE
KWQICPOSTMSGDEL_1411381181 SCHEDULED TRUE
SQL> select count(*) from dba_scheduler_jobs;
COUNT(*)
----------
103236
SQL> select OWNER,count(*) from dba_scheduler_jobs group by owner;
OWNER COUNT(*)
------------------------------------------------------------ ----------
DBMT 2
ORACLE_OCM 4
SYS 103230
TIP:
KWQICPOSTMSGDEL scheduler jobs. This is an AQ feature posting a scheduler job to clear orphan queue messages. The job is dropped at the end of the execution and therefore there is no information about in the DBMS_SCHEDULER views, the job_queue_processes has some impact on the dbms_scheduler jobs.
SQL> @p job
NAME VALUE
---------------------------------------- ----------------------------------------
job_queue_processes 0
_job_queue_interval 5
_optimizer_autostats_job TRUE
_srvntfn_jobsubmit_interval 3
_srvntfn_max_concurrent_jobs 20
_srvntfn_job_deq_timeout 60
6 rows selected.
Solution
DOC 1360526.1 ,set job_queue_processes to appropriate value .
SQL> alter system set job_queue_processes=10;
System altered.
SQL>@p job
NAME VALUE
—————————————- —————————————-
job_queue_processes 10
_job_queue_interval 5
_optimizer_autostats_job TRUE
_srvntfn_jobsubmit_interval 3
_srvntfn_max_concurrent_jobs 20
_srvntfn_job_deq_timeout 60
#wait a moments
SQL> select OWNER,count(*) from dba_scheduler_jobs group by owner;
OWNER COUNT(*)
———————————————————— ————————-
ORACLE_OCM 2
SYS 3252
SQL> /
OWNER COUNT(*)
———————————————————— ————————-
ORACLE_OCM 2
SYS 2577
SQL> select OWNER,count(*) from dba_scheduler_jobs group by owner;
OWNER COUNT(*)
———————————————————— ———-
ORACLE_OCM 2
SYS 12
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。




