Rem
Rem $Header: jobs_recovery_pkgbody.sql 17-sep-2007.02:50:34 shnavane Exp $
Rem
Rem jobs_recovery_pkgbody.sql
Rem
Rem Copyright (c) 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      jobs_recovery_pkgbody.sql - utility recovery routines
Rem
Rem    DESCRIPTION
Rem      utility recovery routines
Rem
Rem    NOTES
Rem      These routines should not be called directly
Rem      They are for recovery situations or upgrade cases
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    shnavane    09/18/07 - Backport shnavane_bug-6391386 from main
Rem    rdabbott    08/23/07 - delete_orphans -> delete_orphan_schedules
Rem    rdabbott    08/22/07 - prefix EM_ instead of MGMT_
Rem    rdabbott    08/17/07 - fix 6327699: utility recovery routines
Rem    rdabbott    08/17/07 - Created
Rem

-------------------------------------------------
CREATE OR REPLACE PACKAGE BODY EM_JOB_RECOVERY AS
-------------------------------------------------

MODULE_NAME CONSTANT VARCHAR2(11) := MGMT_JOB_ENGINE.MODULE_NAME;

TYPE arr_lrg_type IS TABLE OF mgmt_job_large_params.param_id%TYPE;
TYPE arr_cnt_type IS TABLE OF mgmt_job_large_params.reference_count%TYPE;


FUNCTION get_commit_frequency(p_str IN VARCHAR2, 
                              p_default IN INTEGER:= 50000) RETURN INTEGER IS 
l_freq INTEGER := p_default;
BEGIN
  SELECT parameter_value
  INTO l_freq
  FROM mgmt_parameters 
  WHERE parameter_name = p_str;
  RETURN l_freq;

EXCEPTION
  WHEN others THEN
     RETURN l_freq;
END;


PROCEDURE delete_orphan_schedules IS
l_cnt         NUMBER;
l_commit_freq INTEGER;

BEGIN
    l_commit_freq := get_commit_frequency('job_schedule_upgrade_batchsize');

    LOOP
        DELETE
        FROM   mgmt_job_schedule s
        WHERE  NOT EXISTS
               (SELECT 1
                FROM   mgmt_job j
                WHERE  j.schedule_id = s.schedule_id)
        AND    ROWNUM < l_commit_freq;

        l_cnt := SQL%ROWCOUNT;

        COMMIT;
        EXIT WHEN l_cnt = 0;
    END LOOP;
END;

PROCEDURE add_missing_large_params IS
l_commit_freq INTEGER;

    -- Cursor to return all the orphaned entries in mgmt_job_parameter and mgmt_job_step_params
    -- UNION operator removes duplicate records
    -- We use this cursor to insert place holder empty_clob() records into mgmt_job_large_params for the
    -- orphaned entries in mgmt_job_parameter and mgmt_job_step_params
    CURSOR get_missing_large_params IS
      (SELECT large_value
       FROM mgmt_job_parameter p
       WHERE p.parameter_type = 2
       AND NOT EXISTS
        (SELECT 1
         FROM mgmt_job_large_params l
         WHERE p.large_value = l.param_id)
      UNION
       SELECT large_value
       FROM mgmt_job_step_params p
       WHERE p.parameter_type = 2
       AND NOT EXISTS
        (SELECT 1
         FROM mgmt_job_large_params l
         WHERE p.large_value = l.param_id)
      )
    ;
    l_arr_large_values arr_lrg_type;

BEGIN
    l_commit_freq := get_commit_frequency('job_add_missing_lp_batchsize');

    -- Insert empty_clob() for orphaned entries in mgmt_job_parameter and mgmt_job_step_params
    OPEN get_missing_large_params;
    LOOP
        FETCH get_missing_large_params bulk collect
        INTO l_arr_large_values limit l_commit_freq;
        EXIT WHEN l_arr_large_values.COUNT = 0;
        FORALL j IN l_arr_large_values.FIRST .. l_arr_large_values.LAST
             INSERT
             INTO mgmt_job_large_params(param_id,     param_value)
             VALUES(l_arr_large_values(j),     empty_clob());
        COMMIT;
        IF EMDW_LOG.p_is_info_set THEN
            EMDW_LOG.info('add_missing_large_params() added '
                        || l_arr_large_values.COUNT
                        || ' dummy large params '
                       ,MODULE_NAME);
        END IF;
    END LOOP;
    CLOSE get_missing_large_params;
END;

