Rem drv: <migrate type="schema_upgrade" version="13.1.0.0" />
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/13.1.0.0/adminmsg/adminmsg_bus_types_upgrade.sql rbali_oneoff_updated/1 2025/03/14 06:41:13 rbali Exp $
Rem
Rem adminmsg_bus_types_upgrade.sql
Rem
Rem Copyright (c) 2015, 2025, Oracle and/or its affiliates.

Rem BEGIN EMRU_FILE_METADATA
Rem EMRU_SOURCE_FILE:emcore/source/oracle/sysman/emdrep/sql/core/13.1.0.0/adminmsg/adminmsg_bus_types_upgrade.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/13.1.0.0/adminmsg/adminmsg_bus_types_upgrade.sql

Rem EMRU_FILE_POSTINSTALL:<!!! enter the view based location of the executable file. If there is NONE leave as-is !!!>
Rem EMRU_GENERIC_FLAG:NONE
Rem END EMRU_FILE_METADATA

Rem
Rem    NAME
Rem      adminmsg_bus_types_upgrade.sql - Upgrade of admin bus type in v 13
Rem
Rem    DESCRIPTION
Rem      Change to admin bus type
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    BEGIN SQL_FILE_METADATA 
Rem    SQL_SOURCE_FILE: emcore/source/oracle/sysman/emdrep/sql/core/13.1.0.0/adminmsg/adminmsg_bus_types_upgrade.sql 
Rem    SQL_SHIPPED_FILE: 
Rem    SQL_PHASE: 
Rem    SQL_STARTUP_MODE: NORMAL 
Rem    SQL_IGNORABLE_ERRORS: NONE 
Rem    SQL_CALLING_FILE: 
Rem    END SQL_FILE_METADATA
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    gbafna      01/07/25 - make synonyms noneditionable
Rem    njagathe    07/14/15 - Initial version
Rem    njagathe    07/14/15 - Created
Rem

ALTER SESSION SET CURRENT_SCHEMA=SYSMAN;

-- Stop and drop the Cache Bus Queue before updating the type
DECLARE
    l_count NUMBER := 0;
BEGIN
  SELECT count(1)
    INTO   l_count
    FROM   all_objects
    WHERE  OBJECT_NAME = 'MGMT_ADMINMSG_BUS' AND
           OWNER =  '&&EM_REPOS_USER';

  IF (l_count > 0) THEN
    BEGIN
       DBMS_AQADM.STOP_QUEUE (queue_name => '&&EM_REPOS_USER'||'.MGMT_ADMINMSG_BUS');
       DBMS_AQADM.DROP_QUEUE (queue_name => '&&EM_REPOS_USER'||'.MGMT_ADMINMSG_BUS');
       DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => '&&EM_REPOS_USER'||'.MGMT_ADMINMSG_BUS');
    EXCEPTION
      WHEN OTHERS THEN
         NULL;
    END;
  END IF;
END;
/
COMMIT;

ALTER SESSION SET CURRENT_SCHEMA=SYSMAN_TYPES;


ALTER TYPE MGMT_ADMIN_MSG
  ADD ATTRIBUTE (msg_broadcast_timestamp TIMESTAMP, 
                 msg_deq_timestamp       TIMESTAMP) INVALIDATE;

grant execute on MGMT_ADMIN_MSG to sysman;

create or replace noneditionable synonym SYSMAN.MGMT_ADMIN_MSG for MGMT_ADMIN_MSG;

ALTER SESSION SET CURRENT_SCHEMA=SYSMAN;

-- Create and start the Cache Bus Queue
BEGIN
  DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => '&&EM_REPOS_USER'||'.MGMT_ADMINMSG_BUS',
                               queue_payload_type => '&&EM_REPOS_USER'||'_TYPES.MGMT_ADMIN_MSG',
                               multiple_consumers => TRUE);
  
  DBMS_AQADM.CREATE_QUEUE (queue_name => '&&EM_REPOS_USER'||'.MGMT_ADMINMSG_BUS',
                           queue_table => '&&EM_REPOS_USER'||'.MGMT_ADMINMSG_BUS');
  
  DBMS_AQADM.START_QUEUE (queue_name => '&&EM_REPOS_USER'||'.MGMT_ADMINMSG_BUS');
END;
/

