Removing RIB_TAFR Subscriber Taking Too Long (Doc ID 2965914.1)

Last updated on AUGUST 07, 2023

Applies to:

Goal

The user is trying to remove all RIB_TAFR subscribers due to decommissioning. User is using this SQL:

DECLARE
  subscriber sys.aq$_agent;
  myname varchar(50);
BEGIN
  FOR myname in (select queue_name,consumer_name from dba_queue_subscribers where consumer_name like '%RIB_TAFR%') loop
  subscriber := sys.aq$_agent (myname.consumer_name, NULL, null);
  DBMS_AQADM.REMOVE_SUBSCRIBER(
  queue_name => myname.queue_name,
  subscriber => subscriber);
  end loop;
END;
/

It is taking too long because there are a huge number of records on 1 queue - ETSOSTATUS. Which has 35 million records.

What is the best way to remove this? Also, is it possible to do it while RIB is running and will it impact any running processes?
 

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.