Rem drv: <migrate type="data_upgrade" version="10.1.0.4"/> 
Rem
Rem $Header: ecm_data_upgrade.sql 19-jul-2005.00:53:56 smalathe Exp $
Rem
Rem ecm_data_upgrade.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ecm_data_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    smalathe    07/18/05 - Bug4437833: Change update to commit frequently 
Rem    ktlaw       01/13/05 - add repmgr header 
Rem    smalathe    12/28/04 - 3903438: Set LAST_UPDATED_TIME in MGMT_DELTA_IDS 
Rem    vkhizder    11/24/04 - vkhizder_misc_fixes_nov8_04
Rem    vkhizder    11/18/04 - Schema upgrade from 10.1.0.3.0 to 10.1.0.4.0
Rem

DECLARE

l_ignore mgmt_performance_names.job_name%TYPE;

BEGIN

  -- To be safe, drop HOST_CONFIG purge policy in case it 
  -- were added by a oneoff patch. 
  BEGIN
    EM_PURGE.DROP_PURGE_POLICY('HOST_CONFIG');
  EXCEPTION
    WHEN OTHERS THEN NULL;
  END;

  -- (Re)create purge policy HOST_CONFIG for host config snapshots
  EM_PURGE.add_purge_policy('HOST_CONFIG',
                            EM_PURGE.G_POLICY_TYPE_SYSTEM,
                            'ECM_CT.PURGE_HOST_CONFIGS',
                            0, -- use number of retention hours used by
                               -- CONFIG retention group below
                            NULL, -- No rollup procedure
                            'Purge policy for orphan host config snapshots.',
                            EM_PURGE.G_RETENTION_GROUP_CONFIG
                            );

  COMMIT;

  -- Register logging module 'Host Configuration' if this was not done yet
  BEGIN
    SELECT job_name INTO l_ignore 
    FROM mgmt_performance_names
    WHERE job_name = 'Host Configuration';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      -- MGMT_LOG.register_logging_module(ECM_CT.HOST_CONFIG_LOG_MODULE_NAME);
      MGMT_LOG.register_logging_module('Host Configuration');
      COMMIT;
  END;

END;
/

Rem Bug3903438L Update MGMT_DELTA_IDS, set LAST_UPDATED_TIME
DECLARE
  l_num_rows   NUMBER := 0;
  l_max_commit NUMBER := 5000;
BEGIN
  BEGIN
    LOOP
      UPDATE MGMT_DELTA_IDS i SET LAST_UPDATED_TIME = (SELECT DELTA_TIME
        FROM MGMT_DELTA_ENTRY e WHERE
          i.LAST_DELTA_ENTRY_GUID = e.DELTA_ENTRY_GUID AND
          e.DELTA_TIME IS NOT NULL AND
          i.LAST_UPDATED_TIME != e.DELTA_TIME)
        WHERE ROWNUM <= l_max_commit;
      l_num_rows := SQL%ROWCOUNT;

      -- Bug 4437833 Commit after l_max_commit rows
      COMMIT;

      EXIT WHEN l_num_rows < l_max_commit;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN NULL;
  END;
END;
/