Rem drv: <migrate type="schema_upgrade" version="10.2.0.3" />
Rem
Rem $Header: jobs_schema_upgrade.sql 23-jul-2007.01:57:05 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    07/23/07 - Backport lsatyapr_bug-6209668 from main
Rem    lsatyapr    07/18/07 - Bug6209668 - Safely create MGMT_JOB_EXT_TARGET_IDX02
Rem    nqureshi    06/14/07 - bug 6124782
Rem    amiagraw    12/06/06 - Backport amiagraw_bug-5682353 from main
Rem    kmanicka    08/29/06 - Backport kmanicka_bug-5484374 from main
Rem    amiagraw    12/04/06 - Bug fix 5682353: Added index
Rem                           MGMT_JOB_TARGET_IDX03
Rem    kmanicka    08/25/06 - bug 5484374 disable triger on MGMT_JOB_EXEC_SUMMARY 
Rem                           before adding column deleted_target_guid
Rem    kmanicka    07/13/06 - add deleted_target_guid to MGMT_JOB_EXEC_SUMMARY
Rem    kmanicka    08/01/06 - Backport kmanicka_bug-5223528 from main
Rem    kmanicka    08/01/06 - Backport kmanicka_bug5126621 from main
Rem    kmanicka    07/19/06 - Backport kmanicka_bug-4967469 from main
Rem    kmanicka    05/23/06 - make MGMT_JOB_EXEC_SUMMARY.JOB_ID AS "NOT NULL"
Rem    kmanicka    03/01/06 - add initrans 4 to mgmt_job_queues
Rem    nqureshi    01/18/06 - created

-- BUG bug 5484374 DISABLE trigger on MGMT_JOB_EXEC_SUMMARY
-- before adding column deleted_target_guid

BEGIN
    EXECUTE IMMEDIATE 'ALTER TRIGGER job_summ_ins_trigger DISABLE';
EXCEPTION
    WHEN OTHERS THEN
       -- While Upgrading from 10.1 job_summ_ins_trigger
       -- might not be defined
       IF SQLCODE = -04080 THEN
           NULL;
       ELSE
           RAISE;
       END IF;
END;
/

BEGIN
    EXECUTE IMMEDIATE 'ALTER TRIGGER job_summ_ins_trigger2 DISABLE';
EXCEPTION
    WHEN OTHERS THEN
       -- While Upgrading from 10.1 job_summ_ins_trigger2
       -- might not be defined
       IF SQLCODE = -04080 THEN
           NULL;
       ELSE
           RAISE;
       END IF;
END;
/

-- make MGMT_JOB_EXEC_SUMMARY.JOB_ID AS "NOT NULL"

ALTER TABLE MGMT_JOB_EXEC_SUMMARY MODIFY JOB_ID RAW(16) NOT NULL;

ALTER TABLE MGMT_JOB_EXEC_SUMMARY 
ADD (deleted_target_guid RAW(16) DEFAULT NULL);

-- BUG bug 5484374 ENABLE trigger on MGMT_JOB_EXEC_SUMMARY
-- after adding column deleted_target_guid

BEGIN
    EXECUTE IMMEDIATE 'ALTER TRIGGER job_summ_ins_trigger ENABLE';
EXCEPTION
    WHEN OTHERS THEN
       -- While Upgrading from 10.1 job_summ_ins_trigger
       -- might not be defined
       IF SQLCODE = -04080 THEN
           NULL;
       ELSE
           RAISE;
       END IF;
END;
/

BEGIN
    EXECUTE IMMEDIATE 'ALTER TRIGGER job_summ_ins_trigger2 ENABLE';
EXCEPTION
    WHEN OTHERS THEN
       -- While Upgrading from 10.1 job_summ_ins_trigger2
       -- might not be defined
       IF SQLCODE = -04080 THEN
           NULL;
       ELSE
           RAISE;
       END IF;
END;
/

CREATE INDEX MGMT_JOB_EXEC_SUMM_IDX06 ON
   MGMT_JOB_EXEC_SUMMARY(deleted_target_guid)
   STORAGE(INITIAL 64K NEXT 64K);


alter table MGMT_JOB_TYPE_PARAM_DSPLY_INFO add  CONSTRAINT PK_JOB_TYPE_PARAM_DSPLY_INFO PRIMARY KEY(job_type_id,parameter_name);

-- add initrans 4 to reduce block level contention
ALTER TABLE MGMT_JOB_QUEUES INITRANS 4;

-- rebuild table so that initrans param is updated for older blocks
ALTER TABLE MGMT_JOB_QUEUES MOVE TABLESPACE &EM_TABLESPACE_NAME;

-- rebuild Primary key
ALTER INDEX PK_JOB_QUEUE REBUILD INITRANS 4;

-- rebuild index with new initrans
DROP INDEX MGMT_JOB_QUEUE_IDX01;

CREATE UNIQUE INDEX MGMT_JOB_QUEUE_IDX01 ON
    MGMT_JOB_QUEUES(queue_name)
    INITRANS 4;

-- Ignore errors if the following index already exists (possible due to one-offs)
BEGIN
    EXECUTE IMMEDIATE 'CREATE INDEX MGMT_JOB_EXT_TARGET_IDX02 ON MGMT_JOB_EXT_TARGETS(execution_id,target_guid,job_id)';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -1408 THEN
            RAISE;
        END IF;
END;
/

CREATE INDEX MGMT_JOB_TARGET_IDX03 ON MGMT_JOB_TARGET
    (execution_ID);

ALTER TYPE MGMT_JOB_STEP_RECORD ADD ATTRIBUTE
(job_params MGMT_JOB_PARAM_LIST) CASCADE ;