--
connect &METADATAOWNER/&USERPASSWORD@&TNSALIAS
--
--
-- ************************************************
-- *           STOP the Queue                     *
-- ************************************************
--
DECLARE
  non_existant_queue exception;
  PRAGMA EXCEPTION_INIT( non_existant_queue , -24010 );
BEGIN
  begin
    dbms_aqadm.stop_queue(XDB.XDB_ASYNCHRONOUS_EVENTS.REPOSITORY_EVENTS_QUEUE);
  exception
    when non_existant_queue then
        null;
  end;
END;
/
--
-- ************************************************
-- *      DE-REGISTER the Event Handler           *
-- ************************************************
--
DECLARE 
  non_existant_queue exception;
  PRAGMA EXCEPTION_INIT( non_existant_queue , -25205 );

  queue_not_registered exception;
  PRAGMA EXCEPTION_INIT( queue_not_registered , -24950 );

  reginfo      sys.aq$_reg_info; 
  reginfolist  sys.aq$_reg_info_list; 
BEGIN 

  begin   
    reginfo := sys.aq$_reg_info(XDB.XDB_ASYNCHRONOUS_EVENTS.REPOSITORY_EVENTS_QUEUE', 
                                DBMS_AQ.NAMESPACE_AQ, 
                                XDB.XDB_ASYNCHRONOUS_EVENTS.EVENT_HANDLER, 
                                HEXTORAW('FF')); 

    reginfolist := sys.aq$_reg_info_list(reginfo); 
    sys.dbms_aq.unregister(reginfolist, 1); 
  exception
    when non_existant_queue then
        null;
    when queue_not_registered then
        null;
  end;
END;
/
--
-- ************************************************
-- *               DROP the Queue                 *
-- ************************************************
--
declare
  non_existant_queue exception;
  PRAGMA EXCEPTION_INIT( non_existant_queue , -24010 );
begin
  begin
    DBMS_AQADM.DROP_QUEUE (XDB.XDB_ASYNCHRONOUS_EVENTS.REPOSITORY_EVENTS_QUEUE);
  exception
    when non_existant_queue then
        null;
  end;
end;
/ 
--
-- ************************************************
-- *           DROP the Queue Table               *
-- ************************************************
--
DECLARE
  non_existant_queue_table exception;
  PRAGMA EXCEPTION_INIT( non_existant_queue_table , -24002 );
begin
  begin
    DBMS_AQADM.DROP_QUEUE_TABLE (XDB.XDB_ASYNCHRONOUS_EVENTS.REPOSITORY_EVENTS_TABLE);
  exception
    when non_existant_queue_table then
        null;
  end;
end;
/ 
--
-- ************************************************
-- *     Revoke AQ Permissions from XDB           *
-- ************************************************
--
DECLARE
  not_granted exception;
  PRAGMA EXCEPTION_INIT( not_granted , -1927 );
begin
  begin
    execute immediate 'revoke execute on DBMS_AQ from XDB';
  exception
    when not_granted then
        null;
  end;
end;
/
DECLARE
  not_granted exception;
  PRAGMA EXCEPTION_INIT( not_granted , -1951 );
begin
  begin
    execute immediate 'revoke AQ_ADMINISTRATOR_ROLE from XDB';
  exception
    when not_granted then
        null;
  end;
end;
/
disconnect
--
