How to drop and recreate SYS.SYS$SERVICE_METRICS_TAB

简介: 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       
相关文章
|
6月前
|
SQL Oracle 关系型数据库
SYS_AUTO_SPM_EVOLVE_TASK
AUTO_STATS_ADVISOR_TASK SYS_AUTO_SPM_EVOLVE_TASK
51 0
|
6月前
|
数据库
Greenplum【部署 09】has an active database process on port = 6000 和 [Errno 2] No such file or directory
Greenplum【部署 09】has an active database process on port = 6000 和 [Errno 2] No such file or directory
116 0
|
6月前
Example: Auditing the SYS User
Example: Auditing the SYS User
24 0
|
6月前
|
Oracle 关系型数据库
【已解决】ORA-00001: unique constraint (USR_JXZX_DSJKF_MODEL.SYS_C00912833) violated
【已解决】ORA-00001: unique constraint (USR_JXZX_DSJKF_MODEL.SYS_C00912833) violated
165 0
|
关系型数据库 MySQL 数据库
【报错】DVWA遇到Could not connect to the database service. Please check the config file. Database Error
【报错】DVWA遇到Could not connect to the database service. Please check the config file. Database Error
1200 0
|
关系型数据库 MySQL
lower_case_table_names=1 启动报错 mysql8.0
lower_case_table_names=1 启动报错 mysql8.0
893 0
|
存储 关系型数据库 MySQL
【MySQL】lower_case_table_names参数详解
简介: lower_case_table_names 是mysql设置大小写是否敏感的一个参数。
1041 0
|
SQL 数据库 数据库管理
Replication-Replication Distribution Subsystem: agent xxxxxx failed. Column names in each table must be unique
原文:Replication-Replication Distribution Subsystem: agent xxxxxx failed. Column names in each table must be unique   最近遇到一个关于发布订阅(Replication)的奇葩问题,特此记录一下这个案例。
1036 0