Rem drv: <migrate type="schema_upgrade" version="10.2.0.0" pos="group/group_schema_upgrade.sql+"/>
Rem
Rem $Header: jobs_schema_upgrade.sql 06-aug-2007.02:41:41 lsatyapr Exp $
Rem
Rem jobs_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, 2007, Oracle. All rights reserved.
Rem
Rem NAME
Rem jobs_schema_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 MODIFIED (MM/DD/YY)
Rem lsatyapr 08/06/07 - Bug6316745 Drop constraint with index
Rem aptrived 12/01/06 - Adding CASCADE DROP INDEX for PK_JOB_TYPE_INFO
Rem and for PK_MGMT_JOB_PARAM
Rem bmallipe 08/13/06 - fixing the DBControl upgrade errors
Rem pshishir 08/23/05 - Creating INDEX on MGMT_CA table
Rem pkantawa 08/23/05 - Fix beta->prod upgrade issues
Rem pkantawa 08/05/05 - Fix 4432894: fix column length
Rem skini 07/29/05 - Fix bug 4325569: disable triggers prior to
Rem updating tables
Rem chyu 07/25/05 - modifying the upgrade header
Rem chyu 07/18/05 - adding the <migration> header
Rem pkantawa 06/24/05 - Fix 4453976: Add column in type
Rem nqureshi 06/21/05 - adding new attribute in the table
Rem MGMT_JOB_TYPE_PARAM_URI_INFO time
Rem nqureshi 06/20/05 - adding types created for targets props
Rem validation
Rem skini 06/06/05 - grabtrans 'skini_bug-4377344'
Rem skini 05/27/05 - Fix syntax error
Rem skini 05/27/05 - Upgrade fixes
Rem dsahrawa 05/25/05 - fix bug 4388845, diffs from upgrade
Rem ashugupt 05/17/05 - Fix 4338761
Rem jvishen 05/19/05 - change job_id type
Rem pkantawa 05/04/05 - Fix bug 4348693: upgrade MGMT_JOB_STEP_RECORD
Rem pkantawa 04/17/05 - Fix 4305554: Upgrade errors
Rem chyu 03/29/05 - syncing up the mgmt_target_fixca_record
Rem definition
Rem pkantawa 03/27/05 - Change Delete to Drop
Rem pkantawa 03/25/05 - remove runs/exec related types
Rem eujang 03/08/05 - syncing up the column definitions
Rem eujang 03/08/05 - adding the missing tables
Rem skini 03/03/05 - Add valueOf column
Rem scgrover 02/16/05 - reorder job index
Rem skini 02/16/05 - Add type migration
Rem skini 02/14/05 - Finish upgrade
Rem skini 02/10/05 - Jobs upgrade
Rem dcawley 02/08/05 - Add corrective actions table
Rem rpinnama 02/06/05 - rpinnama_add_upgrade_scripts
Rem rpinnama 02/04/05 - Created
Rem
rem
rem This script requires the following arguments
rem 1. Name of the ecm_depot tablespace where all LOBs are stored
rem
DEFINE EM_ECM_DEPOT_TABLESPACE = "&1"
DEFINE JOB_PARAM_NAME_SIZE=64
DEFINE STEP_NAME_SIZE=64
Rem Upgrade jobs types
REM Represents an record of credential column values
CREATE OR REPLACE TYPE MGMT_JOB_CRED_COL_VAL_RECORD AS OBJECT(
col_value VARCHAR2(64),
col_value_nlsid VARCHAR(64)
);
/
REM Represents an array of credential column values
CREATE OR REPLACE TYPE MGMT_JOB_CRED_COL_VAL_ARRAY AS
TABLE OF MGMT_JOB_CRED_COL_VAL_RECORD;
/
REM Represents a credentials type column
CREATE OR REPLACE TYPE MGMT_JOB_CRED_TYPE_COL_RECORD AS OBJECT (
type_column_name VARCHAR2(64),
type_column_display_name VARCHAR2(64),
base_type_column_name VARCHAR2(64),
type_column_nlsid VARCHAR2(64),
type_column_value VARCHAR2(4000),
default_value_index NUMBER(6),
current_value_index NUMBER(6),
key NUMBER(1),
allowable_values MGMT_JOB_CRED_COL_VAL_ARRAY
);
/
REM Represents a credential set columns
CREATE OR REPLACE TYPE MGMT_JOB_CRED_SET_COL_RECORD AS OBJECT (
set_column_name VARCHAR2(64),
set_column_display_name VARCHAR2(64),
type_column_name VARCHAR2(64),
default_value_index NUMBER(6),
current_value_index NUMBER(6),
allowable_values MGMT_JOB_CRED_COL_VAL_ARRAY
);
/
REM Represents an array of credential type columns
CREATE OR REPLACE TYPE MGMT_JOB_CRED_TYPE_COL_ARRAY AS
TABLE OF MGMT_JOB_CRED_TYPE_COL_RECORD;
/
REM Represents an array of credential set columns
CREATE OR REPLACE TYPE MGMT_JOB_CRED_SET_COL_ARRAY AS
TABLE OF MGMT_JOB_CRED_SET_COL_RECORD;
/
REM Represents a credential type record
CREATE OR REPLACE TYPE MGMT_JOB_CRED_TYPE_RECORD AS OBJECT (
type_name VARCHAR2(64),
type_display_name VARCHAR2(64),
type_nlsid VARCHAR2(64),
target_type VARCHAR2(64),
target_type_meta_ver VARCHAR2(64),
cred_type_cols MGMT_JOB_CRED_TYPE_COL_ARRAY
);
/
REM Represents a credential set record
CREATE OR REPLACE TYPE MGMT_JOB_CRED_SET_RECORD AS OBJECT (
set_name VARCHAR2(64),
set_display_name VARCHAR2(64),
set_nlsid VARCHAR2(64),
target_type VARCHAR2(64),
target_type_meta_ver VARCHAR2(64),
cred_set_cols MGMT_JOB_CRED_SET_COL_ARRAY
);
/
REM Represents an array of credential set
CREATE OR REPLACE TYPE MGMT_JOB_CRED_SET_ARRAY AS
TABLE OF MGMT_JOB_CRED_SET_RECORD;
/
REM Represents a credentials source
CREATE OR REPLACE TYPE MGMT_JOB_CRED_SOURCE_RECORD AS OBJECT (
cred_set_name_var VARCHAR2(64),
cred_set_target_type_var VARCHAR2(64),
b_name_var NUMBER(1),
b_target_type_var NUMBER(1),
base_cred_type MGMT_JOB_CRED_TYPE_RECORD,
derived_cred_type MGMT_JOB_CRED_TYPE_RECORD,
base_cred_type_columns MGMT_JOB_VECTOR_PARAMS,
cred_set MGMT_JOB_CRED_SET_ARRAY,
cred_column_param VARCHAR2(64),
job_cred_set_name VARCHAR2(64),
guid VARCHAR2(64)
);
/
REM Represents an array of credential source
CREATE OR REPLACE TYPE MGMT_JOB_CRED_SOURCE_ARRAY AS
TABLE OF MGMT_JOB_CRED_SOURCE_RECORD;
/
REM Represents a job credential
CREATE OR REPLACE TYPE MGMT_JOB_CREDENTIAL_RECORD AS OBJECT (
cred_source MGMT_JOB_CRED_SOURCE_ARRAY,
is_preferred NUMBER(1)
);
/
DECLARE
l_count NUMBER := 0;
BEGIN
SELECT count(*)
INTO l_count
FROM all_types
WHERE upper(type_name) = 'RUNBASICROWTYPE';
IF (l_count > 0) THEN
EXECUTE IMMEDIATE 'DROP TYPE RUNBASICROWTYPE';
END IF;
SELECT count(*)
INTO l_count
FROM all_types
WHERE upper(type_name) = 'JOBRUNTABLETYPE';
IF (l_count > 0) THEN
EXECUTE IMMEDIATE 'DROP TYPE JOBRUNTABLETYPE';
END IF;
SELECT count(*)
INTO l_count
FROM all_types
WHERE upper(type_name) = 'RUNDETAILEDROWTYPE';
IF (l_count > 0) THEN
EXECUTE IMMEDIATE 'DROP TYPE RUNDETAILEDROWTYPE';
END IF;
SELECT count(*)
INTO l_count
FROM all_types
WHERE upper(type_name) = 'JOBEXECTABLETYPE';
IF (l_count > 0) THEN
EXECUTE IMMEDIATE 'DROP TYPE JOBEXECTABLETYPE';
END IF;
SELECT count(*)
INTO l_count
FROM all_types
WHERE upper(type_name) = 'EXECUTIONROWTYPE';
IF (l_count > 0) THEN
EXECUTE IMMEDIATE 'DROP TYPE EXECUTIONROWTYPE';
END IF;
END;
/
REM Upgrade the step record type
ALTER TYPE MGMT_JOB_STEP_RECORD MODIFY ATTRIBUTE (step_type NUMBER(2)) CASCADE;
ALTER TYPE MGMT_JOB_STEP_RECORD MODIFY ATTRIBUTE (iterate_param VARCHAR2(64)) CASCADE;
-- -----------------------------------------------------------------------------
-- SQL types for job credentials validation
-- These types are used by jobs ui package
-- -----------------------------------------------------------------------------
REM Represents one credential set data for a job task. The target type
REM here represents target type for the entire job.
REM The target type in MGMT_JOB_TARGET_RECORD object represents the target type
REM of group or cluster if job is submitted for a whole group otherwise its same as
REM job type of entire job.
CREATE OR REPLACE TYPE MGMT_JOB_TGT_CRED_SET_RECORD AS OBJECT (
cred_set_name VARCHAR2(64),
target_type VARCHAR2(64),
target_list MGMT_JOB_TARGET_LIST
);
/
REM Represents an array of job target credential set. Each job task may be associated
REM to multiple credential sets and this object holds the cred set data for each job task.
CREATE OR REPLACE TYPE MGMT_JOB_TGT_CRED_SET_ARRAY AS
TABLE OF MGMT_JOB_TGT_CRED_SET_RECORD;
/
REM Represents an array of job task credential set, each of which represents
REM credential set data for the task.
CREATE OR REPLACE TYPE MGMT_JOB_TASK_CRED_SET_ARRAY AS
TABLE OF MGMT_JOB_TGT_CRED_SET_ARRAY;
/
REM Represents a parameter DropDown values record added by nqureshi
CREATE OR REPLACE TYPE MGMT_JOB_PRMETER_DRPDN_RECORD AS OBJECT (
is_default NUMBER(1),
value varchar2(32),
option_text_nlsid VARCHAR2(32),
option_text_default VARCHAR2(256),
param_order number(3)
);
/
REM Represents an array of One parameters DROPDOWN VALUES set
CREATE OR REPLACE TYPE MGMT_JOB_PRMETER_DRPDN_ARRAY AS
TABLE OF MGMT_JOB_PRMETER_DRPDN_RECORD;
/
REM represent one row of parameters
CREATE OR REPLACE TYPE MGMT_JOB_PARAMETER_RECORD AS OBJECT (
job_type VARCHAR2(32),
parameter_name VARCHAR2(64),
show_in_create NUMBER(1),
show_in_results NUMBER(1),
label_nlsid VARCHAR2(32),
label_default VARCHAR2(256),
hint_nlsid VARCHAR2(32),
hint_default VARCHAR2(256),
display_mode NUMBER(2),
num_lines NUMBER(2),
default_text VARCHAR2(4000),
default_nlsid VARCHAR2(32),
param_order number(3),
ARRAY_PARAMETER_DROPDOWN MGMT_JOB_PRMETER_DRPDN_ARRAY,
parameter_required number(1),
parameter_encrypted number(1)
);
/
REM Represents an array of ALL PARAMETERS set
CREATE OR REPLACE TYPE MGMT_JOB_PARAMETER_ARRAY AS
TABLE OF MGMT_JOB_PARAMETER_RECORD;
/
rem this will Hold the one row the MGMT_JOB_TYPE_URI_INFO
CREATE OR REPLACE TYPE MGMT_JOB_TYPE_URI_INFO_RECORD AS OBJECT (
job_type VARCHAR2(32),
uri_use NUMBER(2),
uri VARCHAR2(4000),
is_jsp NUMBER(1),
class VARCHAR2(4000)
);
/
REM Represents an array of One parameters DROPDOWN VALUES set
CREATE OR REPLACE TYPE MGMT_JOB_TYPE_URI_INFO_ARRAY AS
TABLE OF MGMT_JOB_TYPE_URI_INFO_RECORD;
/
REM to hold the ONE ROW OF THE TARGET_PROPERTY
CREATE OR REPLACE TYPE MGMT_TARGET_TYPE_PROP_RECORD AS OBJECT (
property_name VARCHAR2(32),
property_display_name VARCHAR2(255),
property_display_nls_id VARCHAR2(255)
);
/
REM Represents an array of One TARGET PROPERTY VALUES
CREATE OR REPLACE TYPE MGMT_TARGET_TYPE_PROP_ARRAY AS
TABLE OF MGMT_TARGET_TYPE_PROP_RECORD;
/
rem this will Hold the onr row the MGMT_JOB_PARAMETER_URI_INFO
CREATE OR REPLACE TYPE MGMT_JOB_PRMTER_URI_INF_RECORD AS OBJECT (
job_type VARCHAR2(32),
uri VARCHAR2(4000),
class VARCHAR2(4000),
online_help_topic VARCHAR2(4000),
show_target_properties NUMBER(1)
);
/
REM The Super Type that will contain all the sub types
create or replace type MGMT_JOB_PARAMETER_METADATA as Object
(
uri_info_metaData MGMT_JOB_TYPE_URI_INFO_ARRAY ,
parameter_uri_info_metaData MGMT_JOB_PRMTER_URI_INF_RECORD ,
parameter_metaData MGMT_JOB_PARAMETER_ARRAY
);
/
REM to hold the one row of the broken credential of a target
create or replace TYPE MGMT_TARGET_FIXCA_RECORD as object (
target_name VARCHAR2(256),
target_type VARCHAR2(64),
policy_name_display VARCHAR2(128),
column_label VARCHAR2(64),
column_label_nlsid VARCHAR2(64),
severity NUMBER(2),
ca VARCHAR2(64),
current_owner VARCHAR2(256),
job_id VARCHAR2(256),
metric_key_value VARCHAR2(1300),
broken_reason NUMBER(2),
policy_type NUMBER(2),
job_type VARCHAR2(32)
);
/
REM Represents an array of broken credentials of targets
CREATE OR REPLACE TYPE MGMT_TARGET_FIXCA_ARRAY AS
TABLE OF MGMT_TARGET_FIXCA_RECORD;
/
REM holds the metadata info for a combination of job type and target type
CREATE OR REPLACE TYPE MGMT_PARAM_CRED_TGT_MET AS OBJECT
(
job_type VARCHAR2(32),
target_type VARCHAR2(64),
parameter_metadata MGMT_JOB_PARAMETER_METADATA ,
credential_metadata MGMT_JOB_CREDENTIAL_RECORD ,
target_properties MGMT_TARGET_TYPE_PROP_ARRAY ,
required_params SMP_EMD_STRING_ARRAY ,
targets_required NUMBER(1)
);
/
REM Represents array of MGMT_PARAM_CRED_TGT_MET
CREATE OR REPLACE TYPE MGMT_PARAM_CRED_TGT_MET_ARR AS
TABLE OF MGMT_PARAM_CRED_TGT_MET;
/
-- -----------------------------------------------------------------------------
-- SQL types for fetching timezones of all targets of all tasks in a multi
-- task job
-- These types are used by jobs ui package
-- -----------------------------------------------------------------------------
REM Represents one set of target list for a task. The target type
REM here represents target type of the task.
CREATE OR REPLACE TYPE MGMT_JOB_TASK_TGT_RECORD AS OBJECT (
target_list MGMT_JOB_TARGET_LIST,
target_type VARCHAR2(64)
);
/
REM Represents an array of MGMT_JOB_TASK_TGT_RECORD - one for each task
CREATE OR REPLACE TYPE MGMT_JOB_TASK_TGT_ARRAY AS
TABLE OF MGMT_JOB_TASK_TGT_RECORD;
/
CREATE OR REPLACE TYPE MGMT_JOB_NOTIF_EMAIL_SCH_INFO AS OBJECT (
email_address_count NUMBER(3),
schedule_count NUMBER(3),
job_notify_status MGMT_INTEGER_ARRAY
);
/
---------------------------------------------------------------------------------------
----------------------------------- SCHEMA UPGRADE ----------------------------------
---------------------------------------------------------------------------------------
-- Drop indexes prior to schema upgrade
DROP INDEX MGMT_JOB_IDX02 ;
DROP INDEX MGMT_JOB_PARAM_SOURCE_IDX01;
DROP INDEX MGMT_JOB_SEC_INFO_IDX01;
DROP INDEX MGMT_JOB_LOCK_INFO_IDX01;
DROP INDEX MGMT_JT_URI_INFO_IDX01;
DROP INDEX MGMT_JT_DISPLAY_PARAM_IDX01;
DROP INDEX MGMT_JT_DISPLAY_INFO_IDX01;
-- Drop foreign key constraints first
ALTER TABLE MGMT_JOB_SINGLE_TARGET_TYPES DROP CONSTRAINT PK_SINGLE_TARGET_TYPES CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_SINGLE_TARGET_TYPES DROP CONSTRAINT FK_SINGLE_TARGET_TYPES CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_EXECPLAN DROP CONSTRAINT PK_MGMT_JOB_EXECPLAN CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_EXECPLAN DROP CONSTRAINT FK_MGMT_JOB_EXECPLAN CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_STEP_PARAMS DROP CONSTRAINT PK_MGMT_JOB_STEP_PARAMS CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_LOCK_TARGETS DROP CONSTRAINT FK_JOB_LOCK_TARGETS CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_LOCK_INFO DROP CONSTRAINT PK_MGMT_JOB_LOCK_INFO CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_LOCK_INFO DROP CONSTRAINT FK_MGMT_JOB_LOCK_INFO CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_TYPE_URI_INFO DROP CONSTRAINT FK_JOB_TYPE_URI_INFO CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_TYPE_DISPLAY_PARAM DROP CONSTRAINT FK_JOB_TYPE_DISPLAY_PARAM CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_TYPE_DISPLAY_INFO DROP CONSTRAINT FK_JOB_TYPE_DISPLAY_INFO CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_CALLBACKS DROP CONSTRAINT PK_MGMT_JOB_CALLBACKS CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_CALLBACKS DROP CONSTRAINT FK_MGMT_JOB_CALLBACKS CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_PARAMETER DROP CONSTRAINT PK_MGMT_JOB_PARAM CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_TYPE_INFO ADD (
job_type_id RAW(16) DEFAULT SYS_GUID(),
job_type_description VARCHAR2(256),
job_type_owner VARCHAR2(256),
job_type_category NUMBER(1) DEFAULT 0,
last_modified_by VARCHAR2(256),
last_modified_date DATE DEFAULT SYSDATE,
all_target_types NUMBER(1) DEFAULT 0,
major_version NUMBER DEFAULT 1,
minor_version1 NUMBER DEFAULT 0,
minor_version2 NUMBER DEFAULT 1,
job_type_nlsid VARCHAR2(64),
job_type_default VARCHAR2(256),
create_text_nlsid VARCHAR2(64),
create_text_default VARCHAR2(256),
delete_text_nlsid VARCHAR2(64),
delete_text_default VARCHAR2(256)
);
ALTER TABLE MGMT_JOB_TYPE_INFO DROP CONSTRAINT PK_JOB_TYPE_INFO CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_TYPE_INFO ADD CONSTRAINT PK_JOB_TYPE_INFO PRIMARY KEY(job_type_id);
CREATE TABLE MGMT_JOB_TYPE_MAX_VERSIONS (
job_type VARCHAR2(32),
job_type_id RAW(16),
major_version NUMBER DEFAULT 1,
minor_version1 NUMBER DEFAULT 0,
minor_version2 NUMBER DEFAULT 1,
CONSTRAINT PK_JOB_TYPE_MAX_VERSIONS PRIMARY KEY(job_type, major_version)
)
MONITORING;
INSERT INTO MGMT_JOB_TYPE_MAX_VERSIONS(job_type, job_type_id, major_version,
minor_version1, minor_version2)
SELECT job_type, job_type_id, major_version,
minor_version1, minor_version2
FROM MGMT_JOB_TYPE_INFO;
-- MGMT_JOB_SINGLE_TARGET_TYPES
ALTER TABLE MGMT_JOB_SINGLE_TARGET_TYPES ADD (
job_type_id RAW(16)
);
-- Set the job_type_id columns correctly
UPDATE MGMT_JOB_SINGLE_TARGET_TYPES t
SET job_type_id=(SELECT job_type_id FROM MGMT_JOB_TYPE_INFO WHERE job_type=t.job_type);
ALTER TABLE MGMT_JOB_SINGLE_TARGET_TYPES DROP (job_type);
-- Add the new constraints
ALTER TABLE MGMT_JOB_SINGLE_TARGET_TYPES ADD (
CONSTRAINT PK_SINGLE_TARGET_TYPES
PRIMARY KEY(job_type_id, single_target_type),
CONSTRAINT FK_SINGLE_TARGET_TYPES
FOREIGN KEY(job_type_id) REFERENCES
MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
);
-- MGMT_JOB_EXECPLAN
ALTER TABLE MGMT_JOB_EXECPLAN ADD (
job_type_id RAW(16),
nested_job_target_type VARCHAR2(64) DEFAULT NULL,
switch_var_index VARCHAR2(96),
step_nlsid varchar2(64),
step_default VARCHAR2(256)
);
ALTER TABLE MGMT_JOB_EXECPLAN MODIFY (
origin_step_type NUMBER(2),
step_type NUMBER(2)
);
UPDATE MGMT_JOB_EXECPLAN t
SET job_type_id=(SELECT job_type_id FROM MGMT_JOB_TYPE_INFO WHERE job_type=t.job_type);
ALTER TABLE MGMT_JOB_EXECPLAN DROP (job_type);
ALTER TABLE MGMT_JOB_EXECPLAN ADD (
CONSTRAINT PK_MGMT_JOB_EXECPLAN
PRIMARY KEY(job_type_id, step_name, step_type),
CONSTRAINT FK_MGMT_JOB_EXECPLAN FOREIGN KEY(job_type_id)
REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
);
-- MGMT_JOB_STEP_PARAMS
ALTER TABLE MGMT_JOB_STEP_PARAMS ADD (
job_type_id RAW(16),
value_of VARCHAR2(64) DEFAULT NULL
);
-- Set the job_type_id columns correctly
UPDATE MGMT_JOB_STEP_PARAMS t
SET job_type_id=(SELECT job_type_id FROM MGMT_JOB_TYPE_INFO WHERE job_type=t.job_type);
ALTER TABLE MGMT_JOB_STEP_PARAMS DROP (job_type);
ALTER TABLE MGMT_JOB_STEP_PARAMS MODIFY (
PARAM_NAME VARCHAR2(64)
);
ALTER TABLE MGMT_JOB_STEP_PARAMS ADD CONSTRAINT PK_MGMT_JOB_STEP_PARAMS
PRIMARY KEY(job_type_id, step_name, param_name)
USING INDEX TABLESPACE &EM_ECM_DEPOT_TABLESPACE;
-- MGMT_JOB_NESTED_JOB_TARGETS
ALTER TABLE MGMT_JOB_NESTED_JOB_TARGETS ADD (
job_type_id RAW(16)
);
-- MGMT_JOB_EXEC_EVENT_PARAMS
ALTER TABLE MGMT_JOB_EXEC_EVENT_PARAMS MODIFY (
PARAM_NAME VARCHAR2(64)
);
-- Set the job_type_id columns correctly
UPDATE MGMT_JOB_NESTED_JOB_TARGETS t
SET job_type_id=(SELECT job_type_id FROM MGMT_JOB_TYPE_INFO WHERE job_type=t.job_type);
ALTER TABLE MGMT_JOB_NESTED_JOB_TARGETS DROP (job_type);
-- Create the MGMT_JOB_NESTED_JOB_CRED_INFO table
CREATE TABLE MGMT_NESTED_JOB_CRED_INFO
(
job_type_id RAW(16),
nested_job_name VARCHAR2(64),
target_guid RAW(16),
target_type VARCHAR2(64),
container_location VARCHAR2(256),
credential_set_name VARCHAR2(32),
credential_guid RAW(16),
CONSTRAINT PK_MGMT_NESTED_JOB_CRED_INFO
PRIMARY KEY (job_type_id, nested_job_name, target_guid,
target_type, container_location, credential_set_name),
CONSTRAINT FK_MGMT_NESTED_JOB_CRED_INFO
FOREIGN KEY (job_type_id)
REFERENCES MGMT_JOB_TYPE_INFO (job_type_id)
ON DELETE CASCADE
)
MONITORING;
-- MGMT_JOB_PARAM_SOURCE
ALTER TABLE MGMT_JOB_PARAM_SOURCE ADD (
job_type_id RAW(16),
apply_on_retry NUMBER(1) DEFAULT 0
);
ALTER TABLE MGMT_JOB_PARAM_SOURCE MODIFY (
step_type NUMBER (2),
STEP_NAME VARCHAR2(64)
);
-- Set the job_type_id columns correctly
UPDATE MGMT_JOB_PARAM_SOURCE t
SET job_type_id=(SELECT job_type_id FROM MGMT_JOB_TYPE_INFO WHERE job_type=t.job_type);
ALTER TABLE MGMT_JOB_PARAM_SOURCE DROP (job_type);
-- MGMT_JOB_CRED_PARAMS
-- TBD These need to be computed
ALTER TABLE MGMT_JOB_CRED_PARAMS ADD (
base_cred_type_name VARCHAR2(256),
base_cred_type_target_type VARCHAR2(256),
base_cred_type_columns MGMT_JOB_VECTOR_PARAMS
);
ALTER TABLE MGMT_JOB_CRED_PARAMS MODIFY (
CONTAINER_PATHS_PARAM VARCHAR2(64),
TARGET_NAMES_PARAM VARCHAR2(64),
TARGET_TYPES_PARAM VARCHAR2(64)
);
-- MGMT_JOB_EXECPLAN
ALTER TABLE MGMT_JOB_EXECPLAN ADD (
FLATTENED_TARGETS NUMBER(1) DEFAULT 0
);
-- MGMT_JOB_SEC_INFO
ALTER TABLE MGMT_JOB_SEC_INFO ADD (
job_type_id RAW(16)
);
ALTER TABLE MGMT_JOB_SEC_INFO MODIFY (
TARGET_NAMES_PARAM VARCHAR2(64),
TARGET_TYPES_PARAM VARCHAR2(64)
);
-- Set the job_type_id columns correctly
UPDATE MGMT_JOB_SEC_INFO t
SET job_type_id=(SELECT job_type_id FROM MGMT_JOB_TYPE_INFO WHERE job_type=t.job_type);
ALTER TABLE MGMT_JOB_SEC_INFO DROP (job_type);
-- MGMT_JOB_LOCK_INFO
ALTER TABLE MGMT_JOB_LOCK_INFO ADD (
job_type_id RAW(16)
);
-- Set the job_type_id columns correctly
UPDATE MGMT_JOB_LOCK_INFO t
SET job_type_id=(SELECT job_type_id FROM MGMT_JOB_TYPE_INFO WHERE job_type=t.job_type);
ALTER TABLE MGMT_JOB_LOCK_INFO DROP (job_type);
-- Add the constraints back
ALTER TABLE MGMT_JOB_LOCK_INFO ADD
CONSTRAINT PK_MGMT_JOB_LOCK_INFO
PRIMARY KEY(lock_guid, job_type_id)
USING INDEX TABLESPACE &EM_ECM_DEPOT_TABLESPACE;
ALTER TABLE MGMT_JOB_LOCK_INFO ADD
CONSTRAINT FK_MGMT_JOB_LOCK_INFO FOREIGN KEY(job_type_id)
REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE;
-- MGMT_JOB_LOCK_TARGETS
ALTER TABLE MGMT_JOB_LOCK_TARGETS ADD (
job_type_id RAW(16)
);
-- Set the job_type_id columns correctly
UPDATE MGMT_JOB_LOCK_TARGETS t
SET job_type_id=(SELECT job_type_id FROM MGMT_JOB_TYPE_INFO WHERE job_type=t.job_type);
ALTER TABLE MGMT_JOB_LOCK_TARGETS DROP (job_type);
ALTER TABLE MGMT_JOB_LOCK_TARGETS ADD (
CONSTRAINT FK_JOB_LOCK_TARGETS FOREIGN KEY(lock_guid, job_type_id)
REFERENCES MGMT_JOB_LOCK_INFO(lock_guid, job_type_id)
ON DELETE CASCADE
);
-- MGMT_JOB_SCHEDULE
ALTER TABLE MGMT_JOB_SCHEDULE ADD (start_grace_period NUMBER DEFAULT -1);
-- MGMT_JOB
ALTER TABLE MGMT_JOB ADD (
job_type_major_version NUMBER DEFAULT 1,
nested_job_type_id RAW(16),
is_corrective_action NUMBER(1) DEFAULT 0,
restartable NUMBER(1) DEFAULT 1,
broken NUMBER(1) DEFAULT 0,
broken_reason NUMBER(2)
);
ALTER TABLE MGMT_JOB MODIFY (job_owner VARCHAR2(256));
-- Compute the right job type major version
UPDATE MGMT_JOB j
SET job_type_major_version=
(SELECT major_version FROM MGMT_JOB_TYPE_INFO WHERE job_type=j.job_type);
UPDATE MGMT_JOB j
SET is_corrective_action=0, restartable=1, broken=0;
-- Compute job type id for nested jobs
UPDATE MGMT_JOB j
SET nested_job_type_id=
(SELECT job_type_id FROM MGMT_JOB_TYPE_INFO WHERE job_type=j.job_type)
WHERE nested=1;
-- MGMT_CORRECTIVE_ACTION
CREATE TABLE MGMT_CORRECTIVE_ACTION (
job_id RAW(16) DEFAULT SYS_GUID(),
ca_scope NUMBER(1) DEFAULT 1,
ca_target_guid RAW(16),
ca_template_guid RAW(16),
ca_reference_count NUMBER,
CONSTRAINT "MGMT_CORRECTIVE_ACTION_PK" PRIMARY KEY("JOB_ID")
)
MONITORING;
-- MGMT_JOB_EXEC_SUMMARY
ALTER TABLE MGMT_JOB_EXEC_SUMMARY DISABLE ALL TRIGGERS;
-- TBD Schedule waiting executions for all recurring jobs, compute
-- expected_start_time
ALTER TABLE MGMT_JOB_EXEC_SUMMARY ADD (
job_type_id RAW(16),
status_code INTEGER,
status_code_category NUMBER(1) DEFAULT 1,
status_detail INTEGER DEFAULT 0,
expected_start_time DATE DEFAULT null,
triggering_severity RAW(16)
);
-- Compute the job type ids
UPDATE MGMT_JOB_EXEC_SUMMARY e
SET job_type_id=
(SELECT job_type_id FROM MGMT_JOB_TYPE_INFO ji, MGMT_JOB j
WHERE j.job_type=ji.job_type
AND j.job_id=e.job_id);
ALTER TABLE MGMT_JOB_EXEC_SUMMARY ENABLE ALL TRIGGERS;
-- MGMT_JOB_EXEC_LOCKS
ALTER TABLE MGMT_JOB_EXEC_LOCKS ADD (
job_type_id RAW(16)
);
-- Set the job_type_id columns correctly
UPDATE MGMT_JOB_EXEC_LOCKS t
SET job_type_id=(SELECT job_type_id FROM MGMT_JOB_TYPE_INFO WHERE job_type=t.job_type);
ALTER TABLE MGMT_JOB_EXEC_LOCKS DROP (job_type);
-- MGMT_JOB_HISTORY
ALTER TABLE MGMT_JOB_HISTORY MODIFY (step_type NUMBER(2));
ALTER TABLE MGMT_JOB_HISTORY ADD (
step_status_code_category NUMBER(1) DEFAULT 1,
oms_name VARCHAR2(256) DEFAULT NULL
);
-- MGMT_JOB_EXECUTION
ALTER TABLE MGMT_JOB_EXECUTION MODIFY (
step_type NUMBER(2),
ITERATE_PARAM VARCHAR2(64)
);
ALTER TABLE MGMT_JOB_EXECUTION ADD (
step_status_code_category NUMBER(1) DEFAULT 1,
oms_name VARCHAR2(256) DEFAULT NULL
);
-- MGMT_JOB_HISTORY
ALTER TABLE MGMT_JOB_HISTORY MODIFY (
ITERATE_PARAM VARCHAR2(64)
);
-- MGMT_JOB_LOCK_INFO
ALTER TABLE MGMT_JOB_LOCK_INFO MODIFY (
TARGET_NAMES_PARAM VARCHAR2(64),
TARGET_TYPES_PARAM VARCHAR2(64)
);
-- MGMT_JOB_PARAMETER
ALTER TABLE MGMT_JOB_PARAMETER MODIFY (
PARAMETER_NAME VARCHAR2(64)
);
ALTER TABLE MGMT_JOB_PARAMETER ADD (
CONSTRAINT PK_MGMT_JOB_PARAM
PRIMARY KEY(job_id, execution_id, parameter_name)
USING INDEX (CREATE UNIQUE INDEX PK_MGMT_JOB_PARAM
ON MGMT_JOB_PARAMETER (job_id, execution_id, parameter_name)
COMPRESS 2
TABLESPACE &EM_ECM_DEPOT_TABLESPACE)
);
-- MGMT_JOB_PROP_PARAMS
ALTER TABLE MGMT_JOB_PROP_PARAMS MODIFY (
TARGET_NAMES_PARAM VARCHAR2(64),
TARGET_TYPES_PARAM VARCHAR2(64)
);
-- MGMT_JOB_TYPE_URI_INFO
-- TBD change entries for SQLScript, OSCommand, etc?
ALTER TABLE MGMT_JOB_TYPE_URI_INFO ADD (
job_type_id RAW(16)
);
-- Set the job_type_id columns correctly
UPDATE MGMT_JOB_TYPE_URI_INFO t
SET job_type_id=(SELECT job_type_id FROM MGMT_JOB_TYPE_INFO WHERE job_type=t.job_type);
ALTER TABLE MGMT_JOB_TYPE_URI_INFO DROP (job_type);
ALTER TABLE MGMT_JOB_TYPE_URI_INFO ADD (
CONSTRAINT FK_JOB_TYPE_URI_INFO FOREIGN KEY(job_type_id)
REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
);
-- MGMT_JOB_TYPE_DISPLAY_PARAM
ALTER TABLE MGMT_JOB_TYPE_DISPLAY_PARAM ADD (
job_type_id RAW(16)
);
ALTER TABLE MGMT_JOB_TYPE_DISPLAY_PARAM MODIFY (
PARAM_NAME VARCHAR2(64)
);
-- Set the job_type_id columns correctly
UPDATE MGMT_JOB_TYPE_DISPLAY_PARAM t
SET job_type_id=(SELECT job_type_id FROM MGMT_JOB_TYPE_INFO WHERE job_type=t.job_type);
ALTER TABLE MGMT_JOB_TYPE_DISPLAY_PARAM DROP (job_type);
ALTER TABLE MGMT_JOB_TYPE_DISPLAY_PARAM ADD (
CONSTRAINT FK_JOB_TYPE_DISPLAY_PARAM FOREIGN KEY(job_type_id)
REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
);
-- MGMT_JOB_TYPE_DISPLAY_INFO
ALTER TABLE MGMT_JOB_TYPE_DISPLAY_INFO ADD (
job_type_id RAW(16),
use_default_create_ui number(1) DEFAULT 0
);
-- Set the job_type_id columns correctly
UPDATE MGMT_JOB_TYPE_DISPLAY_INFO t
SET job_type_id=(SELECT job_type_id FROM MGMT_JOB_TYPE_INFO WHERE job_type=t.job_type);
UPDATE MGMT_JOB_TYPE_DISPLAY_INFO
SET use_default_create_ui=0;
ALTER TABLE MGMT_JOB_TYPE_DISPLAY_INFO DROP (job_type);
ALTER TABLE MGMT_JOB_TYPE_DISPLAY_INFO ADD (
CONSTRAINT FK_JOB_TYPE_DISPLAY_INFO FOREIGN KEY(job_type_id)
REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
);
-- MGMT_JOB_CALLBACKS
ALTER TABLE MGMT_JOB_CALLBACKS ADD (
job_type_id RAW(16)
);
-- Set the job_type_id columns correctly
UPDATE MGMT_JOB_CALLBACKS t
SET job_type_id=(SELECT job_type_id FROM MGMT_JOB_TYPE_INFO WHERE job_type=t.job_type);
ALTER TABLE MGMT_JOB_CALLBACKS DROP (job_type);
ALTER TABLE MGMT_JOB_CALLBACKS ADD (
CONSTRAINT PK_MGMT_JOB_CALLBACKS
PRIMARY KEY(job_type_id, callback_type, callback_name),
CONSTRAINT FK_MGMT_JOB_CALLBACKS
FOREIGN KEY(job_type_id) REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
);
-- MGMT_JOB_QUEUES
ALTER TABLE MGMT_JOB_QUEUES MONITORING;
-- New tables
CREATE TABLE MGMT_JOB_STATE_CHANGES
(state_change_guid RAW(16) DEFAULT SYS_GUID(),
job_id RAW(16),
execution_id RAW(16),
step_id INTEGER,
logged TIMESTAMP DEFAULT SYSTIMESTAMP,
occurred DATE,
newstate NUMBER(2),
status_bucket NUMBER(2),
type NUMBER,
violation_guid RAW(16),
CONSTRAINT PK_MGMT_JOB_ST_CHGS PRIMARY KEY(state_change_guid),
CONSTRAINT FK_MGMT_JOB_ST_CHGS_1
FOREIGN KEY(execution_id) REFERENCES
MGMT_JOB_EXEC_SUMMARY(execution_id)
ON DELETE CASCADE
)
MONITORING;
CREATE TABLE MGMT_JOB_NOTIFY_STATES (
job_id RAW(16),
notify_state NUMBER(2),
CONSTRAINT PK_MGMT_JOB_NOTIF_STATE PRIMARY KEY(job_id, notify_state),
CONSTRAINT FK_MGMT_JOB_NOTIF_STATE_1
FOREIGN KEY(job_id) REFERENCES
MGMT_JOB(job_id) ON DELETE CASCADE
) MONITORING;
CREATE TABLE MGMT_JOB_TYPE_PARAM_URI_INFO
(
JOB_TYPE_ID RAW(16),
URI VARCHAR2(2000),
CLASS VARCHAR2(2000),
HELP_TOPIC VARCHAR2(2000),
SHOW_TARGET_PROPERTIES NUMBER(1) default 1 ,
CONSTRAINT PK_PARAM_URI_INFO PRIMARY KEY(job_type_id),
CONSTRAINT FK_PARAM_URI_INFO FOREIGN KEY(job_type_id)
REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
) MONITORING;
CREATE TABLE MGMT_JOB_TYPE_PARAM_DSPLY_INFO (
job_type_id RAW(16),
parameter_name VARCHAR2(64),
show_in_create NUMBER(1),
show_in_results NUMBER(1),
label_nlsid VARCHAR2(64),
label_default VARCHAR2(256),
hint_nlsid VARCHAR2(64),
hint_default VARCHaR2(256),
display_mode NUMBER(2),
num_lines NUMBER(2),
default_text VARCHAR2(4000),
default_nlsid VARCHAR2(64),
param_order NUMBER(3) NOT NULL ,
CONSTRAINT FK_JOB_TYPE_PARAM_INFO FOREIGN KEY(job_type_id)
REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
) MONITORING;
CREATE TABLE MGMT_JOB_TYPE_PARAM_DROPDOWNS (
job_type_id RAW(16),
parameter_name VARCHAR2(64),
is_default NUMBER(1),
option_value varchar2(32),
option_text_nlsid VARCHAR2(64),
option_text_default VARCHAR2(256),
param_order number(3) not null,
CONSTRAINT FK_JOB_TYPE_PARAM_DRPDWNS FOREIGN KEY(job_type_id)
REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
) MONITORING;
CREATE TABLE MGMT_JOB_DISPLAY_ERROR_CODES (
job_type_id RAW(16),
id VARCHAR2(64),
nlsid VARCHAR2(64),
default_value VARCHAR2(256),
CONSTRAINT PK_MGMT_JOB_DSPLY_ERR_CODES PRIMARY KEY(job_type_id, id),
CONSTRAINT FK_JOB_TYPE_DSPLY_ERR_CODES FOREIGN KEY(job_type_id)
REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
) MONITORING;
------------------------------------------------------------------------------------
---------------------------- INDEX UPGRADE ----------------------------------------
-----------------------------------------------------------------------------------
CREATE INDEX MGMT_JOB_IDX02 ON MGMT_JOB(job_type, job_type_major_version)
STORAGE (INITIAL 64K NEXT 64K);
CREATE INDEX MGMT_JOB_IDX03 ON MGMT_JOB(is_corrective_action, broken)
STORAGE (INITIAL 64K NEXT 64K);
CREATE INDEX MGMT_JOB_EXEC_SUMM_IDX05 ON
MGMT_JOB_EXEC_SUMMARY(status, status_bucket, scheduled_time)
STORAGE(INITIAL 64K NEXT 64K);
CREATE INDEX MGMT_JOB_PARAM_SOURCE_IDX01 ON
MGMT_JOB_PARAM_SOURCE(job_type_id);
CREATE INDEX MGMT_JOB_SEC_INFO_IDX01 ON
MGMT_JOB_SEC_INFO(job_type_id);
CREATE INDEX MGMT_JOB_LOCK_INFO_IDX01 ON
MGMT_JOB_LOCK_INFO(job_type_id);
CREATE INDEX MGMT_JT_URI_INFO_IDX01 ON
MGMT_JOB_TYPE_URI_INFO(job_type_id);
CREATE INDEX MGMT_JT_DISPLAY_PARAM_IDX01 ON
MGMT_JOB_TYPE_DISPLAY_PARAM(job_type_id);
CREATE INDEX MGMT_JT_DISPLAY_INFO_IDX01 ON
MGMT_JOB_TYPE_DISPLAY_INFO(job_type_id);
REM
REM Indexes for MGMT_JOB_STATE_CHANGES
REM
CREATE INDEX MGMT_JOB_STATE_CHGS_IDX01 ON MGMT_JOB_STATE_CHANGES(execution_id);
REM
REM Indexes for MGMT_CORRECTIVE_ACTION
REM
CREATE INDEX MGMT_CORRECTIVE_ACTION_IDX01 ON
MGMT_CORRECTIVE_ACTION(ca_target_guid);
-- Create the http session tables
CREATE TABLE MGMT_HTTP_SESSION_OBJECTS (
session_id VARCHAR2(256),
oms_failover_id NUMBER,
object_type VARCHAR2(32),
object_guid RAW(16),
CONSTRAINT PK_MGMT_HTTP_SESS_OBJS
PRIMARY KEY(session_id, object_type, object_guid)
);
CREATE TABLE MGMT_HTTP_SESSION_CALLBACKS (
object_type VARCHAR2(32),
callback_name VARCHAR2(64),
CONSTRAINT PK_MGMT_HTTP_SESS_CBS
PRIMARY KEY(object_type, callback_name)
);
rem
rem MGMT_JOB_ASSOC_PARAMS
rem
rem PURPOSE
rem The MGMT_JOB_ASSOC_PARAMS table stores the parameters
rem referenced in the ASSOC parameter sources in a job type.
rem
rem COLUMNS
rem
rem SOURCE_ID The unique id of this source
rem TARGET_NAMES_PARAM The name of the vector parameter which
rem will contain the associated target names.
rem TARGET_TYPES_PARAM The name of the vecotr parameter which
rem will contain the associated target types.
rem ASSOC_NAME The association that is used
rem SRC_TARGET_NAME The target which is the source for the association
rem SRC_TARGET_TYPE The target type of the source target
rem ASSOC_TARGET_TYPE The desired target type for associated targets
rem Note. If ASSOC_TARGET_TYPE is NULL it implies ALL targets
rem filter the associated targets
rem
CREATE TABLE MGMT_JOB_ASSOC_PARAMS (
source_id RAW(16),
target_names_param VARCHAR2(&JOB_PARAM_NAME_SIZE) NOT NULL,
target_types_param VARCHAR2(&JOB_PARAM_NAME_SIZE) NOT NULL,
assoc_name VARCHAR2(64) NOT NULL,
src_target_name VARCHAR2(&EM_TARGET_NAME_SIZE) NOT NULL,
src_target_type VARCHAR2(&EM_TARGET_TYPE_SIZE) NOT NULL,
assoc_target_type VARCHAR2(&EM_TARGET_TYPE_SIZE),
CONSTRAINT PK_ASSOC_SRC PRIMARY KEY(source_id),
CONSTRAINT FK_ASSOC_PARAM_SRC FOREIGN KEY(source_id)
REFERENCES MGMT_JOB_PARAM_SOURCE(source_id)
ON DELETE CASCADE
) TABLESPACE &EM_ECM_DEPOT_TABLESPACE
MONITORING;
rem
rem MGMT_NESTED_JOB_TARGETS
rem
rem PURPOSE
rem This table holds the targets for nested jobs and flatten steps.
rem
rem COLUMNS
rem JOB_TYPE_ID The job type id
rem STEP_NAME The name of the step
rem STEP_TYPE The type of the step
rem TARGET_NAME The target name
rem TARGET_TYPE The target type
rem
rem NOTES
rem
rem
DECLARE
t_cnt NUMBER := 0;
BEGIN
BEGIN
select count(*) into t_cnt
from ALL_OBJECTS
where OBJECT_NAME = 'MGMT_JOB_NESTED_JOB_TARGETS'
and OBJECT_TYPE = 'TABLE';
EXCEPTION WHEN OTHERS THEN NULL;
END;
IF (t_cnt = 0) THEN
EXECUTE IMMEDIATE
'CREATE TABLE MGMT_NESTED_JOB_TARGETS (
job_type_id RAW(16),
step_name VARCHAR2(&STEP_NAME_SIZE),
step_type NUMBER(2),
target_name VARCHAR2(&EM_TARGET_NAME_SIZE),
target_type VARCHAR2(&EM_TARGET_TYPE_SIZE)
)MONITORING';
ELSE
EXECUTE IMMEDIATE
'ALTER TABLE MGMT_JOB_NESTED_JOB_TARGETS RENAME TO MGMT_NESTED_JOB_TARGETS';
EXECUTE IMMEDIATE
'ALTER TABLE MGMT_NESTED_JOB_TARGETS RENAME COLUMN NESTED_JOB_NAME TO STEP_NAME';
EXECUTE IMMEDIATE
'ALTER TABLE MGMT_NESTED_JOB_TARGETS ADD (
step_type NUMBER(2)
)';
END IF;
END;
/
CREATE OR REPLACE TYPE MGMT_JOB_TARGET_TYPE AS OBJECT (
target_guid RAW(16)
);
/
CREATE OR REPLACE TYPE MGMT_JOB_GUID_LIST AS TABLE OF MGMT_JOB_TARGET_TYPE;
/
-- Upgrade for MGMT_JOB_SCHEDULE_RECORD. Do this at the end so as to
-- not invalidate any packages
CREATE OR REPLACE TYPE MGMT_JOB_SCHEDULE_RECORD AS OBJECT
(
-- The frequency code for the schedule.
frequency_code NUMBER,
-- The schedule start time. Use SYSDATE if the job is to start
-- immediately. For daily, days of week and days of month schedules,
-- the start_time denotes when the job should start. Only the time part
-- is used, and the date part is ignored.
start_time DATE,
-- For all periodic schedules, the last date (and time) to run the job.
-- For daily, day of week and day of month schedules, only the date
-- portion is used. For interval schedules, the time is taken into
-- account as well.
end_time DATE,
-- The grace period for starting the job specified in minutes.
-- If the job does not start between start_time and
-- start_time + start_grace_period it is skipped. The default of -1
-- indicates an infinite grace period.
start_grace_period NUMBER,
-- The "execution time" for daily, day of week/month/year schedules.
-- Indicates the time of the day at which the job will execute. Hours
-- are specified in 24-hour format (0 to 23), and minutes is a number
-- between 0 and 59.
execution_hours NUMBER,
execution_minutes NUMBER,
-- The interval, in minutes for interval jobs.
interval NUMBER,
-- For days-of-year schedules, this indicates the "month" portion of each
-- (month, day) pair in the schedule. The "days" portion is specified
-- using "days", below.
months MGMT_JOB_INT_ARRAY,
-- For day of week/month jobs, the day(s) of the week,
-- or month as the case may be.
-- For days of week, this is a set of numbers from 1-7 indicating the days
-- sunday through saturday, in that order.
-- For days of month, this is a set of numbers from 1-31 indicating the
-- date of the month. A special constant called LAST_DAY (-1)
-- can be use for day-of-month schedules to indicate the last
-- day of the month.
-- For days of the year, this is a set of numbers from 1-32
-- indicating the date of the month. A special constant called
-- LAST_DAY (-1) can be used for day-of-year schedules to indicate
-- the last day of the month. For day-of-year schedules, the
-- corresponding months are taken from the "months" field above
days MGMT_JOB_INT_ARRAY,
-- Specifies which timezone the start and end times are specified in
-- TIMEZONE_REPOSITORY: The timezone of the repository
-- TIMEZONE_TARGET: The timezone of one of the targets in the
-- target list, whose index in the target list is specified
-- by timezone_target_index
-- TIMEZONE_SPECIFIED: The timezone offset is specified in the
-- field timezone_offset
timezone_info NUMBER,
-- The index of the target in the target list(s) whose timezone the
-- times are specified in; only relevant when
-- timezone_info=TIMEZONE_TARGET
timezone_target_index NUMBER,
-- The actual timezone offset. Relevent only when
-- timezone_info=TIMEZONE_SPECIFIED
timezone_offset NUMBER,
-- The actual timezone region. Relevent only when
-- timezone_info=TIMEZONE_RGN_SPECIFIED
timezone_region VARCHAR2(64)
);
/
DROP TYPE JobRunObjTbl;
create or replace TYPE JobRunObjType as OBJECT
(job_id RAW(16)
,job_name VARCHAR2(64)
,job_owner VARCHAR2(256)
,job_type VARCHAR2(32)
,scheduled_time DATE
,frequency_code NUMBER(2)
,timezone_region VARCHAR2(64)
,timezone_info NUMBER(2)
,target_name VARCHAR2(256)
,target_type VARCHAR2(64)
,type_display_name VARCHAR2(128)
,target_count NUMBER(5)
,status_bucket NUMBER(2)
,status_bucket_count NUMBER(5)
,sample_exec_id RAW(16)
,job_type_category NUMBER(1)
);
/
create or replace TYPE JobRunObjTbl as TABLE of JobRunObjType;
/
DROP INDEX MGMT_JOB_EXEC_IDX04;
CREATE INDEX MGMT_JOB_EXEC_IDX04 ON
MGMT_JOB_EXECUTION(start_time, step_status, emd_url);