Oracle Database 12c -- SYS.AQ_SRVNTFN_TABLE_1 Top Space Consumer

Problem

Segment SYS.AQ_SRVNTFN_TABLE_1 has grown too much, and is top space consumer of the SYSTEM tablespace

Solution

SYS.AQ_SRVNTFN_TABLE_1 is a queue table, used by system queue SYS.AQ_SRVNTFN_TABLE_Q_1. This queue is used for AQ PL/SQL notifications.

When there is a PL\SQL procedure subscription for an application queue, Oracle will place a notification message in the SYS.AQ_SRVNTFN_TABLE_Q_1 queue, and will call a callback procedure.

Verify if there are any subscriptions:

SQL>  select * from dba_subscr_registrations;

Check if the subscriber is getting notifications, counters should increment:

SQL> select * from v$subscr_registration_stats;  

Check if  SYS.AQ_SRVNTFN_TABLE_Q_1 grows with:

SQL> select * from v$aq;

There are two reasons for the SYS.AQ_SRVNTFN_TABLE_Q_1 queue to grow:

1) the callback procedure does not work properly and does not dequeue messages from the application queue

2) there is unsynchronization between messages in the application queue and in the SYS.AQ_SRVNTFN_TABLE_Q_1

To fix the problem follow the steps:

1) Unregister the callback procedure, that is, remove the notification subscription using DBMS_AQ.UNREGISTER procedure

2) Start purging the notification queue as SYS user:

DECLARE
   po dbms_aqadm.aq$_purge_options_t;
BEGIN
   po.block := FALSE;
   DBMS_AQADM.PURGE_QUEUE_TABLE(
     queue_table     => 'AQ_SRVNTFN_TABLE_1',
     purge_condition => NULL,
     purge_options   => po);
END;
/ 

3) At this time, the purging might get blocked by the dbms_scheduler jobs, identify those and kill:

SQL> select * from dba_scheduler_running_jobs where job_name like 'AQ$_PLSQL_NTFN%'; 

4) Once blocking jobs killes, the purging from step 2) will succeed