Rem drv: <create type="out_of_box" params="EM_REPOS_USER" pos="-"/>
Rem
Rem $Header: init_outofbox.sql 18-jul-2007.09:11:44 edemembe Exp $
Rem
Rem init_outofbox.sql
Rem
Rem Copyright (c) 2005, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      init_outofbox.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      *************************************************************
Rem      **                                                         **
Rem      **  PLEASE CONTACT THE REPOSITORY TEAM IF YOU WISH TO ADD  **
Rem      **  ANY NEW ENTRIES TO THIS FILE                           **
Rem      **                                                         **
Rem      *************************************************************
Rem This file is reserved for running setup related SQL scripts that
Rem rely on user input during product installation. A good example of
Rem this setting up default notification rules based on the email address, 
Rem sender address etc. entered by the install user. In general, there
Rem are very few scripts that need user input.  All the other setup scripts
Rem that do not rely on user input must be added to core_sysaux_init.sql file. 
Rem
Rem The scripts that are run from _core_outofbox.sql must handle the case
Rem where the repository is *not* brand new (in other words, scripts must
Rem handle the case where entries for some of the items already exist in
Rem the repository). This is to handle cases where customer is using seed
Rem database with EM schema.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    edemembe    07/18/07 - Backport edemembe_bug-6157164 from main
Rem    rpinnama    07/26/06 - Remove the recreation of collection packages
Rem    rpinnama    10/24/05 - Fix 4692595 : Re-create collections packages to 
Rem                           avoid recreating seed. 
Rem    kmanicka    08/24/05 - added code for emkey and view user
Rem    ramalhot    07/28/05 - added code for rep_id generation 
Rem    gsbhatia    07/15/05 - gsbhatia_repmgr_new
Rem    gsbhatia    07/06/05 - Created
Rem
Rem This script requires the following arguments
Rem  1. Name of the repository user
Rem
DEFINE EM_REPOS_USER = "&1"


Rem
Rem Add Raw, 1Hour and 1Day partitions
Rem if partitioning is in use.
Rem
PROMPT Adding Partitions
DECLARE
  v_True VARCHAR2(5);
  v_partitions_init NUMBER := 0;
  v_err NUMBER;
  v_maxPartition   DATE;
  v_newPartition DATE := TRUNC(SYSDATE-7, 'DD');

  CURSOR max_timestamp_cursor (c_table_name VARCHAR2) IS
    SELECT MAX(TO_DATE(PARTITION_NAME,'YYYY-MM-DD HH24:MI'))
    FROM USER_TAB_PARTITIONS
    WHERE TABLE_NAME = UPPER(c_table_name);

