问题现象： 收集统计信息时报错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
编辑推荐：
- DBMS_STATS Fails ORA-06512 and ORA-2000103-03
- Oracle 19C RAC补丁安装03-03
- 数据库优化之存储电池失效排查03-03
- [20250410]GUID转换GUID_BASE64(补充4).txt03-03
- Oracle用户的PASSWORD_LIFE_TIME有哪些坑？03-03
- [20250409]执行计划出现OPT_PARAM('_optim_peek_user_binds' 'false')的相关问题.txt03-03
- Oracle19C 19.27补丁发布03-03
- Oracle19C低版本一天遭遇两BUG（ORA-04031/ORA-600）03-03
下一篇：
相关推荐
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效
2 月 9 日消息，雷神 (THUNDEROBOT) 现已宣布推出基于英
-
制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用
2 月 10 日消息，制造商 Musnap 现已在海外推出一款 Oce
热文推荐
- 数据库优化之存储电池失效排查
数据库优化之存储电池失效排查
26-03-03 - Oracle用户的PASSWORD_LIFE_TIME有哪些坑？
Oracle用户的PASSWORD_LIFE_TIME有哪些坑？
26-03-03 - Oracle19C 19.27补丁发布
Oracle19C 19.27补丁发布
26-03-03 - Oracle19C低版本一天遭遇两BUG（ORA-04031/ORA-600）
- Oracle 11g rac adg for linux安装文档
Oracle 11g rac adg for linux安装文档
26-03-03 - 记一次ORA600内部错误故障分析与修复实录
记一次ORA600内部错误故障分析与修复实录
26-03-03 - 升级到oracle 19.8后vm_concat函数不可用怎么解决
升级到oracle 19.8后vm_concat函数不可用怎么解决
26-03-03 - RAC磁盘头损坏问题处理
RAC磁盘头损坏问题处理
26-03-03 - 数据库管理-第316期 Oracle DB 23.8新特性一览（20250417）
- ASM 内存不足引发核心数据库故障分析与解决方案
ASM 内存不足引发核心数据库故障分析与解决方案
26-03-03
