Rem
Rem $Header: jobs_tables_schema_upgrade.sql 13-jun-2003.15:34:55 skini Exp $
Rem
Rem jobs_tables_schema_upgrade.sql
Rem
Rem Copyright (c) 2003, Oracle Corporation.  All rights reserved.  
Rem
Rem    NAME
Rem      jobs_tables_schema_upgrade.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      Upgrade script for jobs in EM 4.0.1
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED 
Rem       skini      06/13/03 - Index upgrade
Rem       skini      06/06/03 - skini_bug-2962805
Rem

Rem Change the constraint in the mgmt_job_exec_event_params table
Rem to include param value
Rem
ALTER TABLE MGMT_JOB_EXEC_EVENT_PARAMS DROP CONSTRAINT PK_MGMT_JOB_EXEC_EVPARAMS;

ALTER TABLE MGMT_JOB_EXEC_EVENT_PARAMS ADD CONSTRAINT PK_MGMT_JOB_EXEC_EVPARAMS PRIMARY KEY(execution_id, param_name, param_value);

Rem New indexes in 4.0.1
DROP INDEX MGMT_JOB_IDX02;

CREATE INDEX MGMT_JOB_IDX02 ON MGMT_JOB(job_type)
       STORAGE (INITIAL 64K NEXT 64K);

CREATE INDEX MGMT_JOB_IDX03 ON MGMT_JOB(system_job)
       STORAGE (INITIAL 64K NEXT 64K);

CREATE INDEX MGMT_JOB_EXEC_SUMM_IDX03 ON
   MGMT_JOB_EXEC_SUMMARY(job_id)
   STORAGE(INITIAL 64K NEXT 64K);

Rem
Rem Indexes for the parameter source tables
Rem
CREATE INDEX MGMT_JOB_PARAM_SOURCE_IDX01 ON
    MGMT_JOB_PARAM_SOURCE(job_type);

CREATE INDEX MGMT_JOB_USER_PARAMS_IDX01 ON
    MGMT_JOB_USER_PARAMS(source_id);

CREATE INDEX MGMT_JOB_SQL_PARAMS_IDX01 ON
    MGMT_JOB_SQL_PARAMS(source_id);

CREATE INDEX MGMT_JOB_CRED_PARAMS_IDX01 ON
    MGMT_JOB_CRED_PARAMS(source_id);

CREATE INDEX MGMT_JOB_PROP_PARAMS_IDX01 ON
    MGMT_JOB_PROP_PARAMS(source_id);

CREATE INDEX MGMT_JOB_VALUE_PARAMS_IDX01 ON
    MGMT_JOB_VALUE_PARAMS(source_id);

Rem
Rem Index for the security info and lock info tables
Rem
CREATE INDEX MGMT_JOB_SEC_INFO_IDX01 ON
    MGMT_JOB_SEC_INFO(job_type);

CREATE INDEX MGMT_JOB_LOCK_INFO_IDX01 ON
    MGMT_JOB_LOCK_INFO(job_type);

Rem
Rem Indexes for MGMT_JOB_EXEC_LOCKS
Rem
CREATE INDEX MGMT_JOB_EXEC_LOCKS_IDX01 ON MGMT_JOB_EXEC_LOCKS(execution_id);