PROCEDURE fix_large_params_ref_count IS
l_commit_freq INTEGER;
l_count INTEGER;
l_dummy INTEGER;

CURSOR get_large_params IS
       SELECT param_id FROM mgmt_job_large_params;

l_arr_large_values arr_lrg_type;
l_updated_count INTEGER := 0;

BEGIN
  EM_JOB_RECOVERY.add_missing_large_params;
  l_commit_freq := get_commit_frequency('job_fix_lp_refcount_batchsize',50);
  OPEN get_large_params;
  LOOP
    FETCH get_large_params bulk collect INTO l_arr_large_values limit l_commit_freq;
    EXIT WHEN l_arr_large_values.COUNT = 0;
    FOR j IN l_arr_large_values.FIRST .. l_arr_large_values.LAST
    LOOP
        BEGIN
          -- Lock the record for update
          SELECT 1 into l_dummy FROM mgmt_job_large_params WHERE param_id = l_arr_large_values(j)
          FOR UPDATE;
      
          SELECT COUNT(1) AS
          cnt
          INTO l_count
          FROM
            (SELECT large_value
             FROM mgmt_job_parameter
             WHERE parameter_type = 2
             AND large_value = l_arr_large_values(j)
             UNION ALL
             SELECT large_value
             FROM mgmt_job_step_params
             WHERE parameter_type = 2
             AND large_value = l_arr_large_values(j))
          GROUP BY large_value;
      
          UPDATE mgmt_job_large_params
          SET reference_count = l_count
          WHERE param_id = l_arr_large_values(j)
           AND reference_count != l_count;
          l_updated_count := l_updated_count + SQL%ROWCOUNT; 

        EXCEPTION
        WHEN no_data_found THEN
          NULL;
        END;
    END LOOP;
    COMMIT;
    IF EMDW_LOG.p_is_info_set THEN
          EMDW_LOG.info('fix_large_params_ref_count() updated '
                          || l_updated_count
                          || ' large param reference counts '
                          ,MODULE_NAME);
    END IF;
    l_updated_count := 0;
  END LOOP;
  CLOSE get_large_params;
END;

PROCEDURE delete_orphan_large_params IS
l_commit_freq INTEGER;
    -- Cursor that returns orphaned entries in mgmt_job_large_params
    CURSOR get_orphaned_large_params IS
    (
      SELECT l.param_id FROM mgmt_job_large_params l
      WHERE(NOT EXISTS
        (SELECT 1
         FROM mgmt_job_parameter p
         WHERE p.large_value = l.param_id
         AND p.parameter_type = 2))
      AND(NOT EXISTS
        (SELECT 1
         FROM mgmt_job_step_params p
         WHERE p.large_value = l.param_id
         AND p.parameter_type = 2))
     );

    l_arr_large_values arr_lrg_type;

BEGIN
    l_commit_freq := get_commit_frequency('job_delete_orphan_lp_batchsize');

    -- Delete Orphan entries from mgmt_job_large_params
    OPEN get_orphaned_large_params;
    LOOP
        FETCH get_orphaned_large_params bulk collect
        INTO l_arr_large_values limit l_commit_freq;
        EXIT WHEN l_arr_large_values.COUNT = 0;

        FORALL j IN l_arr_large_values.FIRST..l_arr_large_values.LAST
             DELETE FROM mgmt_job_large_params
             where param_id = l_arr_large_values(j);
        COMMIT;
        IF EMDW_LOG.p_is_info_set THEN
            EMDW_LOG.info('delete_orphan_large_params() deleted '
                        || l_arr_large_values.COUNT
                        || ' unreferenced large params '
                       ,MODULE_NAME);
        END IF;
    END LOOP;
    CLOSE get_orphaned_large_params;

END;

-- submit_dbms_job('EM_JOB_RECOVERY.delete_orphan_schedules;', SYSDATE+1);
PROCEDURE submit_dbms_job ( p_what        VARCHAR2,
                            p_start_date  DATE     DEFAULT SYSDATE,
                            p_interval    VARCHAR2 DEFAULT NULL ) IS
l_job_num NUMBER;
l_count   NUMBER;
BEGIN
    SELECT count(1)
    INTO   l_count
    FROM   user_jobs 
    WHERE  what = p_what;

    -- If the job already exists, then don't submit
    -- a new one
    IF (l_count > 0) THEN
        RETURN;
    END IF;

    DBMS_JOB.SUBMIT(l_job_num, p_what, p_start_date, p_interval);
  
  END submit_dbms_job;
  
END EM_JOB_RECOVERY;
/
show errors;