Rem drv:
Rem
Rem $Header: jobs_schema_upgrade.sql 06-aug-2007.02:38:24 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 -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem lsatyapr 08/03/07 - Drop index while dropping constraint
Rem lsatyapr 08/06/07 - Backport lsatyapr_bug-6316745 from main
Rem dsahrawa 12/12/05 - Backport dsahrawa_bug-4651110 from main
Rem dsahrawa 12/06/05 - fix bug 4651110, add index on tname, type on
Rem mgmt_nested_job_cred_info after the table has
Rem been altered
Rem kmanicka 12/08/05 - Backport kmanicka_bug-4617892 from main
Rem kmanicka 12/04/05 - add initrans 8 to mgmt_job_execution
Rem skini 09/08/05 - Add index on exec_cred_info
Rem skini 08/31/05 - Fix broken creds upgrade
Rem skini 08/31/05 - Changes to nested_job_cred_info table
Rem skini 08/30/05 - Add index to state changes table
Rem skini 08/29/05 - Fix mgmt_job_exec_idx01
Rem skini 08/28/05 - Broken creds
Rem pshishir 08/23/05 - Moving INDEX on MGMT_CA from v102020 to v102010
Rem pkantawa 08/23/05 - Fix beta->prod upgrade issues
Rem pkantawa 08/05/05 - Fix 4432894: modify target type column length
Rem dsahrawa 07/25/05 - changes to mgmt_job_step_params
Rem chyu 07/25/05 - modifying the upgrade header
Rem chyu 07/18/05 - adding the upgrade
Rem header
Rem skini 07/17/05 - Add async_error_received
Rem skini 07/12/05 - skini_gcbugs
Rem skini 07/11/05 - Add command block table
Rem skini 07/11/05 - Created
Rem
DEFINE EM_TABLESPACE_NAME = "&1"
DEFINE JOB_PARAM_NAME_SIZE=64
--Upgrade job types
DROP TYPE MGMT_PARAM_CRED_TGT_MET_ARR;
DROP TYPE MGMT_PARAM_CRED_TGT_MET;
DROP TYPE MGMT_JOB_PARAMETER_METADATA;
DROP TYPE MGMT_JOB_PRMTER_URI_INF_RECORD;
DROP TYPE MGMT_JOB_TASK_CRED_SET_ARRAY;
DROP TYPE MGMT_JOB_TGT_CRED_SET_ARRAY;
DROP TYPE MGMT_JOB_TGT_CRED_SET_RECORD;
DROP TYPE MGMT_TARGET_FIXCA_ARRAY;
DROP TYPE MGMT_TARGET_FIXCA_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(2000),
online_task_help_topic VARCHAR2(2000),
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 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;
/
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,
task_name VARCHAR2(64),
cred_set_nlsid VARCHAR2(64),
cred_set_disp_name VARCHAR2(64)
);
/
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 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 RAW(16),
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 represents and array of target properties
CREATE OR REPLACE TYPE MGMT_JOB_SHORT_STR_ARR_TABLE AS TABLE OF MGMT_SHORT_STRING_ARRAY ;
/
REM represents and array of Task/job Targets List
CREATE OR REPLACE TYPE MGMT_JOB_TARGET_LIST_TABLE AS TABLE OF MGMT_JOB_TARGET_LIST ;
/
REM represents an RECORD of targets Display Name and target property
CREATE OR REPLACE TYPE MGMT_JOB_DNAME_TYPE_PROP_REC AS OBJECT
(
target_display_name VARCHAR2(256),
target_name VARCHAR2(64),
target_type VARCHAR2(64),
propName VARCHAR2(64)
);
/
REM represents an ARRAY of targets Display Names and target properties
CREATE OR REPLACE TYPE MGMT_JOB_DNAME_TYPE_PROP_TABLE AS TABLE OF MGMT_JOB_DNAME_TYPE_PROP_REC;
/
--Drop indexes prior to schema upgrade
DROP INDEX MGMT_JOB_EXEC_SUMM_IDX05;
DROP INDEX MGMT_JOB_IDX03;
rem
rem MGMT_JOB_COMMAND_BLOCK_PROCS
rem PURPOSE
rem This table holds all pre-registered procedures that can
rem be called from command blocks.
rem COLUMNS
rem proc_name The name of the procedure
rem param_types The (formal) parameter types for the procedure
rem
CREATE TABLE MGMT_JOB_COMMAND_BLOCK_PROCS (
proc_name VARCHAR2(256),
param_types MGMT_SHORT_STRING_ARRAY,
CONSTRAINT PK_COMMAND_BLOCK_PROCS
PRIMARY KEY(proc_name)
)
MONITORING;
-- Add async_error_received column
-- add initrans 8 to reduce block level contention
ALTER TABLE MGMT_JOB_EXECUTION
ADD (async_error_received NUMBER(1) DEFAULT 0)
INITRANS 8;
-- rebuild table so that initrans param is updated for older blocks
ALTER TABLE MGMT_JOB_EXECUTION MOVE TABLESPACE &EM_TABLESPACE_NAME;
-- rebuild Primary key
ALTER INDEX PK_MGMT_JOB_EXECUTION REBUILD INITRANS 8;
-- rebuild index with new initrans
DROP INDEX MGMT_JOB_EXEC_IDX01;
CREATE INDEX MGMT_JOB_EXEC_IDX01 ON
MGMT_JOB_EXECUTION(step_type, command_type, step_status, start_time)
STORAGE(INITIAL 64K NEXT 64K)
INITRANS 8;
DROP INDEX MGMT_JOB_EXEC_IDX02;
CREATE INDEX MGMT_JOB_EXEC_IDX02 ON
MGMT_JOB_EXECUTION(execution_id)
STORAGE(INITIAL 64K NEXT 64K)
INITRANS 8;
DROP INDEX MGMT_JOB_EXEC_IDX03;
CREATE INDEX MGMT_JOB_EXEC_IDX03 ON
MGMT_JOB_EXECUTION(parent_step_id)
STORAGE(INITIAL 64K NEXT 64K)
INITRANS 8;
DROP INDEX MGMT_JOB_EXEC_IDX04;
CREATE INDEX MGMT_JOB_EXEC_IDX04 ON
MGMT_JOB_EXECUTION(start_time, step_status, emd_url)
STORAGE(INITIAL 64K NEXT 64K)
INITRANS 8;
-- add initrans 8 to reduce block level contention
ALTER TABLE MGMT_JOB_HISTORY INITRANS 8;
-- rebuild table so that initrans param is updated for older blocks
ALTER TABLE MGMT_JOB_HISTORY MOVE TABLESPACE &EM_TABLESPACE_NAME;
-- rebuild Primary key
ALTER INDEX PK_MGMT_JOB_HISTORY REBUILD INITRANS 8;
-- rebuild index with new initrans
DROP INDEX MGMT_JOB_HIST_IDX01;
CREATE INDEX MGMT_JOB_HIST_IDX01 ON
MGMT_JOB_HISTORY(execution_id)
STORAGE(INITIAL 64K NEXT 64K)
INITRANS 8;
DROP INDEX MGMT_JOB_HIST_IDX02;
CREATE INDEX MGMT_JOB_HIST_IDX02 ON
MGMT_JOB_HISTORY(parent_step_id)
STORAGE(INITIAL 64K NEXT 64K)
INITRANS 8;
------------------------------------------------------------------------------------
---------------------------- INDEX UPGRADE ----------------------------------------
-----------------------------------------------------------------------------------
CREATE INDEX MGMT_JOB_CREDENTIALS_IDX01 ON
MGMT_JOB_CREDENTIALS(target_guid);
CREATE INDEX MGMT_JOB_EXEC_LOCKS_IDX02 ON
MGMT_JOB_EXEC_LOCKS(target_guid);
CREATE INDEX MGMT_JOB_EXEC_SUMM_IDX05 ON
MGMT_JOB_EXEC_SUMMARY(status, status_bucket, scheduled_time)
COMPRESS 2;
CREATE INDEX MGMT_JOB_IDX03 ON
MGMT_JOB(is_corrective_action, broken)
COMPRESS 2;
CREATE INDEX MGMT_JOB_STEP_TARGETS_IDX02 ON
MGMT_JOB_STEP_TARGETS(target_guid);
REM
REM Indexes for MGMT_CREDENTIALS2
REM
CREATE INDEX MGMT_CREDENTIALS2_IDX01 ON
MGMT_CREDENTIALS2(assoc_target_guid);
REM changes to MGMT_JOB_STEP_PARAMS to support
REM large parameters in multi task jobs
REM rename is_scalar to parameter_type
ALTER TABLE MGMT_JOB_STEP_PARAMS RENAME COLUMN IS_SCALAR TO PARAMETER_TYPE;
ALTER TABLE MGMT_JOB_STEP_PARAMS
ADD(large_value RAW(16) DEFAULT NULL);
REM
REM MGMT_JOB_LOCK_TARGETS
ALTER TABLE MGMT_JOB_LOCK_TARGETS MODIFY (
target_type VARCHAR2(64)
);
REM
REM MGMT_JOB_TYPE_PARAM_URI_INFO
ALTER TABLE MGMT_JOB_TYPE_PARAM_URI_INFO ADD (
TASK_HELP_TOPIC VARCHAR2(2000)
);
REM
REM This change is required since upgrade script in beta branch uses wrong
REM column width.
REM MGMT_JOB_ASSOC_PARAMS
ALTER TABLE MGMT_JOB_ASSOC_PARAMS MODIFY (
target_names_param VARCHAR2(&JOB_PARAM_NAME_SIZE)
);
REM
REM MGMT_JOB_EXEC_CRED_INFO
REM
REM PURPOSE
REM Holds information about credential requirements for each
REM execution of a job. Specifies whether or not that
REM an execution needs a specific set of credentials
REM
REM For TOP LEVEL jobs, information will be set at the level of
REM (job_id=>parent job id, execution_id, target_guid)
REM For NESTED jobs, we use
REM (job_id=>nested job id, execution_id, target_guid)
REM For MULTITASK jobs of all flavors, we use
REM (job_id=>parent job id, execution_id, task name, target_guid)
REM COLUMNS
REM
REM job_id The job id (nested job id for nested jobs)
REM execution_id The execution id
REM task_name The task name, for multitask jobs only
REM target_guid The target guid
REM credential_set_name The credential set name for that target
REM credentials_set Set to 1 if this execution has credentials for
REM the specific target and credential set, 0 if not.
REM This flag is required since a job may have overridden
REM credentials set for a target
REM
CREATE TABLE MGMT_JOB_EXEC_CRED_INFO (
job_id RAW(16),
execution_id RAW(16),
task_name VARCHAR2(64) DEFAULT '<0>',
target_guid RAW(16),
container_location VARCHAR2(256),
credential_set_name VARCHAR2(32),
credentials_set NUMBER(1),
CONSTRAINT PK_JOB_EXEC_CRED_INFO
PRIMARY KEY(job_id, execution_id, task_name, target_guid,
container_location, credential_set_name)
)
MONITORING;
ALTER TABLE MGMT_JOB_TYPE_INFO add (suspend_on_nocreds NUMBER(1) DEFAULT 0);
UPDATE MGMT_JOB_TYPE_INFO SET suspend_on_nocreds=single_target;
DROP INDEX MGMT_JOB_EXEC_IDX01;
CREATE INDEX MGMT_JOB_EXEC_IDX01 ON
MGMT_JOB_EXECUTION(step_type, command_type, step_status, start_time)
STORAGE(INITIAL 64K NEXT 64K);
CREATE INDEX MGMT_JOB_STATE_CHGS_IDX02 ON MGMT_JOB_STATE_CHANGES(logged);
REM Changes to MGMT_NESTED_JOB_CRED_INFO
ALTER TABLE MGMT_NESTED_JOB_CRED_INFO DROP CONSTRAINT PK_MGMT_NESTED_JOB_CRED_INFO CASCADE DROP INDEX;
ALTER TABLE MGMT_NESTED_JOB_CRED_INFO DROP CONSTRAINT FK_MGMT_NESTED_JOB_CRED_INFO CASCADE DROP INDEX;
ALTER TABLE MGMT_NESTED_JOB_CRED_INFO ADD (
target_name VARCHAR2(&EM_TARGET_NAME_SIZE)
);
DECLARE
l_target_name MGMT_TARGETS.target_name%TYPE;
l_target_type MGMT_TARGETS.target_type%TYPE;
BEGIN
FOR crec IN (SELECT credential_guid, target_guid
FROM MGMT_NESTED_JOB_CRED_INFO) LOOP
BEGIN
SELECT target_name, target_type
INTO l_target_name, l_target_type
FROM MGMT_NESTED_JOB_CRED_INFO
WHERE target_guid=crec.target_guid;
UPDATE MGMT_NESTED_JOB_CRED_INFO
SET target_name=l_target_name,
target_type=l_target_type
WHERE credential_guid=crec.credential_guid;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
ALTER TABLE MGMT_NESTED_JOB_CRED_INFO DROP (target_guid);
ALTER TABLE MGMT_NESTED_JOB_CRED_INFO ADD (
CONSTRAINT PK_MGMT_NESTED_JOB_CRED_INFO
PRIMARY KEY (job_type_id, nested_job_name, target_name,
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
);
REM using IDX1 instead of IDX01 due to name length constraint
CREATE INDEX MGMT_NESTED_JOB_CRED_INFO_IDX1 ON
MGMT_NESTED_JOB_CRED_INFO(target_name, target_type);
CREATE INDEX MGMT_JOB_EXEC_CRED_INFO_IDX01 ON
MGMT_JOB_EXEC_CRED_INFO(target_guid);