Rem drv: <migrate type="schema_upgrade" version="10.1.0.4"/>
Rem
Rem $Header: jobs_schema_upgrade.sql 18-jul-2005.11:00:22 chyu Exp $
Rem
Rem jobs_schema_upgrade.sql
Rem
Rem Copyright (c) 2004, 2005, 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    chyu        07/18/05 - adding the upgrade header 
Rem    skini       02/11/05 - Remove ROWNUM 
Rem    skini       02/11/05 - Do not minimize empty tables 
Rem    skini       12/23/04 - Qualify user 
Rem    skini       12/07/04 - Add job_id to exec_locks table
Rem    rzazueta    10/23/04 - rzazueta_backport_10.1.0.3.0_3647470
Rem    rzazueta    10/21/04 - Created
Rem

rem
rem MGMT_JOB_BLACKOUT_ASSOC
rem
rem PURPOSE
rem   The MGMT_JOB_BLACKOUT_ASSOC table holds the associations between
rem   jobs and blackouts (Ie. jobs that create blackouts)
rem
rem execution_id  The execution id which creates the blackout
rem blackout_guid The blackout guid
CREATE TABLE MGMT_JOB_BLACKOUT_ASSOC (
    execution_id RAW(16),
    blackout_guid RAW(16),
    CONSTRAINT PK_MGMT_JOB_BLACKOUT_ASSOC
        PRIMARY KEY(execution_id, blackout_guid),
    CONSTRAINT FK_MGMT_JOB_BLACKOUT_ASSOC
        FOREIGN KEY(execution_id) REFERENCES
            MGMT_JOB_EXEC_SUMMARY(execution_id)
                ON DELETE CASCADE
)
MONITORING;

-- Bug 3584073
ALTER TABLE MGMT_JOB_EXEC_LOCKS ADD (job_id RAW(16));

-- Bug 3863188
ALTER TABLE &EM_REPOS_USER..mgmt_job_execution PCTFREE 30;

DECLARE
l_num_rows NUMBER;
BEGIN
    SELECT  COUNT(step_id) INTO l_num_rows
    FROM    MGMT_JOB_EXECUTION;

    IF l_num_rows > 0 THEN
        EXECUTE IMMEDIATE 'ALTER TABLE &EM_REPOS_USER..mgmt_job_execution MINIMIZE records_per_block';
    END IF;
END;
/

-- ALTER TABLE &EM_REPOS_USER..mgmt_job_execution COMPRESS;
ALTER TABLE &EM_REPOS_USER..mgmt_job_execution MOVE;
ALTER INDEX MGMT_JOB_EXEC_IDX01 rebuild;
ALTER INDEX MGMT_JOB_EXEC_IDX02 rebuild;
ALTER INDEX MGMT_JOB_EXEC_IDX03 rebuild;
ALTER INDEX MGMT_JOB_EXEC_IDX04 rebuild;
ALTER INDEX PK_MGMT_JOB_EXECUTION rebuild; 

ALTER TABLE &EM_REPOS_USER..mgmt_job_history PCTFREE 25;

DECLARE
l_num_rows NUMBER;
BEGIN
    SELECT  COUNT(step_id) INTO l_num_rows
    FROM    MGMT_JOB_HISTORY;

    IF l_num_rows > 0 THEN
        EXECUTE IMMEDIATE 'ALTER TABLE &EM_REPOS_USER..mgmt_job_history MINIMIZE records_per_block';
    END IF;
END;
/

-- ALTER TABLE &EM_REPOS_USER..mgmt_job_history COMPRESS;
ALTER TABLE &EM_REPOS_USER..mgmt_job_history MOVE;
ALTER INDEX MGMT_JOB_HIST_IDX01 rebuild;
ALTER INDEX MGMT_JOB_HIST_IDX02 rebuild;
ALTER INDEX PK_MGMT_JOB_HISTORY rebuild;