Ora-24067: exceeded maximum number of subscribers for queue sys.scheduler$event_queue
这种问题是队列满了,找到队列对应的队列表
select * from dba_queues s where s.OWNER='SYS' ;
name queue_table
scheduler$event_queue SCHEDULER$_EVENT_QTAB
第一次处理
select count(1) from SCHEDULER$_EVENT_QTAB; --200多万的记录
truncate table SCHEDULER$_EVENT_QTAB;
还是报错
第二次处理:
DECLARE
po_t dbms_aqadm.aq$_purge_options_t;
BEGIN
dbms_aqadm.purge_queue_table('SCHEDULER$_EVENT_QTAB', NULL, po_t);
END;
/
还是报错
第三次处理:
只有重建队列任务scheduler$_event_queue:
1) connect / as sysdba
exec dbms_aqadm.stop_queue(queue_name => 'scheduler$_event_queue');
exec DBMS_AQADM.DROP_QUEUE_TABLE ( QUEUE_TABLE => 'scheduler$_event_qtab',FORCE => true) ;
2) Recreate the queue table:
begin
dbms_aqadm.create_queue_table
(queue_table => 'scheduler$_event_qtab',
queue_payload_type => 'sys.scheduler$_event_info',
multiple_consumers => true,
comment => 'Scheduler event queue table',
secure => true);
exception
when others then
if sqlcode = -24001 then NULL;
else raise;
end if;
end;
/
3) Recreate the queue:
begin
dbms_aqadm.create_queue
(queue_name => 'scheduler$_event_queue',
queue_table => 'scheduler$_event_qtab',
retention_time => 3600,
comment => 'Scheduler event queue');
exception
when others then
if sqlcode = -24006 then NULL;
else raise;
end if;
end;
/
4) Start the queue:
begin
dbms_aqadm.start_queue(queue_name => 'scheduler$_event_queue');
exception
when others then
if sqlcode = -04063 then
dbms_system.ksdwrt(1, 'Error-04063 scheduler$_event_queue will be re-validated');
else
raise;
end if;
end;
/
5) Grant privileges :
begin
dbms_aqadm.grant_queue_privilege('DEQUEUE', 'SYS.SCHEDULER$_EVENT_QUEUE','PUBLIC');
end;
/
Ora-24067: exceeded maximum number of subscribers for queue
最新推荐文章于 2021-12-31 11:19:29 发布