问题现象：
收集统计信息时报错ORA-06512 and ORA-20001
ERROR at line 1:
ORA-20001: subscribe_AQ_agent: ST$T15401 - -24067: ORA-24067: exceeded maximum number of subscribers for queue SYS.SCHEDULER$_EVENT_QUEUE
ORA-06512: at "SYS.DBMS_STATS", line 24281
ORA-06512: at "SYS.DBMS_STATS", line 24332
报错队列满了
找到队列对应的队列表
SQL> select OWNER,NAME,QUEUE_TABLE from dba_queues s where s.OWNER='SYS';
OWNER NAME QUEUE_TABLE
------------------------------ ------------------------------ ------------------------------
SYS SCHEDULER$_EVENT_QUEUE SCHEDULER$_EVENT_QTAB
SYS AQ$_KUPC$DATAPUMP_QUETAB_3_E KUPC$DATAPUMP_QUETAB_3
SYS AQ$_KUPC$DATAPUMP_QUETAB_1_E KUPC$DATAPUMP_QUETAB_1
SYS SYS$SERVICE_METRICS SYS$SERVICE_METRICS_TAB
SYS AQ$_SYS$SERVICE_METRICS_TAB_E SYS$SERVICE_METRICS_TAB
SYS AQ$_KUPC$DATAPUMP_QUETAB_E KUPC$DATAPUMP_QUETAB
SYS AQ_PROP_NOTIFY AQ_PROP_TABLE
SYS AQ$_AQ_PROP_TABLE_E AQ_PROP_TABLE
SYS AQ_EVENT_TABLE_Q AQ_EVENT_TABLE
SYS AQ$_AQ_EVENT_TABLE_E AQ_EVENT_TABLE
SYS SRVQUEUE AQ$_MEM_MC
OWNER NAME QUEUE_TABLE
------------------------------ ------------------------------ ------------------------------
SYS AQ$_AQ$_MEM_MC_E AQ$_MEM_MC
SYS ALERT_QUE ALERT_QT
SYS AQ$_ALERT_QT_E ALERT_QT
SYS SCHEDULER_FILEWATCHER_Q SCHEDULER_FILEWATCHER_QT
SYS AQ$_SCHEDULER_FILEWATCHER_QT_E SCHEDULER_FILEWATCHER_QT
SYS SCHEDULER$_REMDB_JOBQ SCHEDULER$_REMDB_JOBQTAB
SYS AQ$_SCHEDULER$_REMDB_JOBQTAB_E SCHEDULER$_REMDB_JOBQTAB
SYS AQ$_SCHEDULER$_EVENT_QTAB_E SCHEDULER$_EVENT_QTAB
问题处理：
1、尝试清理该表
SQL> select count(1) from SCHEDULER$_EVENT_QTAB;
COUNT(1)
----------
17711
SQL> truncate table SCHEDULER$_EVENT_QTAB;
truncate table SCHEDULER$_EVENT_QTAB
ERROR at line 1:
ORA-24005: Inappropriate utilities used to perform DDL on AQ table SYS.SCHEDULER$_EVENT_QTAB
SQL> alter session set events '10851 trace name context forever,level 1' ;
Session altered.
SQL> truncate table SCHEDULER$_EVENT_QTAB;
Table truncated.
SQL> alter session set events '10851 trace name context off ';
Session altered.
SQL> select count(1) from SCHEDULER$_EVENT_QTAB;
COUNT(1)
----------
0
收集统计信息还是报错 。
2、尝试purge该表
DECLARE
po_t dbms_aqadm.aq$_purge_options_t;
BEGIN
dbms_aqadm.purge_queue_table('SCHEDULER$_EVENT_QTAB', NULL, po_t);
END;
/
收集统计信息还是报错。
3、尝试重建队列任务scheduler$_event_queue
（参考文档： DBMS_STATS Fails ORA-06512 and ORA-20001: exception in perform_cleanups: -24010: ORA-24010 (Doc ID 2660765.1)）
1) Drop and recreate the SYS.SCHEDULER$_EVENT_QTAB and related queue and underneath AQ objects, as follows:
sqlplus / 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;
/
尝试收集统计信息，成功。
SQL> select count(1) from SCHEDULER$_EVENT_QTAB;
COUNT(1)
----------
17
SQL> select OWNER,NAME,QUEUE_TABLE from dba_queues s where s.OWNER='SYS';
OWNER NAME QUEUE_TABLE
------------------------------ ------------------------------ ------------------------------
SYS SCHEDULER$_EVENT_QUEUE SCHEDULER$_EVENT_QTAB
SYS AQ$_SCHEDULER$_EVENT_QTAB_E SCHEDULER$_EVENT_QTAB
SYS AQ$_KUPC$DATAPUMP_QUETAB_3_E KUPC$DATAPUMP_QUETAB_3
SYS AQ$_KUPC$DATAPUMP_QUETAB_1_E KUPC$DATAPUMP_QUETAB_1
SYS SYS$SERVICE_METRICS SYS$SERVICE_METRICS_TAB
SYS AQ$_SYS$SERVICE_METRICS_TAB_E SYS$SERVICE_METRICS_TAB
SYS AQ$_KUPC$DATAPUMP_QUETAB_E KUPC$DATAPUMP_QUETAB
SYS AQ_PROP_NOTIFY AQ_PROP_TABLE
SYS AQ$_AQ_PROP_TABLE_E AQ_PROP_TABLE
SYS AQ_EVENT_TABLE_Q AQ_EVENT_TABLE
SYS AQ$_AQ_EVENT_TABLE_E AQ_EVENT_TABLE
OWNER NAME QUEUE_TABLE
------------------------------ ------------------------------ ------------------------------
SYS SRVQUEUE AQ$_MEM_MC
SYS AQ$_AQ$_MEM_MC_E AQ$_MEM_MC
SYS ALERT_QUE ALERT_QT
SYS AQ$_ALERT_QT_E ALERT_QT
SYS SCHEDULER_FILEWATCHER_Q SCHEDULER_FILEWATCHER_QT
SYS AQ$_SCHEDULER_FILEWATCHER_QT_E SCHEDULER_FILEWATCHER_QT
SYS SCHEDULER$_REMDB_JOBQ SCHEDULER$_REMDB_JOBQTAB
SYS AQ$_SCHEDULER$_REMDB_JOBQTAB_E SCHEDULER$_REMDB_JOBQTAB
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29956245/viewspace-3079986/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
