How to drop and recreate SYS.SYS$SERVICE_METRICS_TAB

1. Check the existence of the SYS$SERVICE_METRICS_TAB
   SQL> col object_name for a30
   SQL> select object_name, object_type from dba_objects
   where objectname like '%SYS$SERVICE%';
   OBJECT_NAME OBJECT_TYPE

---

AQ$_SYS$SERVICE_METRICS_TAB_S TABLE
AQ$_SYS$SERVICE_METRICS_TAB_V EVALUATION CONTEXT
AQ$_SYS$SERVICE_METRICS_TAB_T TABLE
AQ$SYS$SERVICE_METRICS_TAB_S VIEW
AQ$_SYS$SERVICE_METRICS_TAB_N SEQUENCE
AQ$_SYS$SERVICE_METRICS_TAB_H TABLE
AQ$_SYS$SERVICE_METRICS_TAB_G TABLE
AQ$_SYS$SERVICE_METRICS_TAB_I TABLE
AQ$_SYS$SERVICE_METRICS_TAB_E QUEUE
AQ$_SYS$SERVICE_METRICS_TAB_F VIEW
AQ$SYS$SERVICE_METRICS_TAB VIEW
AQ$SYS$SERVICE_METRICS_TAB_R VIEW
SYS$SERVICE_METRICS_R RULE SET
SYS$SERVICE_METRICS_TAB TABLE
SYS$SERVICE_METRICS_N RULE SET
SYS$SERVICE_METRICS QUEUE

1. Modify parameter job_queue_processes to 0 so that none of the AQ jobs start.
   alter system set job_queue_processes=0 scope=both sid='*';

1. Drop Table SYS.SYS$SERVICE_METRICS_TAB:
   exec dbms_aqadm.drop_queue_table(queue_table=>'SYS.SYS$SERVICE_METRICS_TAB',force => TRUE);

NOTE: If this is a PDB, then connect to it and execute:

alter session set "_ORACLE_SCRIPT" = true;
exec dbms_aqadm.drop_queue_table(queue_table=>'SYS.SYS$SERVICE_METRICS_TAB',force => TRUE);

1. Check whether the table,SYS$SERVICE_METRICS_TAB, dropped or not
   SQL> col object_name for a30
   SQL> select object_name, object_type from dba_objects
   where objectname like '%SYS$SERVICE%';
   no rows selected

1. Recreate Queue tables:
   @$ORACLE_HOME/rdbms/admin/execsvr.sql

1. Check whether the table SYS$SERVICE_METRICS_TAB, recreated or not:
   SQL> col object_name for a30
   SQL> select object_name, object_type from dba_objects
   where objectname like '%SYS$SERVICE%';
   OBJECT_NAME OBJECT_TYPE

AQ$_SYS$SERVICE_METRICS_TAB_S TABLE
AQ$_SYS$SERVICE_METRICS_TAB_V EVALUATION CONTEXT
AQ$_SYS$SERVICE_METRICS_TAB_T TABLE
AQ$SYS$SERVICE_METRICS_TAB_S VIEW
AQ$_SYS$SERVICE_METRICS_TAB_N SEQUENCE
AQ$_SYS$SERVICE_METRICS_TAB_H TABLE
AQ$_SYS$SERVICE_METRICS_TAB_G TABLE
AQ$_SYS$SERVICE_METRICS_TAB_I TABLE
AQ$_SYS$SERVICE_METRICS_TAB_E QUEUE
AQ$_SYS$SERVICE_METRICS_TAB_F VIEW
AQ$SYS$SERVICE_METRICS_TAB VIEW
AQ$SYS$SERVICE_METRICS_TAB_R VIEW
SYS$SERVICE_METRICS_R RULE SET
SYS$SERVICE_METRICS_TAB TABLE
SYS$SERVICE_METRICS_N RULE SET
SYS$SERVICE_METRICS QUEUE

1. Modify parameter job_queue_processes
   alter system set job_queue_processes= scope=both sid='*';

Example:

alter system set job_queue_processes=10 scope=both sid='*';
If you explicitly set aq_tm_processes = 10 then there will be no processes available to maintain buffered messages.

Instance bounce might be required.

1. Check AQ table is getting populated or not.
   SQL> select count(), msg_state, queue from
   AQ$SYS$SERVICE_METRICS_TAB
   group by msg_state,queue;
   COUNT() MSG_STATE QUEUE

   105 PROCESSED        SYS$SERVICE_METRICS            
     5 READY            SYS$SERVICE_METRICS