Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/13.1.0.0/met_bsln/adaptive_table_upgrade.sql rbali_oneoff_updated/1 2025/03/14 06:41:13 rbali Exp $
Rem
Rem adaptive_table_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/met_bsln/adaptive_table_upgrade.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/13.1.0.0/met_bsln/adaptive_table_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      adaptive_table_upgrade.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
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/met_bsln/adaptive_table_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    kalchand    09/20/15 - Beta Release Version Folder Renaming
Rem    amikhare    07/10/15 - The tables are renamed for EBRification of EM
Rem                           Repository in 13.1. The original table name is
Rem                           taken by an Editioning view. All your business
Rem                           logic would access the table via Editioning view.
Rem    rnadidar    02/11/15 - cut over to EBR
Rem    nqureshi    05/07/14 - adding table for adaptive 12.2
Rem    nqureshi    05/07/14 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100

Rem    
Rem  Table:
Rem    em_metbsln_tgt_time_setting
Rem    
Rem  Description:
Rem    This table Stores Target and its business timezone ,business start and end time time in 24 hrs format
Rem
Rem  Columns:
Rem    target_guid   - target guid 
Rem    start_time    - start time in 24 hts format 
Rem    end_time    - end time in 24 hts format 
Rem    TIMEZONE_REGION - Business timezone of target
Rem

CREATE TABLE  EM_METBSLN_TGT_TIME_TZ_SETTI_E
   (
     target_guid     RAW(16)      NOT NULL,
     start_Time      NUMBER(2),
     end_Time        NUMBER(2),
     TIMEZONE_REGION VARCHAR2(64) NOT NULL,
     CONSTRAINT em_metbsln_tgt_time_pk PRIMARY KEY (target_guid)
     USING INDEX
   ); 


Rem    
Rem  Table:
Rem    em_metbsln_tgt_time_setting
Rem    
Rem  Description:
Rem    This table Stores Target and its business timezone ,business start and end time time in 24 hrs format
Rem
Rem  Columns:
Rem    target_guid   - target guid 
Rem    last_scheduled    - threshold job last_scheduled  TIME its NOT a job pickup TIME 
Rem    next_scheduled    - threshold job next_scheduled  TIME its NOT a job pickup TIME 
REM                        its expected TIME dbms job will pick the target FOR threshold push operation job submission
Rem    last_exec_status - status OF last job
Rem    message - error message IF any
Rem

CREATE TABLE  EM_METBSLN_TGT_EXEC_SCHEDULE_E
   (
     target_guid           RAW(16)      NOT NULL,
     last_scheduled        date,
     next_scheduled        date,
     next_scheduled_st     date,
     last_exec_status      NUMBER(1), 
     message               VARCHAR2(512), 
     CONSTRAINT em_metbsln_tgt_exec_sch_pk PRIMARY KEY (target_guid)
     USING INDEX
   ); 

---error handler

-- TYPE DEPENDENCY SYNONYMS DROP AND RE-CREATE

DECLARE
  TYPE l_dependency IS TABLE OF VARCHAR2(30);
  l_type_dependency l_dependency := l_dependency();
BEGIN
  
  EXECUTE IMMEDIATE 'DROP SYNONYM EM_AT_THR_VALUE_ARRAY';
  EXECUTE IMMEDIATE q'[select DISTINCT(NAME) FROM DBA_DEPENDENCIES 
    start with REFERENCED_NAME='EM_AT_THR_VALUE_ARRAY' and TYPE='TYPE' CONNECT BY NOCYCLE PRIOR NAME = REFERENCED_NAME 
      and TYPE = 'TYPE']' BULK COLLECT INTO l_type_dependency;

  IF l_type_dependency.COUNT > 0 THEN
    FOR i IN l_type_dependency.first .. l_type_dependency.last LOOP
      BEGIN
       EXECUTE IMMEDIATE 'DROP SYNONYM ' || l_type_dependency(i);
       EXCEPTION
        WHEN OTHERS THEN
        IF (SQLCODE = -1434) THEN
          CONTINUE;
        ELSE
          SYSMAN.MGMT_EMDEP_LOGGER.log_message(1, 'EBR_UPGRADE', 'Failed to Drop Synonym ' || l_type_dependency(i), 'Error : ' || SQLERRM);
          RAISE;
        END IF;
      END;
    END LOOP;
  END IF;

  EXECUTE IMMEDIATE 'ALTER TYPE  SYSMAN_TYPES.EM_AT_THR_VALUE_ARRAY MODIFY LIMIT 42 CASCADE';

  EXECUTE IMMEDIATE 'CREATE OR REPLACE NONEDITIONABLE SYNONYM EM_AT_THR_VALUE_ARRAY FOR SYSMAN_TYPES.EM_AT_THR_VALUE_ARRAY';

  FOR i IN l_type_dependency.first .. l_type_dependency.last LOOP
    EXECUTE IMMEDIATE 'CREATE OR REPLACE NONEDITIONABLE SYNONYM ' || l_type_dependency(i) || ' FOR SYSMAN_TYPES.' || l_type_dependency(i);
  END LOOP;
END;
/

declare

 begin
      EXECUTE IMMEDIATE ' ALTER SESSION ENABLE PARALLEL DDL';
     execute immediate 'alter table EM_METBSLN_RAW_E add obs_bz_time date';
      EXECUTE IMMEDIATE ' ALTER SESSION DISABLE PARALLEL DDL';
 exception 
     when others then 
        null;
 end;
 /