BEGIN
  SELECT 'TRUE' INTO v_True
  FROM V$OPTION
  WHERE PARAMETER = 'Partitioning' AND
        VALUE = 'TRUE';
  
  -- If the repository is not in 'central' mode, exit.
  IF (emd_maintenance.is_central_mode < 1) THEN
     RAISE NO_DATA_FOUND;
  END IF;

  -- Partitioning option is enabled in the database.
  -- Check if the Raw, 1Hour and 1Day partitions exist
  -- Create the partitions if they dont already exist.
  IF NOT (EMD_MAINTENANCE.PARTITIONS_ENABLED(EMD_LOADER.p_metrics_raw_table)) THEN
    EMD_LOADER.INIT_RAW_PARTITIONS();
    v_partitions_init := 1;
  ELSE
    -- Get Max partition date
    OPEN max_timestamp_cursor(EMD_LOADER.p_metrics_raw_table);
    FETCH max_timestamp_cursor INTO v_maxPartition;
    CLOSE max_timestamp_cursor;

    -- Create a big partition from v_maxParition to SYSDATE - 7, if required
    IF (v_newPartition > v_maxPartition) THEN

      EXECUTE IMMEDIATE 'ALTER TABLE ' || EMD_LOADER.p_metrics_raw_table || 
           ' ADD PARTITION "' || TO_CHAR(v_newPartition, 'YYYY-MM-DD HH24:MI') ||
           '" VALUES LESS THAN (TO_DATE(''' ||
           TO_CHAR(v_newPartition, 'YYYY-MM-DD HH24:MI') ||
           ''', ''YYYY-MM-DD HH24:MI''))';
    END IF;

    -- ADD regular PARTITIONS from v_newPartition
    EMD_MAINTENANCE.ADD_PARTITIONS(EMD_LOADER.p_metrics_raw_table,
                                   EMD_LOADER.p_prepopulate_days_raw);
  END IF;


  IF NOT (EMD_MAINTENANCE.PARTITIONS_ENABLED(EMD_LOADER.p_metrics_1hour_table)) THEN
    EMD_LOADER.INIT_1HOUR_PARTITIONS();
    v_partitions_init := 1;
  ELSE
    -- Get Max partition date
    OPEN max_timestamp_cursor(EMD_LOADER.p_metrics_1hour_table);
    FETCH max_timestamp_cursor INTO v_maxPartition;
    CLOSE max_timestamp_cursor;

    -- Create a big partition from v_maxParition to SYSDATE - 7, if required
    IF (v_newPartition > v_maxPartition) THEN

      EXECUTE IMMEDIATE 'ALTER TABLE ' || EMD_LOADER.p_metrics_1hour_table || 
           ' ADD PARTITION "' || TO_CHAR(v_newPartition, 'YYYY-MM-DD HH24:MI') ||
           '" VALUES LESS THAN (TO_DATE(''' ||
           TO_CHAR(v_newPartition, 'YYYY-MM-DD HH24:MI') ||
           ''', ''YYYY-MM-DD HH24:MI''))';
    END IF;

    EMD_MAINTENANCE.ADD_PARTITIONS(EMD_LOADER.p_metrics_1hour_table,
                                EMD_LOADER.p_prepopulate_days_1hour, TRUE);
  END IF;

  IF NOT (EMD_MAINTENANCE.PARTITIONS_ENABLED(EMD_LOADER.p_metrics_1day_table)) THEN
    EMD_LOADER.INIT_1DAY_PARTITIONS();
    v_partitions_init := 1;
  ELSE

    -- Get Max partition date for 1 day table
    OPEN max_timestamp_cursor(EMD_LOADER.p_metrics_1day_table);
    FETCH max_timestamp_cursor INTO v_maxPartition;
    CLOSE max_timestamp_cursor;

    -- Create a big partition from v_maxParition to SYSDATE - 7, if required
    IF (v_newPartition > v_maxPartition) THEN

      EXECUTE IMMEDIATE 'ALTER TABLE ' || EMD_LOADER.p_metrics_1day_table || 
           ' ADD PARTITION "' || TO_CHAR(v_newPartition, 'YYYY-MM-DD HH24:MI') ||
           '" VALUES LESS THAN (TO_DATE(''' ||
           TO_CHAR(v_newPartition, 'YYYY-MM-DD HH24:MI') ||
           ''', ''YYYY-MM-DD HH24:MI''))';
    END IF;

    EMD_MAINTENANCE.ADD_PARTITIONS(EMD_LOADER.p_metrics_1day_table,
                                   EMD_LOADER.p_prepopulate_days_1day, TRUE);
  END IF;

  -- If partitions are initialized, the old unpartitioned tables are
  -- dropped and so all source objects have to be recompiled.
  IF (v_partitions_init = 1) THEN

    -- Recompile all invalid objects
    EMD_MAINT_UTIL.recompile_invalid_objects;

  END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
      NULL;  -- Catch and ignore because partitioning not installed.
END;
/

BEGIN
  INSERT
    INTO mgmt_parameters
         (parameter_name, parameter_value, parameter_comment, internal_flag)
  VALUES
         (MGMT_GLOBAL.G_REPOS_ID_PARAM_NAME, SYS_GUID(), 'Unique id for the repository' ,0);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    NULL;
END;
/

Rem Initilise the view user password and MGMT_TIME_SYNC pkg
BEGIN
MGMT_TIME_SYNC.init_out_of_box;
COMMIT;
END;
/