--
connect &METADATAOWNER/&METADATAPASSWORD@&TNSALIAS
--
--
-- ************************************************
-- *           STOP the Queue                     *
-- ************************************************
--
DECLARE
  non_existant_queue exception;
  PRAGMA EXCEPTION_INIT( non_existant_queue , -24010 );
BEGIN
  begin
    dbms_aqadm.stop_queue('&METADATAOWNER..REPOSITORY_EVENTS_QUEUE' );
  exception
    when non_existant_queue then
        null;
  end;
END;
/
commit
/
--
-- ************************************************
-- *      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('&METADATAOWNER..REPOSITORY_EVENTS_QUEUE', 
                                 DBMS_AQ.NAMESPACE_AQ, 
                                 'plsql://&METADATAOWNER..XDB_ASYNCHRONOUS_EVENTS.DEQUEUE_EVENT', 
                                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;
/
commit
/
--
-- ************************************************
-- *               DROP the Queue                 *
-- ************************************************
--
declare
  non_existant_queue exception;
  PRAGMA EXCEPTION_INIT( non_existant_queue , -24010 );
begin
  begin
    DBMS_AQADM.DROP_QUEUE ('&METADATAOWNER..REPOSITORY_EVENTS_QUEUE');
  exception
    when non_existant_queue then
        null;
  end;
end;
/ 
commit
/
--
-- ************************************************
-- *           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 ('&METADATAOWNER..REPOSITORY_EVENTS_TABLE');
  exception
    when non_existant_queue_table then
        null;
  end;
end;
/ 
commit
/
connect &DBA/&DBAPASSWORD@&TNSALIAS
--
/*
--
-- ************************************************
-- *          Revoke AQ Permissions               *
-- ************************************************
--
DECLARE
  not_granted exception;
  PRAGMA EXCEPTION_INIT( not_granted , -1927 );
  not_found exception;
  PRAGMA EXCEPTION_INIT( not_found , -1917 );
begin
  begin
    execute immediate 'revoke execute on DBMS_AQ from &METADATAOWNER';
  exception
    when not_granted then
        null;
    when not_found then
        null;
  end;
end;
/
commit
/
*/
DECLARE
  not_granted exception;
  PRAGMA EXCEPTION_INIT( not_granted , -1951 );
  not_found exception;
  PRAGMA EXCEPTION_INIT( not_found , -1917 );
begin
  begin
    execute immediate 'revoke AQ_ADMINISTRATOR_ROLE from &METADATAOWNER';
  exception
    when not_granted then
        null;
    when not_found then
        null;
  end;
end;
/
commit
/
disconnect
--