REM
REM Add monitoring clauses to job system tables
REM
ALTER TABLE MGMT_JOB_COMMAND MONITORING;
ALTER TABLE MGMT_JOB_TYPE_INFO MONITORING;
ALTER TABLE MGMT_JOB_EXECPLAN MONITORING;
ALTER TABLE MGMT_JOB_STEP_PARAMS MONITORING;
ALTER TABLE MGMT_JOB_NESTED_JOB_TARGETS MONITORING;
ALTER TABLE MGMT_JOB_PARAM_SOURCE MONITORING;
ALTER TABLE MGMT_JOB_USER_PARAMS MONITORING;
ALTER TABLE MGMT_JOB_SQL_PARAMS MONITORING;
ALTER TABLE MGMT_JOB_CRED_PARAMS MONITORING;
ALTER TABLE MGMT_JOB_PROP_PARAMS MONITORING;
ALTER TABLE MGMT_JOB_VALUE_PARAMS MONITORING;
ALTER TABLE MGMT_JOB_SEC_INFO MONITORING;
ALTER TABLE MGMT_JOB_LOCK_INFO MONITORING;
ALTER TABLE MGMT_JOB_LOCK_TARGETS MONITORING;
ALTER TABLE MGMT_JOB_SCHEDULE MONITORING;
ALTER TABLE MGMT_JOB MONITORING;
ALTER TABLE MGMT_JOB_TARGET MONITORING;
ALTER TABLE MGMT_JOB_EXT_TARGETS MONITORING;
ALTER TABLE MGMT_JOB_OUTPUT MONITORING;
ALTER TABLE MGMT_JOB_EXEC_SUMMARY MONITORING;
ALTER TABLE MGMT_JOB_EXEC_EVENT_PARAMS MONITORING;
ALTER TABLE MGMT_JOB_EXEC_LOCKS MONITORING;
ALTER TABLE MGMT_JOB_PARAMETER MONITORING;
ALTER TABLE MGMT_JOB_HISTORY MONITORING;
ALTER TABLE MGMT_JOB_EXECUTION MONITORING;
ALTER TABLE MGMT_JOB_STEP_COMMAND_LOG MONITORING;
ALTER TABLE MGMT_JOB_EMD_STATUS_QUEUE MONITORING;
ALTER TABLE MGMT_JOB_PURGE_POLICIES MONITORING;
ALTER TABLE MGMT_JOB_PURGE_CRITERIA MONITORING;
ALTER TABLE MGMT_JOB_PURGE_TARGETS MONITORING;
ALTER TABLE MGMT_JOB_PURGE_VALUES MONITORING;
ALTER TABLE MGMT_JOB_EVENT MONITORING;


Rem
Rem New constraints
Rem
ALTER TABLE MGMT_JOB_EXECPLAN ADD CONSTRAINT PK_MGMT_JOB_EXECPLAN 
        PRIMARY KEY(job_type, step_name, step_type);

ALTER TABLE MGMT_JOB_STEP_PARAMS ADD CONSTRAINT PK_MGMT_JOB_STEP_PARAMS
        PRIMARY KEY(job_type, step_name, param_name);

Rem
Rem Drop old unique key for the history table, replace
Rem with uique constraint
Rem 
DROP INDEX MGMT_JOB_HIST_IDX01;
ALTER TABLE MGMT_JOB_HISTORY ADD CONSTRAINT PK_MGMT_JOB_HISTORY 
   PRIMARY KEY(step_id);
Rem
Rem Drop old unique key and indexes for consistency
Rem
DROP INDEX MGMT_JOB_EXEC_IDX01;
DROP INDEX MGMT_JOB_EXEC_IDX02;
DROP INDEX MGMT_JOB_EXEC_IDX03;
DROP INDEX MGMT_JOB_EXEC_IDX04;
ALTER TABLE MGMT_JOB_EXECUTION ADD CONSTRAINT PK_MGMT_JOB_EXECUTION 
   PRIMARY KEY(step_id);

Rem Recreate indexes for the execution table
CREATE INDEX MGMT_JOB_EXEC_IDX01 ON
  MGMT_JOB_EXECUTION(step_type, step_status, start_time)
  STORAGE(INITIAL 64K NEXT 64K); 

CREATE INDEX MGMT_JOB_EXEC_IDX02 ON
   MGMT_JOB_EXECUTION(execution_id)
   STORAGE(INITIAL 64K NEXT 64K);

CREATE INDEX MGMT_JOB_EXEC_IDX03 ON
   MGMT_JOB_EXECUTION(parent_step_id)
   STORAGE(INITIAL 64K NEXT 64K); 

ALTER TABLE MGMT_JOB_STEP_COMMAND_LOG ADD CONSTRAINT PK_STEP_COMMAND_LOG
        PRIMARY KEY(command_block_id);