Ora-24067: exceeded maximum number of subscribers for queue

水深无声 2022-10-07 05:59 21阅读 0赞

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;
    /