В общем, благодаря странному коду приложения, поперли джобы на "Streams AQ: waiting for messages in the queue", т.е. фактически просто выдрючивающие
SQL код:
call DBMS_AQADM_SYS.REGISTER_DRIVER
Эти джобы можно посмотреть так
SQL код:
select * from dba_scheduler_running_jobs where lower(job_name) like 'aq$_plsql_ntfn%';
И посмотреть откуда у них растут ноги
SQL код:
select distinct n.user_data.queue_name from sys.aq_srvntfn_table_1 n;
Собственно, можно посмотреть список "потеряшек" в очереди сообщений и их количество
SQL код:
select msgid,n.enq_time,n.user_data.msg_id app_msgid,n.user_data.queue_name qname from sys.aq_srvntfn_table_1 n order by 2 desc;
SQL код:
select count(msgid) from sys.aq_srvntfn_table_1 n;
А вся необходимая информация по очередям тут
SQL код:
select * from dba_queues;
Можно попробовать сделать dequeue, т.е. "потеряшек" выкинуть из очереди
SQL код:
declare
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
dequeue_options dbms_aq.dequeue_options_t;
message_handle raw(16);
mes aq$_srvntfn_message;
begin
for a in (select msgid from SYS.AQ_SRVNTFN_TABLE_1 where enq_time<sysdate-3) loop
dequeue_options.wait := dbms_aq.no_wait;
dequeue_options.msgid := a.msgid;
dbms_aq.dequeue(queue_name => 'AQ_SRVNTFN_TABLE_Q_1',
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => mes,
msgid => message_handle);
dbms_output.put_line('removed: ' || message_handle);
commit;
end loop;
end;
если начнет ругаться, что сообщение вообще не событие, отсутствует или вроде того, то можно убрать no_wait, чтобы скрипт ждал, пока джобы очередь отпустят.
SQL код:
declare
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
dequeue_options dbms_aq.dequeue_options_t;
message_handle raw(16);
mes aq$_srvntfn_message;
begin
for a in (select msgid from SYS.AQ_SRVNTFN_TABLE_1 where enq_time<sysdate-3) loop
dequeue_options.msgid := a.msgid;
dbms_aq.dequeue(queue_name => 'AQ_SRVNTFN_TABLE_Q_1',
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => mes,
msgid => message_handle);
dbms_output.put_line('removed: ' || message_handle);
commit;
end loop;
end;
Можно вообще устроить жесткую очистку очереди
SQL код:
declare
v_purge_options dbms_aqadm.aq$_purge_options_t;
begin
v_purge_options.block := true;
dbms_aqadm.purge_queue_table('sys.aq_srvntfn_table_1', null, v_purge_options);
end;
/
А заодно еще и джобы поубивать, если мешают
SQL код:
declare
po_t dbms_aqadm.aq$_purge_options_t;
begin
po_t.block := true;
for sess in (select a.session_id,
g.serial#,
a.oracle_username,
a.os_user_name,
b.owner "OBJECT OWNER",
b.object_name,
b.object_type,
a.locked_mode
from (select object_id,
session_id,
oracle_username,
os_user_name,
locked_mode
from v$locked_object) a,
(select object_id, owner, object_name, object_type
from dba_objects) b,
v$session g
where a.object_id = b.object_id
and a.session_id = g.sid
and object_name like 'AQ_SRVNTFN_TABLE%'
order by session_id) loop
dbms_output.put_line('ALTER SYSTEM KILL SESSION ''' || sess.session_id || ',' ||
sess.serial# || ''' IMMEDIATE');
execute immediate 'ALTER SYSTEM KILL SESSION ''' || sess.session_id || ',' ||
sess.serial# || ''' IMMEDIATE';
end loop;
dbms_aqadm.purge_queue_table('sys.aq_srvntfn_table_1', null, po_t);
end;
/
а можно и просто джобы поубивать
SQL код:
begin
for a in (select job_name from dba_scheduler_running_jobs where lower(job_name) like 'aq$_plsql_ntfn%') loop
begin
DBMS_SCHEDULER.STOP_JOB (job_name => a.job_name,force=>true);
DBMS_SCHEDULER.DROP_JOB (job_name => a.job_name,force=>true);
end;
end loop;
end;
/