Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/13.1.0.0/loaderjob/loaderjob_tables.sql rbali_oneoff_updated/1 2025/03/14 06:41:13 rbali Exp $
Rem
Rem loaderjob_tables.sql
Rem
Rem Copyright (c) 2014, 2025, Oracle and/or its affiliates. 
Rem All rights reserved.

Rem BEGIN EMRU_FILE_METADATA
Rem EMRU_SOURCE_FILE:emcore/source/oracle/sysman/emdrep/sql/core/13.1.0.0/loaderjob/loaderjob_tables.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/13.1.0.0/loaderjob/loaderjob_tables.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      loaderjob_tables.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/latest/loaderjob/loaderjob_tables.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    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    kalchand    11/17/14 - Offending Types move to SYSMAN_TYPES Schema
Rem    pinshah     10/24/14 - support for incremental (row level diffs) config metrics
Rem    soumegho    07/25/14 - Created
Rem

ALTER SESSION SET CURRENT_SCHEMA = SYSMAN_TYPES;

CREATE OR REPLACE TYPE LOADER_STR_ARRAY AS VARRAY(1000) OF VARCHAR2(1000);
/

GRANT EXECUTE ON LOADER_STR_ARRAY TO SYSMAN WITH GRANT OPTION;

CREATE OR REPLACE NONEDITIONABLE SYNONYM SYSMAN.LOADER_STR_ARRAY FOR LOADER_STR_ARRAY;

ALTER SESSION SET CURRENT_SCHEMA = SYSMAN;

CREATE TABLE EM_LOADERJOB_QUEUE_PAYLOAD_E (
        payload_id NUMBER,
        command_type VARCHAR2(128),
        is_empty VARCHAR2(64),
        target_guid VARCHAR2(64),
        target_type VARCHAR2(256),
        target_name VARCHAR2(256),
        collection_name VARCHAR2(512),
        collection_timestamp VARCHAR2(64),
        emd_url_string VARCHAR2(1024),
        conf_vers VARCHAR2(128),
        ver_string VARCHAR2(128),
        load_snapshot_id VARCHAR2(1024),
        job_name VARCHAR2(128),
        mtc_guids_list SYSMAN_TYPES.LOADER_STR_ARRAY,
        metric_tables_list SYSMAN_TYPES.LOADER_STR_ARRAY,
        prev_info_array SYSMAN_TYPES.LOADER_STR_ARRAY,
        is_incremental_array SYSMAN_TYPES.LOADER_STR_ARRAY,
        sup_data_keys SYSMAN_TYPES.LOADER_STR_ARRAY,
        sup_data_values SYSMAN_TYPES.LOADER_STR_ARRAY,
        preload_keys SYSMAN_TYPES.LOADER_STR_ARRAY,
        preload_values SYSMAN_TYPES.LOADER_STR_ARRAY,
	constraint EM_LOADERJOB_QUEUE_PAYLOAD_PK
                PRIMARY KEY (payload_id) USING INDEX
);


CREATE INDEX IDX_EM_LD_PL
        ON EM_LOADERJOB_QUEUE_PAYLOAD_E(target_guid);

CREATE TABLE EM_LOADERJOB_TARGETS_E (
        target_guid VARCHAR2(64),
        current_processing_seq_no NUMBER,
        is_locked NUMBER,
        start_time TIMESTAMP(6) WITH TIME ZONE,
        end_time TIMESTAMP(6) WITH TIME ZONE,
        constraint EM_LOADERJOB_TARGETS_PK
                PRIMARY KEY (target_guid) USING INDEX
);

CREATE TABLE EM_LOADERJOB_STATUS_E (
        job_name VARCHAR2(128),
        status NUMBER,
        message VARCHAR2(100),
        enqueue_time TIMESTAMP,
        dequeue_time TIMESTAMP,
        job_end_time TIMESTAMP,
        oms_name VARCHAR2(256),
        thread_name VARCHAR2(256),
        payload_id NUMBER
);


CREATE TABLE EM_LOADERJOB_LOG_E (
  id             NUMBER(10)     NOT NULL,
  log_timestamp  TIMESTAMP      NOT NULL,
  error_message  VARCHAR2(4000),
  thread_id VARCHAR2(128),
  CONSTRAINT EM_LOADERJOB_LOG_PK PRIMARY KEY (id)
);


CREATE UNIQUE INDEX IDX_EM_LD_ST
        ON EM_LOADERJOB_STATUS_E(job_name);

show errors;



