Rem drv: <create type="pkgbodys" pos="sdk/sdk_audit_log_pkgbody.sql+"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_maintenance_pkgbody.sql /st_emcore_10.2.0.4.4db11.2.0.3/1 2011/06/30 10:11:47 edemembe Exp $
Rem
Rem admin_maintenance_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      admin_maintenance_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
rem
rem   This maintanence package handles statistics maintenance for the EMD Schema.
rem   This package contains the following procedures:
rem
rem   update_stale_stats
rem
rem     This procedure collects stale statistics for the specified schema.
rem     It collects it every Saturday at 2am by default.
rem     These parameters can be configured.
rem
rem   delete_all_stats
rem
rem     This procedure deletes all stats for the specified schema. It will
rem     delete all schema statistics once on Saturday at 1am by default. These
rem     parameters can be configured.
rem
rem   analyze_emd_schema
rem
rem     This procedure is called by update_stale_stats.  It gathers
rem     statistics for all objects in the schema.  It runs only once.
rem     It calls GATHER_STALE for objects that have monitoring turned on
rem     and already have statistics or just analyzes objects that do not
rem     have statistics.
rem
rem   pin_plsql
rem
rem     This procedure is called by update_stale_stats.  This procedure will
rem     pin all the critical EMD PL/SQL packages in memory.  This is critical
rem     to ensure that we do not fragment the shared pool.
rem
rem INTENDED USAGE:
rem
rem   The first time you use this package run delete_all_stats before
rem   scheduling update_stale_stats. This will ensure all statistics are
rem   generated fresh.  This package assumes monitoring is enabled on
rem   all objects in the schema.
rem
rem   Once update_stale_stats is run it will continue schedule itself
rem   analyze_emd_schema to run once a week on (i.e.) Saturday at 2:00am
rem   (this is configurable, see above).
rem
rem COMMENTS:
rem
rem   To see scheduled/completed jobs use the dba_jobs (or user_jobs) table.
rem   To see running jobs use dba_jobs_running or user_jobs_running.
rem   To cancel a job use the above tables to find the job number ( job ) and
rem   then call: dbms_jobs.remove_job(job)
rem
rem   Due to a bug in 8.1.7 the analyze_emd_schema function
rem   calls analyze directly for any tables that do not have statistics.
rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    edemembe    06/27/11 - Backport 12401461
Rem    edemembe    02/24/10 - Bug 8912823
Rem    jsadras     12/29/08 - Backport jsadras_bug-7585160 from
Rem                           st_emcore_10.2.0.1.0
Rem    jsadras     10/21/08 - Backport jsadras_bug-7479372 from
Rem                           st_emcore_10.2.0.1.0
Rem    edemembe    10/07/08 - Backport edemembe_bug-6630325 from main
Rem    jsadras     09/23/08 - Bug:7426052, add dbms_assert calls
Rem    lsatyapr    07/10/08 - Bug7214155 Avoid jobtype upg in non-central mode
Rem    edemembe    06/03/08 - Backport edemembe_bug-7010689 from main
Rem    edemembe    07/11/07 - Bug 6157164 removed recompile_invalid_objects
Rem    edemembe    07/17/07 - Backport edemembe_bug-6157164 from main
Rem    jsadras     04/20/07 - Bug:5967369, resolve dbms_shared_pool
Rem    jsadras     04/20/07 - Bug:5967369, resolve dbms_shared_pool
Rem    jsadras     06/07/07 - Backport jsadras_bug-5967369 from main
Rem    minfan      05/22/07 - bug 5894442
Rem    minfan      05/22/07 - bug 5894442
Rem    rrawat      05/21/07 - Backport rrawat_bug-5502664 from main
Rem    zmi         05/11/07 - Back out change for bug 5890927.
Rem    jsadras     05/03/07 - Backport jsadras_bug-5357916 from main
Rem    dytong      05/02/07 - Bugfix 5890927
Rem    neearora    03/30/07 - changed schedule duraion of check_for_severites
Rem                           dbms_job
Rem    neearora    07/25/06 - Backport neearora_bug-4967160 from main 
Rem    rrawat      09/06/06 - Bug-5502664
Rem    jsadras     08/10/06 - Moved drop_partition/recompile invalid
Rem                           to emd_maint_util. Bug:5357916
Rem    pmodi       07/11/06 - Bug4465878 - call drop partition from 
Rem                           analyze_emd_schema job 
Rem    pmodi       07/20/06 - Backport pmodi_bug-4465878 from main 
Rem    pmodi       07/07/06 - Backport pmodi_bug-3900473 from main 
Rem    jsadras     12/11/05 - Backport jsadras_bug-4746117 from main 
Rem    neearora    06/30/06 - bug 4967160. added maintenance job to clenup the 
Rem                           mgmt_targets_delete table 
Rem    pmodi       04/20/06 - Bug:3900473 Use function to get default 
Rem                           retention window for truncation 
Rem    jsadras     11/22/05 - Bug:4746117, skip of partition creation 
Rem    pmodi       09/09/05 - Add new job 
Rem    pmodi       08/30/05 - Bug:4581761 Handle ORA-23421 in remove_dbms_job 
Rem    jsadras     08/22/05 - upper in compress index 
Rem    pmodi       08/17/05 - Bug:4547155 - Diff days to analyze data in raw, 
Rem                           1hour and 1day table 
Rem    jsadras     08/03/05 - Bug:4527081 temporarily remove compression for 
Rem                           IOT due to RDBMS bug 
Rem    jriel       07/20/05 - add RCA job to remove proc 
Rem    scgrover    07/07/05 - add extended sql trace 
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    pmodi       05/30/05 - Bug:4392834 - Adding weekly job to 
Rem                           analyze_emd_schema 
Rem    niramach    05/09/05 - Add dbms job for clear expired suppressions-bug 
Rem                           fix 4229157. 
Rem    jriel       05/17/05 - add RCA purge job 
Rem    jsadras     05/16/05 - Security fixes 
Rem    jsadras     04/26/05 - change to call em_task.get_worker_count 
Rem    jberesni    04/21/05 - add mgmt_bsln calls for GC 
Rem    jsadras     03/28/05 - Bug:4148613, added security checks
Rem    gsbhatia    03/15/05 - bug 4178702 
Rem    edemembe    03/09/05 - bug-4211429
Rem    jsadras     03/02/05 - Bug:4162225
Rem    edemembe    01/21/05 - Bug 3556656 
Rem    scgrover    01/10/05 - add index compress sql 
Rem    jsadras     12/27/04 - Bug3315409
Rem    jsadras     12/27/04 - bug3883828
Rem    snakai      11/22/04 - update beacon avail job 
Rem    jsadras     09/01/04 - repocollect
Rem    ramalhot    08/30/04 - cutover to new assoc tables 
Rem    jsadras     08/25/04 - bug3356702
Rem    dcawley     07/21/04 - Remove notification job 
Rem    dcawley     07/12/04 - Add job for checking state changes 
Rem    dcawley     07/07/04 - Increase user name size 
Rem    edemembe    06/24/04 - Adding pin PLSQL to database startup trigger 
Rem    edemembe    04/13/04 - Bug 3227319: Collect histograms for non-idx cols 
Rem    edemembe    04/13/04 - Moving partition truncation 
Rem    rpinnama    04/16/04 - Analyze the schema that has MGMT_TARGETS table
Rem    rpinnama    12/12/03 - Fix bug 3296613: Record the outofbox configured 
Rem    rzazueta    10/30/03 - Use mgmt_global.elapsed_time_msec function 
Rem    rzazueta    10/29/03 - Log elapsed time for execute_em_dbms_job_procs 
Rem    gan         10/17/03 - Fix dbms job submission 
Rem    lgloyd      09/18/03 - perf: bug 3092790 
Rem    rpinnama    09/07/03 - Submit purge policies job 
Rem    edemembe    08/25/03 - Partition truncation added 
Rem    glavash     08/07/03 - improve partition check 
Rem    rpinnama    07/25/03 - Merge all dbms jobs to one for standalone repository
Rem    rpinnama    07/14/03 - Start CENTRAL jobs only in central mode
Rem    yfeng       07/10/03 - Remove the job for bad sql evaluation
Rem    dcawley     07/10/03 - Remove notification delivery job
Rem    rpinnama    07/02/03 - Remove hardcoded SYSMAN
Rem    rpinnama    07/01/03 - APIs to submit and remove EM jobs
Rem    edemembe    07/01/03 - Adding partition support
Rem    rpinnama    03/11/03 - Implement move_tablespace procedure
Rem    rpinnama    11/19/02 - Add move procedure
Rem    rpinnama    03/17/03 - Provide get/setVersion APIs
Rem    sgrover     02/04/03 - ignore ORA-25191 errors
Rem    sgrover     01/20/03 - fix analyze
Rem    sgrover     01/14/03 - remove perf logging
Rem    sgrover     01/13/03 - add log of rebuild
Rem    sgrover     01/13/03 - use day of year
Rem    sgrover     01/10/03 - customize index rebuilds
Rem    edemembe    11/04/02 - Fixing perf logging
Rem    rpinnama    10/14/02 - Modify the CORE SDK
Rem    rpinnama    10/08/02 - Grant privileges to viewas
Rem    rpinnama    10/07/02 - Add grant core sdk procedure
Rem    edemembe    10/04/02 - Moving index rebuilds
Rem    edemembe    09/25/02 - Changing analysis param
Rem    edemembe    08/23/02 - Adding IOTs
Rem    edemembe    08/15/02 - Adding performance logging
Rem    edemembe    08/14/02 - Changing the index rebuild frequency to 1 day
Rem    edemembe    07/10/02 - Adding index rebuilds
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Created
Rem

create or replace package body emd_maintenance as
   -- suffix to be added to table name to get the 
   -- parameter name for last truncated partition
   g_trunc_suffix CONSTANT VARCHAR2(11) := '_LAST_TRUNC' ;
   --
   -- forward declaration
   --
   PROCEDURE cleanup_deleted_targets;

   FUNCTION REPOSITORY_DB_V91 RETURN BOOLEAN  ;

   FUNCTION REPOSITORY_DB_V10 RETURN BOOLEAN  ;

   FUNCTION REPOSITORY_DB_V102 RETURN BOOLEAN ;
   --
   -- PURPOSE 
   --   Procedure(s) to enable/disable/verify detailed logging
   -- 
   PROCEDURE enable_detailed_logging IS
   BEGIN
     BEGIN
       INSERT INTO MGMT_PARAMETERS
         (parameter_name, parameter_value, parameter_comment,
          internal_flag)
        VALUES
         (DETAILED_MAINT_LOGGING, 'TRUE', DETAILED_MAINT_LOGGING_CMT, 1);
     EXCEPTION
       WHEN DUP_VAL_ON_INDEX THEN
         UPDATE MGMT_PARAMETERS
            SET parameter_value = 'TRUE'
          WHERE parameter_name = DETAILED_MAINT_LOGGING;
     END;
   END enable_detailed_logging;

   PROCEDURE disable_detailed_logging IS
   BEGIN
     BEGIN
       INSERT INTO MGMT_PARAMETERS
         (parameter_name, parameter_value, parameter_comment,
          internal_flag)
        VALUES
         (DETAILED_MAINT_LOGGING, 'FALSE', DETAILED_MAINT_LOGGING_CMT, 1);
     EXCEPTION
       WHEN DUP_VAL_ON_INDEX THEN
         UPDATE MGMT_PARAMETERS
            SET parameter_value = 'FALSE'
          WHERE parameter_name = DETAILED_MAINT_LOGGING;
    END;
   END disable_detailed_logging;
   FUNCTION  is_detailed_logging_enabled 
     RETURN INTEGER IS
     l_retVal INTEGER := 0;
     l_cnt    INTEGER := 0;
   BEGIN
     SELECT COUNT(*) into l_cnt
       FROM MGMT_PARAMETERS
      WHERE parameter_name=DETAILED_MAINT_LOGGING
        AND upper(parameter_value)='TRUE';

     IF (l_cnt > 0) THEN
       -- detailed_maint_logging property is TRUE
       l_retVal := 1;
     END IF;

     RETURN l_retVal;
   END is_detailed_logging_enabled;
   --
   -- This is to keep track of already truncated partitions
   -- so we do not have to truncate again bug:3356702
   --
   PROCEDURE upd_trunc_partition(table_name      IN VARCHAR2,
                                 partition_name  IN VARCHAR2
                                ) 
   IS
   BEGIN
     UPDATE mgmt_parameters
        SET parameter_value = partition_name
      WHERE parameter_name = table_name||g_trunc_suffix ;
     IF SQL%NOTFOUND
     THEN
       INSERT INTO MGMT_PARAMETERS
         (parameter_name, parameter_value, parameter_comment,
          internal_flag)
        VALUES
         (table_name||g_trunc_suffix,partition_name,
          'Last partition truncated', 1) ;
     END IF ;
   END upd_trunc_partition;

   -- PROCEDURE:   update_stale_stat
   --
   -- PARAMETERS:
   --
   --   emd_schema VARCHAR2
   --
   --      Name of the schema containing the emd objects you want current
   --      statistics on i.e. SCOTT
   --
   --   start_day VARCHAR2 DEFAULT 'SATURDAY'
   --
   --      The day of the week you want statistics estimated on.
   --      The default is to have stats generated on SATURDAY.
   --
   --   start_hour DEFAULT 2
   --
   --      The hour you want statistics estimated on, in military time
   --      By default this is 2am. If you want it at 1pm you would pass 13.
   --
   --   start_minutes DEFAULT 0
   --
   --      Number of minutes after the hour you want statistics estimated on.
   --      The default is to estimate on the hour ( 0 minutes ).
   --
   --   frequency_in_days DEFAULT 1
   --
   --      How often you want to estimate statistics.
   --      The default case is once every day.
   --
   -- EXAMPLE:
   --
   --    If this is Monday, this example would start a job that updates stale
   --    statistics, in schema emd, everyday, at 2:50pm, including today.
   --
   --    > exec emd_maintenance.update_stale_stats('emd','MONDAY',14,50,1);
   --
   procedure update_stale_stats(emd_schema VARCHAR2,
                                start_day VARCHAR2 DEFAULT 'SATURDAY',
                                start_hour NUMBER DEFAULT 0,
                                start_minutes NUMBER DEFAULT 0,
                                frequency_in_days NUMBER DEFAULT 1) IS
    jobnum number;
    BEGIN    
      dbms_job.submit ( jobnum,
                        'emd_maintenance.analyze_emd_schema(''' ||
                         em_check.schema_name(emd_schema) || ''');',
                        trunc(next_day((sysdate-1),start_day)) +
                        start_hour/24+start_minutes/60/24,
                        'trunc(sysdate + '''|| frequency_in_days||''') + ''' ||
                        start_hour || ''' /24 + ''' || start_minutes ||
                        ''' /60/24');
      commit;
    END update_stale_stats;
    -- PROCEDURE: Add/truncate partitions
   -- procedure to add/truncate partitions called from analyze_emd_schema 
   -- PARAMETERS: None
   PROCEDURE add_truncate_partitions
   IS 
   l_retries     NUMBER := 0 ;
   l_max_retries NUMBER := 2 ;
   l_next_start_loc   NUMBER := 1 ;
   l_success BOOLEAN := FALSE ;
   l_error_message VARCHAR2(1000) ;
   BEGIN
     -- l_max_retries=2 means the loop at most will be
     -- executed thrice ( the first time and 2 retries)
     WHILE l_retries <= l_max_retries 
     LOOP
       BEGIN
         l_retries := l_retries + 1 ;

         IF l_next_start_loc = 1
         THEN
           ADD_PARTITIONS(EMD_LOADER.p_metrics_raw_table,
                          EMD_LOADER.p_prepopulate_days_raw);
           l_next_start_loc := 2 ;
         END IF ;

         IF l_next_start_loc = 2 
         THEN
           TRUNCATE_PARTITIONS(EMD_LOADER.p_metrics_raw_table,
                               EMD_MAINT_UTIL.get_raw_retention_window);
           l_next_start_loc := 3 ;
         END IF ;

         IF l_next_start_loc =3 
         THEN
           ADD_PARTITIONS(EMD_LOADER.p_metrics_1hour_table,
                          EMD_LOADER.p_prepopulate_days_1hour, TRUE);
           l_next_start_loc := 4 ;
         END IF ;

         IF l_next_start_loc = 4
         THEN 
           TRUNCATE_PARTITIONS(EMD_LOADER.p_metrics_1hour_table,
                               EMD_MAINT_UTIL.get_1hour_retention_window);
           l_next_start_loc := 5 ;
         END IF ;

         IF l_next_start_loc = 5
         THEN 
           ADD_PARTITIONS(EMD_LOADER.p_metrics_1day_table,
                          EMD_LOADER.p_prepopulate_days_1day, TRUE);
           l_next_start_loc := 6 ;
         END IF ;

         IF l_next_start_loc = 6
         THEN
           TRUNCATE_PARTITIONS(EMD_LOADER.p_metrics_1day_table,
                               EMD_MAINT_UTIL.get_1day_retention_window);
           l_next_start_loc := 7 ;
         END IF ;
         l_success := TRUE ;
         -- Exit the loop on successful execution till here
         EXIT ;
       EXCEPTION
       -- release locks and retry
       WHEN OTHERS THEN ROLLBACK ; 
           l_error_message := substr(sqlerrm,1,1000) ;
       END ;
     END LOOP ;

    IF NOT l_success
    THEN
        MGMT_LOG.LOG_ERROR('ANALYZE', null,
          'Error during maintenance: ' || l_error_message) ;
    END IF ;

   END add_truncate_partitions ;
   
   -- PROCEDURE:   analyze_emd_schema
   --
   -- PARAMETERS:
   --
   --   emd_schema VARCHAR2
   --
   --      Name of the schema containing the emd objects that you want
   --      to delete statistics on ( i.e. 'SCOTT' )
   --
   -- EXAMPLE:
   --
   --   This is called by update_stale_stats.  If called directly this function
   --   will immediatly update the stale_stats of all objects and generate
   --   stats for any objects that don't have statistics.
   --   NOTE:  objects will only be evaluated as having stale stats if
   --          monitoring is turned on for the object.
   --
   --  > execute emd_maintenance.analyze_emd_schema('emd');
   --
   PROCEDURE analyze_emd_schema(emd_schema  VARCHAR2) IS
      OLIST DBMS_STATS.OBJECTTAB;
      start_timestamp DATE DEFAULT SYSDATE;
      object_count NUMBER := 0;
      l_emd_schema VARCHAR2(30) := EM_CHECK.schema_name(emd_schema);
      l_table_name USER_TABLES.table_name%TYPE ;
      CURSOR partitioned_tables IS
       SELECT table_name, num_rows
       FROM USER_TABLES
       WHERE partitioned = 'YES' AND 
       (IOT_TYPE IS NULL OR IOT_TYPE <> 'IOT_OVERFLOW');

      can_not_access_overflow_table EXCEPTION;
      PRAGMA EXCEPTION_INIT(can_not_access_overflow_table, -25191);
   BEGIN      
     BEGIN
       cleanup_deleted_targets;
     END; 


    MGMT_SQLTRACE.EXTENDED_SQL_TRACE(EST_ANALYZE_NAME);
    -- Rebuild the high update indexes.
    rebuild_indexes;
                            
    DBMS_APPLICATION_INFO.SET_MODULE(
         module_name => 'Oracle Enterprise Manager.analyze EM schema',
         action_name => 'start');

    -- The following code block is obsolete in 10g or higher repository
    -- databases.
    IF (REPOSITORY_DB_V9) THEN
      -- Get list of objects with stale stats
      DBMS_STATS.GATHER_SCHEMA_STATS(l_emd_schema, DBMS_STATS.AUTO_SAMPLE_SIZE,
        FALSE, 'FOR ALL COLUMNS SIZE AUTO',NULL,'DEFAULT',FALSE,NULL,NULL,
        'LIST STALE',OLIST);

      -- Loop through the list of objects with stale stats.  Analyze the
      -- objects that are not partitioned.  Obsolete in 10g database.
      IF (OLIST.COUNT > 0) THEN
        FOR TABLE_ROW IN OLIST.FIRST..OLIST.LAST LOOP
          BEGIN
            IF OLIST(TABLE_ROW).OBJTYPE = 'TABLE' AND 
                         OLIST(TABLE_ROW).PARTNAME IS NULL AND 
               OLIST(TABLE_ROW).OBJNAME NOT IN ('MGMT_METRICS_RAW',
                                                'MGMT_METRICS_1HOUR',
                                                'MGMT_METRICS_1DAY') THEN
               dbms_stats.gather_table_stats(l_emd_schema, OLIST(TABLE_ROW).OBJNAME,
                 NULL, DBMS_STATS.AUTO_SAMPLE_SIZE, FALSE,
                 'FOR ALL COLUMNS SIZE AUTO',NULL,'GLOBAL',TRUE,NULL,NULL,NULL);
            END IF;
          EXCEPTION
            WHEN can_not_access_overflow_table THEN
              NULL;
            WHEN OTHERS THEN
              MGMT_LOG.LOG_ERROR('ANALYZE', null,
                'Error analyzing object: ' || OLIST(TABLE_ROW).OBJNAME ||
                 ' Error: ' || SUBSTR(SQLERRM, 1, 1000));
          END;
        END LOOP;
      END IF; -- OLIST.COUNT >  0

      -- For performance logging
      object_count := OLIST.COUNT;

      -- Clear the object list for reuse.
      OLIST.DELETE;
    
      -- Get list of objects with empty stats
      DBMS_STATS.GATHER_SCHEMA_STATS(l_emd_schema, DBMS_STATS.AUTO_SAMPLE_SIZE,
        FALSE, 'FOR ALL COLUMNS SIZE AUTO',NULL,'DEFAULT',FALSE,NULL,NULL,
        'LIST EMPTY',OLIST);

      -- Loop through the list of objects with empty stats.  Analyze the
      -- objects that are not partitioned.  Obsolete in 10g database.
      IF (OLIST.COUNT > 0) THEN
        FOR TABLE_ROW IN OLIST.FIRST..OLIST.LAST LOOP
          BEGIN
            IF OLIST(TABLE_ROW).OBJTYPE = 'TABLE' AND 
                     OLIST(TABLE_ROW).PARTNAME IS NULL AND
               OLIST(TABLE_ROW).OBJNAME NOT IN ('MGMT_METRICS_RAW',
                                                'MGMT_METRICS_1HOUR',
                                                'MGMT_METRICS_1DAY') THEN
               dbms_stats.gather_table_stats(l_emd_schema, OLIST(TABLE_ROW).OBJNAME,
                 NULL, DBMS_STATS.AUTO_SAMPLE_SIZE, FALSE,
                 'FOR ALL COLUMNS SIZE AUTO',NULL,'GLOBAL',TRUE,NULL,NULL,NULL);
            END IF;
          EXCEPTION
            WHEN can_not_access_overflow_table THEN
              NULL;
            WHEN OTHERS THEN
              MGMT_LOG.LOG_ERROR('ANALYZE', null,
                'Error analyzing object: ' || OLIST(TABLE_ROW).OBJNAME ||
                 ' Error: ' || SUBSTR(SQLERRM, 1, 1000));
          END;
        END LOOP;
      END IF; -- OLIST.COUNT > 0
     
      -- For performance logging
      object_count := object_count + OLIST.COUNT;
 
      -- Clear the object list for reuse.
      OLIST.DELETE;
    END IF; -- REPOSITORY_DB_V9

    --
    -- Only execute the next block if the repository database version is 
    -- 9.2 or higher.  Partitioned table analysis is expected to be handled
    -- manually in repositories less than 9.2.  Still required in 10g 
    -- database due to inefficient analysis of large partitioned IOTs
    -- by default system analysis job.
    -- 
    IF NOT (REPOSITORY_DB_V91) THEN    
      -- Loop through the list of partitioned tables and analyze them with the
      -- smallest possible sample size.  We only want to analyze them more than
      -- once a week if the table is less that a set number of rows in size.
      -- We will analyze the partitioned tables once a week otherwise.
      FOR part_rec in partitioned_tables LOOP
        BEGIN
          l_table_name := part_rec.table_name;

          -- All table other then MGMT_METRICS_RAW and MGMT_METRICS_1HOUR to be analysed on 7th day of week
          -- MGMT_METRICS_1HOUR to be analysed on 6th day of week
          -- MGMT_METRICS_RAW to be analysed on 5th day of week
          IF (part_rec.num_rows < 1000000 OR part_rec.num_rows is null) OR 
             (to_char(SYSDATE,'d') = '7' AND part_rec.table_name NOT IN ('MGMT_METRICS_RAW', 'MGMT_METRICS_1HOUR')) OR 
             (to_char(SYSDATE,'d') = '6' AND part_rec.table_name = 'MGMT_METRICS_1HOUR') OR 
             (to_char(SYSDATE,'d') = '5' AND part_rec.table_name = 'MGMT_METRICS_RAW') THEN 

            -- Only run the following procedure on v10 DBs or higher.
            IF NOT (REPOSITORY_DB_V9) THEN
              execute immediate 
               'call dbms_stats.unlock_table_stats(' || 
                 em_check.enquote_name(l_emd_schema) || ', ' || 
                 em_check.enquote_name(l_table_name) || ')';
            END IF;

              dbms_stats.gather_table_stats(l_emd_schema, l_table_name,
                    NULL, .000001 , FALSE, 'FOR ALL INDEXED COLUMNS',NULL,
                    'GLOBAL',TRUE,NULL,NULL,NULL);

            -- Only run the following procedure on v10 DBs or higher.
            IF NOT (REPOSITORY_DB_V9) THEN
              execute immediate
               'call dbms_stats.lock_table_stats(' || 
                 em_check.enquote_name(l_emd_schema) || ', ' || 
                 em_check.enquote_name(l_table_name) || ')';
            END IF;

            -- For performance logging
            object_count := object_count+1;

          END IF;
        EXCEPTION
            WHEN can_not_access_overflow_table THEN
              BEGIN
                EXECUTE IMMEDIATE 
                   'call dbms_stats.lock_table_stats(' || 
                      em_check.enquote_name(l_emd_schema) || ', ' ||
                      em_check.enquote_name(l_table_name) || ')';
              EXCEPTION
                WHEN OTHERS THEN
                 NULL;  -- Ignore errors
              END;
            WHEN OTHERS THEN
              BEGIN
                EXECUTE IMMEDIATE 
                   'call dbms_stats.lock_table_stats(' || 
                         em_check.enquote_name(l_emd_schema) || ', ' ||
                         em_check.enquote_name(l_table_name) || ')';
              EXCEPTION
                WHEN OTHERS THEN
                 NULL;  -- Ignore errors
              END;

              MGMT_LOG.LOG_ERROR('ANALYZE', null,
                'Error analyzing object: ' || part_rec.table_name ||
                 ' Error: ' || SUBSTR(SQLERRM, 1, 1000));
        END;
      END LOOP;
    END IF;

    -- Executing DBMS_STATS.gather_system_stats on every Saturday
    IF TO_CHAR(SYSDATE, 'DAY') LIKE 'SATURDAY%' THEN

      DBMS_STATS.gather_system_stats();

    END IF;

    -- Log overall maintenance performance.
    MGMT_LOG.LOG_PERFORMANCE('ANALYZE', 
             (SYSDATE-start_timestamp)*(24*60*60*1000),
                start_timestamp, 'Y', 'OBJECTS', object_count);

    -- Pin all PL/SQL packages and triggers in memory.
    pin_plsql;

    --
    -- Perform partion truncation after analysis due to bug: 353446
    --
    add_truncate_partitions ;

    -- partition_maintenance will drop the partition beyond retention period
    -- IF DB versio is >= 10.2

    IF REPOSITORY_DB_V102 THEN
      EMD_MAINT_UTIL.partition_maintenance ( p_stop_job => FALSE );
    END IF;

    --add recreate synonyms to recreate invalidated records in 10
    IF REPOSITORY_DB_V10 THEN
      EMD_MAINT_UTIL.recreate_synonyms;
    END IF;

    DBMS_APPLICATION_INFO.SET_MODULE(
         module_name => ' ',
         action_name => ' ');
    EXCEPTION
      WHEN can_not_access_overflow_table THEN
         NULL; 
      WHEN OTHERS THEN
        MGMT_LOG.LOG_ERROR('ANALYZE', null,
          'Error during maintenance: ' || SUBSTR(SQLERRM, 1, 1000));                     
                        
   END analyze_emd_schema;

   --
   -- Procedure to tun on/of extended sql trace for analyze schema dbms job
   --
   PROCEDURE DBMSJOB_EXTENDED_SQL_TRACE_ON(p_value IN BOOLEAN) IS
   BEGIN  
     MGMT_SQLTRACE.EXTENDED_SQL_TRACE_ON(EST_ANALYZE_NAME, p_value);
   END DBMSJOB_EXTENDED_SQL_TRACE_ON;
   
   -- PROCEDURE:   analyze_objects_without_stats
   --
   -- PARAMETERS:
   --
   --  emd_schema VARCHAR2
   --
   -- Name of the schema containing the emd objects without stats that you want
   --   to analyze( i.e. 'SCOTT' )
   --
   -- EXAMPLE:
   --
   --   This is called by RepMgr uprade script (emrepmgr.pl). If called 
   -- directly this function
   -- will immediatly generate stats for tables in db version 9.2+ that 
   -- are not partitioned

   --  > execute emd_maintenance.analyze_objects_without_stats('emd');
   --
   PROCEDURE analyze_objects_without_stats(emd_schema  VARCHAR2) IS
      OLIST DBMS_STATS.OBJECTTAB;
      l_emd_schema VARCHAR2(30) := EM_CHECK.schema_name(emd_schema);

   BEGIN

    DBMS_APPLICATION_INFO.SET_MODULE(
         module_name => 'Oracle Enterprise Manager.analyze objects without stats',
         action_name => 'start');

    --If repository is 9.2 or above    
    IF NOT (REPOSITORY_DB_V91) THEN    
      -- Get list of objects with empty stats
      DBMS_STATS.GATHER_SCHEMA_STATS(l_emd_schema, DBMS_STATS.AUTO_SAMPLE_SIZE,
        FALSE, 'FOR ALL COLUMNS SIZE AUTO',NULL,'DEFAULT',FALSE,NULL,NULL,
        'LIST EMPTY',OLIST);

      -- Loop through the list of objects with empty stats.  Analyze the
      -- objects that are not partitioned.
      IF (OLIST.COUNT > 0) THEN
        FOR TABLE_ROW IN OLIST.FIRST..OLIST.LAST LOOP
          BEGIN         
            IF OLIST(TABLE_ROW).OBJTYPE = 'TABLE' AND
                     OLIST(TABLE_ROW).PARTNAME IS NULL THEN
                --If table is not mgmt_metric_raw, 1day or 1hour, 
                --let Oracle determine appropriate sample size
                IF OLIST(TABLE_ROW).OBJNAME NOT IN ('MGMT_METRICS_RAW',
                                                'MGMT_METRICS_1HOUR',
                                                'MGMT_METRICS_1DAY') THEN
                  dbms_stats.gather_table_stats(l_emd_schema, OLIST(TABLE_ROW).OBJNAME,                 NULL, DBMS_STATS.AUTO_SAMPLE_SIZE, FALSE,
                 'FOR ALL COLUMNS SIZE AUTO',NULL,'GLOBAL',TRUE,NULL,NULL,NULL);                ELSE
                --If table is either mgmt_metric_raw, 1day or 1hour, 
                --use estimate percentage as 0.000001
                  dbms_stats.gather_table_stats(l_emd_schema, OLIST(TABLE_ROW).OBJNAME,                 NULL, 0.000001, FALSE,
                 'FOR ALL COLUMNS SIZE AUTO',NULL,'GLOBAL',TRUE,NULL,NULL,NULL);                END IF;                        
            END IF;
          EXCEPTION
            WHEN OTHERS THEN
              MGMT_LOG.LOG_ERROR('ANALYZE OBJECTS WITHOUT STATS', null,
                'Error analyzing object: ' || OLIST(TABLE_ROW).OBJNAME ||
                 ' Error: ' || SUBSTR(SQLERRM, 1, 1000));
          END;
        END LOOP;
      END IF; -- OLIST.COUNT > 0

      -- Clear the object list
      OLIST.DELETE;
    END IF; -- REPOSITORY_DB_V9.2+

    DBMS_APPLICATION_INFO.SET_MODULE(
         module_name => ' ',
         action_name => ' ');
    EXCEPTION
      WHEN OTHERS THEN
        MGMT_LOG.LOG_ERROR('ANALYZE OBJECTS WITHOUT STATS', null,
          'Error during maintenance: ' || SUBSTR(SQLERRM, 1, 1000));
   END analyze_objects_without_stats;



  -- PROCEDURE:   delete_all_stats
  --
  -- PARAMETERS:
  --
  --    emd_schema VARCHAR2
  --
  --    name of the schema containing the emd objects that you want
  --    to delete statistics on.  i.e. 'SCOTT'
  --
  --  start_day VARCHAR2 DEFAULT 'SATURDAY' -
  --
  --    The day of the week you want to delete statistics on.
  --    The default is to have stats deleted on SATURDAY.
  --
  --  start_hour DEFAULT 1
  --
  --     The hour you want statistics deleted on, in military time
  --     By default this is 1am. If you want it at 1pm you would pass 13.
  --
  --  start_minutes DEFAULT 0
  --
  --     Number of minutes after the hour you want statistics deleted on.
  --     The default is to delete statistics on the hour ( 0 minutes ).
  --
  -- EXAMPLE:
  --
  --   If this is Monday, this example would delete all stats, in schema emd,
  --   today, at 2:50pm.
  --
  --  > execute emd_maintenance.delete_all_stats('emd','MONDAY',14,50);
  --
  procedure delete_all_stats(emd_schema VARCHAR2,
                             start_day VARCHAR2 DEFAULT 'SATURDAY',
                             start_hour NUMBER DEFAULT 0,
                             start_minutes NUMBER DEFAULT 0) IS
     jobnum number;
     BEGIN
       dbms_job.submit ( jobnum,
                         'dbms_stats.delete_schema_stats(''' ||
                            EM_CHECK.schema_name(emd_schema) || ''');',
                        trunc(next_day((sysdate-1),start_day)) +
                        start_hour/24+start_minutes/60/24);
       commit;
     END delete_all_stats;

  -- PROCEDURE:   monitor_indexes
  --
  --   This procedure will turn on index usage monitoring for all indexes in the
  --   schema which it is run.
  --
  -- PARAMETERS:
  --
  --    none
  --
  -- EXAMPLE:
  --
  --  > execute emd_maintenance.monitor_indexes();
  --
  procedure monitor_indexes IS
     CURSOR user_indexes IS
      SELECT index_name
      FROM USER_INDEXES
      WHERE index_type = 'NORMAL' AND status = 'VALID';
   BEGIN
     -- Loop through the index names and turn usage monitoring on.
     FOR idx_rec in user_indexes LOOP
       execute immediate 'alter index ' || 
                  em_check.enquote_name(idx_rec.index_name)
                        || ' monitoring usage';
     END LOOP;
   END monitor_indexes;

  -- PROCEDURE:   rebuild_indexes
  --
  --   This procedure will rebuild indexes on the repository tables
  --   with the highest proportion of inserts/deletes.
  --
  -- PARAMETERS:
  --
  --    none
  --
  -- EXAMPLE:
  --
  --  > execute emd_maintenance.rebuild_indexes();
  --
  procedure rebuild_indexes IS
     CURSOR table_list IS
      SELECT table_name, interval, table_type FROM mgmt_rebuild_indexes;

     TYPE p_table_names IS TABLE OF VARCHAR(30);
     TYPE p_intervals   IS TABLE OF NUMBER(10);
     TYPE p_table_types IS TABLE OF VARCHAR(30);

     v_rebuild_flag NUMBER(10);
     v_table_names  p_table_names;
     v_intervals    p_intervals;
     v_table_types  p_table_types;

     start_timestamp DATE DEFAULT SYSDATE;
   BEGIN
     DBMS_APPLICATION_INFO.SET_MODULE(
          module_name => 'Oracle Enterprise Manager.rebuild EM indexes',
          action_name => ' ');

     -- get tables to rebuild
     OPEN  table_list;
     FETCH table_list BULK COLLECT INTO v_table_names, v_intervals, v_table_types;
     CLOSE table_list;

     FOR i IN 1..v_table_names.LAST LOOP
       SELECT MOD(TO_CHAR(sysdate, 'DDD'), v_intervals(i)) INTO v_rebuild_flag FROM DUAL;
       IF v_rebuild_flag = 0 THEN
         IF v_table_types(i) = 'IOT' THEN
           rebuild_iot(v_table_names(i));
         END IF;
         rebuild_table_indexes(v_table_names(i));
       END IF;
     END LOOP; 

     -- Log index rebuild performance
     MGMT_LOG.LOG_PERFORMANCE('MAINT INDEX REBUILDS',
             (SYSDATE-start_timestamp)*(24*60*60*1000),
              start_timestamp, 'Y', 'NONE', 0);

     DBMS_APPLICATION_INFO.SET_MODULE(
          module_name => ' ',
          action_name => ' ');

   EXCEPTION
     WHEN OTHERS THEN
        IF table_list%ISOPEN THEN
          CLOSE table_list;
        END IF;
        MGMT_LOG.LOG_ERROR('MAINT INDEX REBUILDS', null,
          'Error during index rebuilds: ' || SUBSTR(SQLERRM, 1, 1000));

   END rebuild_indexes;

  -- PROCEDURE:   rebuild_iot
  --
  --   This procedure will rebuild an iot
  --
  -- PARAMETERS:
  --
  --    iot name

  procedure rebuild_iot(iot IN VARCHAR2) IS
     begin_timestamp DATE DEFAULT SYSDATE;
   BEGIN
      DBMS_APPLICATION_INFO.SET_ACTION(action_name => iot || ' IOT rebuild');
      execute immediate 'alter table ' || EM_CHECK.enquote_name(iot) || 
           ' move online';
      DBMS_APPLICATION_INFO.SET_ACTION(action_name => ' ');
   EXCEPTION
      WHEN OTHERS THEN
        MGMT_LOG.LOG_ERROR('MAINT INDEX REBUILDS', null,
          'Error rebuilding ' || iot || ' IOT: ' || SUBSTR(SQLERRM, 1, 1000));

   END rebuild_iot;

  -- PROCEDURE:   rebuild_table_indexes
  --
  --   This procedure will rebuild all indexes on a given table
  --
  -- PARAMETERS:
  --
  --    table name

  procedure rebuild_table_indexes(tablename IN VARCHAR2) IS
     TYPE p_IndexNameList IS TABLE OF VARCHAR2(30)
       INDEX BY BINARY_INTEGER;
     CURSOR table_indexes IS
      SELECT index_name
      FROM USER_INDEXES
      WHERE index_type = 'NORMAL' AND status = 'VALID' AND
       table_name = tablename; 
     start_timestamp DATE DEFAULT SYSDATE;
     v_index_names p_IndexNameList;
   BEGIN
    OPEN table_indexes;
    -- Loop through the target cursor limiting to purge batch so we scale.
    FETCH table_indexes BULK COLLECT INTO v_index_names;
    CLOSE table_indexes;

    IF v_index_names.COUNT != 0 THEN
      FOR i IN v_index_names.FIRST..v_index_names.LAST LOOP
        BEGIN
          DBMS_APPLICATION_INFO.SET_ACTION(action_name => v_index_names(i) ||
                       ' rebuilding');
          execute immediate 'alter index ' || 
             em_check.enquote_name(v_index_names(i)) || 
                  ' rebuild online nologging';
          DBMS_APPLICATION_INFO.SET_ACTION(action_name => ' ');
      EXCEPTION
        WHEN OTHERS THEN
          MGMT_LOG.LOG_ERROR('MAINT INDEX REBUILDS', null,
            'Error rebuilding index ' || v_index_names(i) ||
            ' Error: ' || SUBSTR(SQLERRM, 1, 1000));
        END;
      END LOOP;  -- Index size loop
    END IF;

   EXCEPTION
     WHEN OTHERS THEN
        IF table_indexes%ISOPEN THEN
          CLOSE table_indexes;
        END IF;

   END rebuild_table_indexes;

  -- PROCEDURE:   pin_plsql
  -- 
  --   This procedure will pin all the most heavily used SQL objects into
  --   the shared pool.  This will help prevent fragmentation of the shared pool
  --   on busy systems.
  --
  -- PARAMETERS:
  --
  --    none
  --
  -- EXAMPLE:
  --
  --  > execute emd_maintenance.pin_plsql();
  --
  procedure pin_plsql IS
     CURSOR pinnable_objects IS
      SELECT object_name
      FROM ALL_OBJECTS
      WHERE object_type in ('PROCEDURE','PACKAGE','FUNCTION')
      AND status = 'VALID'
      AND owner = MGMT_USER.GET_REPOSITORY_OWNER;
      l_object_exists NUMBER := 0 ;
   BEGIN

     -- Make this routine a no-op for EM 11g. epd
     RETURN;

     DBMS_APPLICATION_INFO.SET_MODULE(
         module_name => 'Oracle Enterprise Manager.pin EM plsql',
         action_name => 'start');

    BEGIN
      IF EM_CHECK.SQL_OBJECT_NAME('SYS.DBMS_SHARED_POOL.KEEP') = 
                                  'SYS.DBMS_SHARED_POOL.KEEP'
      THEN
        l_object_exists := 1 ;
      END IF ;
    EXCEPTION
    WHEN OTHERS THEN
      l_object_exists := 0 ;
    END ; 
     -- Pin the key SQL based objects in memory, if
     -- the DBMS_SHARED_POOL package exists.
     IF (l_object_exists > 0) THEN
       FOR pin_rec in pinnable_objects LOOP
         EXECUTE IMMEDIATE 
           'BEGIN sys.dbms_shared_pool.keep(:1); END;'
         USING  MGMT_USER.GET_REPOSITORY_OWNER||'.'||pin_rec.object_name;
       END LOOP;
     END IF;

     EXCEPTION
      WHEN OTHERS THEN
        IF pinnable_objects%ISOPEN THEN
          CLOSE pinnable_objects;
        END IF;
        MGMT_LOG.LOG_ERROR('MAINT PIN PLSQL', null,
          'Error pinning PLSQL: ' || SUBSTR(SQLERRM, 1, 1000));
   END pin_plsql;

  procedure collect_segment_sizes IS
    TYPE p_TableNameList IS TABLE OF VARCHAR2(30)
      INDEX BY BINARY_INTEGER;
    TYPE p_AllocSizeList IS TABLE OF NUMBER
      INDEX BY BINARY_INTEGER;
    TYPE p_SpaceUsedList IS TABLE OF NUMBER
      INDEX BY BINARY_INTEGER;
    TYPE p_IndexNameList IS TABLE OF VARCHAR2(30)
      INDEX BY BINARY_INTEGER;

    TYPE p_TableRec IS RECORD
     (tableName     p_TableNameList,    -- The name of the table
      AllocatedSize p_AllocSizeList,    -- The total space allocated for table
      SpaceUsed     p_SpaceUsedList);   -- The actual used space for table

    TYPE p_IndexRec IS RECORD
     (indexName     p_IndexNameList,    -- The name of the index
      tableName     p_TableNameList,    -- The name of the table the index is on
      AllocatedSize p_AllocSizeList,    -- The total space allocated for index
      SpaceUsed     p_SpaceUsedList);   -- The actual used space for index

    CURSOR table_alloc_cursor IS
      SELECT t.table_name,
           s.bytes
      FROM USER_TABLES t, USER_SEGMENTS s
      WHERE t.table_name = s.segment_name
        AND s.segment_type = 'TABLE';

    CURSOR index_alloc_cursor IS
      SELECT i.index_name,
           i.table_name,
           s.bytes
      FROM USER_INDEXES i, USER_SEGMENTS s
      WHERE i.index_name = s.segment_name
        AND s.segment_type = 'INDEX';

    v_tables  p_TableRec;
    v_indexes p_IndexRec;
    v_user VARCHAR2(256);
    rows NUMBER := 0;

    --
    -- Output variables for DBMS_SPACE package
    --

    v_tblocks number;
    v_tbytes number;
    v_ublocks number;
    v_ubytes number;
    v_luef number;
    v_lueb number;
    v_lub number;

    i BINARY_INTEGER;
    start_timestamp DATE DEFAULT SYSDATE;
  BEGIN
    DBMS_APPLICATION_INFO.SET_MODULE(
      module_name => 'Oracle Enterprise Manager.Segment Sizing',
      action_name => 'Collect');
      
    OPEN table_alloc_cursor;
    -- Loop through the target cursor limiting to purge batch so we scale.
    FETCH table_alloc_cursor BULK COLLECT INTO v_tables.tableName,
                                             v_tables.AllocatedSize;
    -- Get the user for the sizing procedure
    SELECT USER INTO v_user FROM DUAL;

    -- Loop through the tables finding the allocated size.
    FOR i IN v_tables.tableName.FIRST..v_tables.tableName.LAST LOOP
      dbms_space.unused_space(v_user,v_tables.tableName(i),
                            'TABLE',
                            v_tblocks, v_tbytes,
                            v_ublocks, v_ubytes,
                            v_luef, v_lueb,
                            v_lub, null);
      v_tables.SpaceUsed(i) := v_tbytes - v_ubytes;
    END LOOP;  -- Table size loop
    CLOSE table_alloc_cursor;

    OPEN index_alloc_cursor;
    FETCH index_alloc_cursor BULK COLLECT INTO v_indexes.indexName,
                                             v_indexes.tableName,
                                             v_indexes.AllocatedSize;

    -- Loop through the indexes finding the allocated size.
    FOR i IN v_indexes.indexName.FIRST..v_indexes.indexName.LAST LOOP
      dbms_space.unused_space(v_user,v_indexes.indexName(i),
                            'INDEX',
                            v_tblocks, v_tbytes,
                            v_ublocks, v_ubytes,
                            v_luef, v_lueb,
                            v_lub, null);
      v_indexes.SpaceUsed(i) := v_tbytes - v_ubytes;
    END LOOP;  -- Index size loop
    CLOSE index_alloc_cursor;

    -- Insert the collected table size records.
    FORALL i IN v_tables.tableName.FIRST..v_tables.tableName.LAST
      INSERT INTO MGMT_TABLE_SIZES (TABLE_NAME, ALLOCATED_SIZE, SPACE_USED)
          VALUES(v_tables.tableName(i), v_tables.AllocatedSize(i),
                    v_tables.SpaceUsed(i));
    -- Insert the collected index size records.
    FORALL i IN v_indexes.indexName.FIRST..v_indexes.indexName.LAST
      INSERT INTO MGMT_INDEX_SIZES (INDEX_NAME, TABLE_NAME,
          ALLOCATED_SIZE, SPACE_USED)
          VALUES(v_indexes.indexName(i), v_indexes.tableName(i),
                 v_indexes.AllocatedSize(i), v_indexes.SpaceUsed(i));
    COMMIT;
    MGMT_LOG.LOG_PERFORMANCE('MAINT COLLECT SEG SIZES', 
             (SYSDATE-start_timestamp)*(24*60*60*1000),
                start_timestamp, 'Y', 'NONE', 0);
    EXCEPTION
      WHEN OTHERS THEN
        IF table_alloc_cursor%ISOPEN THEN
          CLOSE table_alloc_cursor;
        END IF;
        IF index_alloc_cursor%ISOPEN THEN
          CLOSE index_alloc_cursor;
        END IF;
        MGMT_LOG.LOG_ERROR('MAINT COLLECT SEG SIZES', null,
          'Error during seg sizing: ' || SUBSTR(SQLERRM, 1, 1000));      
  end collect_segment_sizes;


  --  internal procedure to submit a job
  PROCEDURE submit_dbms_job(v_job_num OUT NUMBER, 
                            v_what        VARCHAR2,
			    v_start_date  DATE,
			    v_interval    VARCHAR2)
  IS
    l_count NUMBER;
  BEGIN
    SELECT count(*) 
      INTO l_count
      FROM user_jobs 
     WHERE what = v_what;

    -- If the job already exists, then don't submit
    -- a new one
    IF (l_count != 0) THEN 
       RETURN;
    END IF;
     
    DBMS_JOB.SUBMIT(v_job_num, v_what, v_start_date, v_interval);

  END submit_dbms_job;


  -- The following procedures are used to start and stop 
  -- EM jobs
  PROCEDURE submit_em_dbms_jobs
  IS
    jobNum NUMBER;
    l_user_name VARCHAR2(32);
    l_worker_count NUMBER ;
  BEGIN

    -- Submit the following jobs for central mode only.
    IF (is_central_mode > 0) THEN 

      -- Collections job
      BEGIN
        MGMT_COLLECTION.start_workers ;
        COMMIT ;
      END ;
 
      -- Basic rollup job
      submit_dbms_job(jobNum, 'EMD_LOADER.ROLLUP();',
                      sysdate + (1/48), 'sysdate + (1/24)' );
      COMMIT;

      -- Notification check job
      submit_dbms_job(jobNum, 'EMD_NOTIFICATION.CHECK_FOR_SEVERITIES();',
                      SYSDATE, 'sysdate + (1/(24*60*2))' );
      COMMIT;

      -- Ping system job
      submit_dbms_job(jobNum, 'EM_PING.MARK_NODE_STATUS();',
                      SYSDATE, 'sysdate + (1/(24*60*2))' );
      COMMIT;
 
      -- Submit job to compute the severities every minute
      submit_dbms_job(jobNum, 'EM_SEVERITY_REPOS.EXECUTE_REPOS_SEVERITY_EVAL();',
                      SYSDATE, 'sysdate + 1 / (24 * 60)' );
      COMMIT;

      -- Job engine jobs
      submit_dbms_job(JobNum, 'MGMT_JOB_ENGINE.apply_purge_policies();',
                      SYSDATE, 'sysdate + 1' );
      COMMIT;

      -- maintenace / analysis job
      l_user_name := user;

      BEGIN
        SELECT owner INTO l_user_name
	  FROM all_tables
         WHERE table_name = 'MGMT_TARGETS';
      EXCEPTION 
        WHEN NO_DATA_FOUND THEN
	  -- Default to SYSMAN
	  l_user_name := 'SYSMAN';
      END;

      submit_dbms_job(jobNum,
                      'emd_maintenance.analyze_emd_schema(''' || l_user_name || ''');',
                      trunc(next_day((sysdate-1), TO_CHAR((SYSDATE+1),'DAY'))) +
                             3/24 + 10/60/24,
                      'trunc(sysdate + 1) + 3/24 + 10/60/24');
      COMMIT;

      -- RT Rollup job
      submit_dbms_job(jobNum, 'EMD_RT_ROLLUP.ROLLUP();',
                      SYSDATE, 'sysdate + (1/24)' );
      COMMIT;

      -- E2E rollup job
      submit_dbms_job(jobNum, 'EMD_E2E_ROLLUP.ROLLUP();',
                    SYSDATE, 'sysdate + (1/24)' );
      COMMIT;

      -- Service beacon availability job
      submit_dbms_job(jobNum, 'MGMT_GENSVC_AVAIL.EXEC_AVAIL_JOB();',
                    SYSDATE, 'sysdate + (1/1440)' );
      COMMIT;

      -- Purge policies job
      submit_dbms_job(jobNum, 'EM_PURGE.APPLY_PURGE_POLICIES();',
                    trunc(SYSDATE+1), 'trunc(sysdate + 1)' );
      COMMIT;
      
      -- RCA purge job
      submit_dbms_job(jobNum, 'MGMT_RCA.PURGE_RCA_RESULTS();',
                    SYSDATE, 'sysdate + 1' );
      COMMIT;

      -- Metric baselines compute stats and set thresholds jobs
      MGMT_BSLN.SUBMIT_BSLN_JOBS;
      COMMIT;
      
      -- Clear expired policy suppressions job
      submit_dbms_job(jobNum, 'EM_POLICY.CLEAR_EXPIRED_SUPPRESSIONS;',
                    SYSDATE, 'trunc(SYSDATE + 1)+(1/60/24)' );
      COMMIT;

      --
      -- Submit RESUBMIT_FAILED_TASK to run every hour, starting from next hour.
      --

      submit_dbms_job(jobNum, 'EM_TASK.RESUBMIT_FAILED_TASK();',
                      TRUNC(SYSDATE, 'HH24')+1/24,
                      'SYSDATE+1/24'
                     );
      COMMIT;
    ELSE

      -- Submit only one job for stand alone
      submit_dbms_job(jobNum, 'EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();',
                      SYSDATE, 'sysdate + 1 / (24 * 60)' );
      COMMIT;
 
    END IF;
 
  END submit_em_dbms_jobs;


  PROCEDURE execute_em_dbms_job_procs
  IS
      v_proc_start_date DATE DEFAULT SYSDATE;
      l_start_timestmp  TIMESTAMP := SYSTIMESTAMP;
      l_status NUMBER ;
  BEGIN 
 
    -- Collections proc
    IF MOD(to_char(v_proc_start_date,'MI'),2) = 0 
    THEN
      BEGIN
        l_status := EM_TASK.run_tasks(1) ;
      EXCEPTION 
        WHEN OTHERS THEN
          NULL;
      END;
    END IF ;

    -- Ping system proc
    BEGIN
      EM_PING.MARK_NODE_STATUS();
    EXCEPTION 
      WHEN OTHERS THEN
        NULL;
    END;

    -- compute severities proc
    BEGIN
      EM_SEVERITY_REPOS.EXECUTE_REPOS_SEVERITY_EVAL();
    EXCEPTION 
      WHEN OTHERS THEN
        NULL;
    END;

    -- Run the following jobs once every hour
    IF (to_char(v_proc_start_date, 'MI') = '00') THEN 

      -- Basic rollup proc
      BEGIN
        EMD_LOADER.ROLLUP();
      EXCEPTION 
        WHEN OTHERS THEN
          NULL;
      END;


      --
      -- Execute resubmit_failed_task to resubmit failed task.
      -- To be executed on hourly basis
      --

      BEGIN
        EM_TASK.resubmit_failed_task();
      EXCEPTION
        WHEN OTHERS THEN
          NULL;
      END;


    END IF;

    -- Run this once a day at mid night.
    IF (to_char(v_proc_start_date, 'HH24:MI') = '00:00') THEN 

      -- Purge policies job
      BEGIN
        EM_PURGE.APPLY_PURGE_POLICIES();
      EXCEPTION 
        WHEN OTHERS THEN
          NULL;
      END;

      -- Job engine proc
      BEGIN
        MGMT_JOB_ENGINE.apply_purge_policies();
      EXCEPTION 
        WHEN OTHERS THEN
          NULL;
      END;

   END IF;
   
    -- Run clear expired violation suppressions proc at 12:01 AM everyday.
    IF (to_char(v_proc_start_date, 'HH24:MI') = '00:01') THEN 

      -- Clear expired suppressions
      BEGIN
        EM_POLICY.CLEAR_EXPIRED_SUPPRESSIONS;
      EXCEPTION 
        WHEN OTHERS THEN
          NULL;
      END;
    END IF;

   IF (is_detailed_logging_enabled > 0) THEN
     MGMT_LOG.LOG_PERFORMANCE('MAINT EXEC DBMS JOB PROCS',
              MGMT_GLOBAL.ELAPSED_TIME_MSEC(l_start_timestmp, SYSTIMESTAMP),
              l_start_timestmp, 'Y', 'NONE', 0);
   END IF;

  END execute_em_dbms_job_procs;

  PROCEDURE remove_em_dbms_jobs 
  IS
  l_error VARCHAR2(100) := NULL;
  l_error_count NUMBER := 0 ;
  BEGIN
    SAVEPOINT remove_em_dbms_jobs_start ;
    FOR crec IN (select job, what FROM user_jobs
                  where what in (
                                 'MGMT_GENSVC_AVAIL.EXEC_AVAIL_JOB();',
                                 'EMD_E2E_ROLLUP.ROLLUP();',
                                 'EMD_EVALUATION.EVALSQLMETRICS();',
                                 'EMD_LOADER.ROLLUP();',
                                 'EMD_NOTIFICATION.CHECK_FOR_SEVERITIES();',
                                 'EMD_RT_ROLLUP.ROLLUP();',
                                 'EM_PING.MARK_NODE_STATUS();',
                                 'EM_PURGE.APPLY_PURGE_POLICIES();',
                                 'EM_SEVERITY_REPOS.EXECUTE_REPOS_SEVERITY_EVAL();',
                                 'MGMT_JOB_ENGINE.apply_purge_policies();',
                                 'EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();',
                                 'EM_POLICY.CLEAR_EXPIRED_SUPPRESSIONS;',
                                 'MGMT_RCA.PURGE_RCA_RESULTS();',
                                 'EM_TASK.RESUBMIT_FAILED_TASK();'
                                 )
                        OR what LIKE 'emd_maintenance.analyze_emd_schema%'
                        OR what LIKE 'EM_TASK.WORKER%'
                                 )
    LOOP
      BEGIN
        DBMS_JOB.REMOVE(crec.job);
      EXCEPTION
        WHEN OTHERS THEN
          IF SQLCODE != -23421 THEN
            l_error_count := l_error_count + 1 ;
            IF l_error IS NULL
            THEN
              l_error := SUBSTR(SQLERRM,1,100) ;
            END IF ;
          END IF;
      END;
   END LOOP;
   
   BEGIN
     DELETE MGMT_COLLECTION_WORKERS ;
   EXCEPTION
   WHEN OTHERS THEN NULL ;
   END ;

   BEGIN
      MGMT_BSLN.DELETE_BSLN_JOBS;
   EXCEPTION
      WHEN OTHERS THEN NULL;
   END;

   IF l_error_count > 0
   THEN
     ROLLBACK TO SAVEPOINT remove_em_dbms_jobs_start ;
     raise_application_error(MGMT_GLOBAL.UNEXPECTED_ERR,
       l_error_count||' error(s) when dropping dbms_jobs '||
         ',first error='||l_error) ;
   END IF ;

  END remove_em_dbms_jobs;

  -- The following procedure is used to move em repository
  -- from one table space to another.

  PROCEDURE move_em_tblspc(dest_tbs_in IN VARCHAR2)
  IS
    l_source_tbs VARCHAR2(30);
    l_dest_tbs   VARCHAR2(30);

    TYPE p_TableNameList IS TABLE OF VARCHAR2(30)
       INDEX BY BINARY_INTEGER;
    TYPE p_IndexNameList IS TABLE OF VARCHAR2(30)
       INDEX BY BINARY_INTEGER;

    CURSOR table_names(v_tablespace_name VARCHAR2) IS
      SELECT table_name
        FROM USER_TABLES
       WHERE tablespace_name = v_tablespace_name
         AND iot_name IS NULL;

    CURSOR index_names(v_tablespace_name VARCHAR2) IS
      SELECT index_name
        FROM USER_INDEXES
       WHERE index_type = 'NORMAL' 
         AND status = 'VALID' 
	 AND tablespace_name = v_tablespace_name; 

    l_table_names p_TableNameList;
    l_index_names p_IndexNameList;
  BEGIN
    -- Check that dest_tbs_in exists..
    BEGIN
      SELECT tablespace_name INTO l_dest_tbs
        FROM user_tablespaces
       WHERE tablespace_name=upper(dest_tbs_in);

    EXCEPTION
      WHEN OTHERS THEN
        raise_application_error(-20001, 'Destination tablespace ' || 
	    dest_tbs_in || ' does not exists.');
    END;

    -- Get source tablespace
    BEGIN
      SELECT tablespace_name INTO l_source_tbs
        FROM user_tables
       WHERE table_name = 'MGMT_TARGETS';
    EXCEPTION
      WHEN OTHERS THEN
        raise_application_error(-20002, 'Error getting source tablespace name.');
    END;

    -- Check source_tbs != dest_tbs
    IF (l_source_tbs = l_dest_tbs) THEN
        raise_application_error(-20003, 'Source and destination tablespaces are same.');
    END IF;

    -- Move schema to destination tablespace

    -- Get list of tables to move..
    OPEN table_names(l_source_tbs);
    FETCH table_names BULK COLLECT INTO l_table_names;
    CLOSE table_names;

    IF l_table_names.COUNT != 0 THEN
      FOR i IN l_table_names.FIRST..l_table_names.LAST LOOP
	BEGIN
	  -- Move the table.
	  -- This will fail if the table has LONG RAW columns
	  -- Currently the following tables have LONG RAW columns..
          --   MGMT_PORTLET_PREFERENCE_STORE
          --   MIGRATE_EVENT_DETAILS
          --   MIGRATE_PREFERRED_CREDENTIALS
          execute immediate 'ALTER TABLE ' || 
              em_check.enquote_name(l_table_names(i)) || 
                            ' MOVE TABLESPACE ' || 
              em_check.enquote_name(l_dest_tbs);
        EXCEPTION
	  WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error while moving table ' || 
                    l_table_names(i) || '. ERROR = ' || SQLERRM);
	    
	END;
      END LOOP;
    END IF;

    -- Recreate indexes..
    OPEN index_names(l_source_tbs);
    FETCH index_names BULK COLLECT INTO l_index_names;
    CLOSE index_names;

    IF (l_index_names.COUNT > 0) THEN
      FOR j IN l_index_names.FIRST..l_index_names.LAST LOOP
	BEGIN
	  -- Move the index.
	  -- This will fail if the index key length is greater 
          execute immediate 'alter index ' || 
             em_check.enquote_name(l_index_names(j))||
	        ' rebuild online nologging tablespace ' || 
             em_check.enquote_name(l_dest_tbs);
        EXCEPTION 
	  WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error while moving index ' || 
                l_index_names(j) || '. ERROR = ' || SQLERRM);
	  
	END;

      END LOOP;
    END IF;

  END move_em_tblspc;

  -- Get repository version 
  FUNCTION get_version(p_comp_name VARCHAR2 DEFAULT 'CORE')  
    RETURN VARCHAR2 IS
    l_comp_ver MGMT_VERSIONS.version%TYPE;
  BEGIN

    SELECT version INTO l_comp_ver
      FROM MGMT_VERSIONS
     WHERE component_name = UPPER(p_comp_name);

    RETURN l_comp_ver;

  END get_version;

  -- Get repository mode
  FUNCTION get_mode(p_comp_name VARCHAR2 DEFAULT 'CORE')  
    RETURN VARCHAR2 IS
    l_comp_mode MGMT_VERSIONS.component_mode%TYPE;
  BEGIN

    SELECT component_mode INTO l_comp_mode
      FROM MGMT_VERSIONS
     WHERE component_name = UPPER(p_comp_name);

    RETURN l_comp_mode;
  END get_mode;

  FUNCTION is_central_mode(p_comp_name VARCHAR2 DEFAULT 'CORE')
    RETURN NUMBER IS
  BEGIN
    RETURN(EMD_MAINT_UTIL.is_central_mode(p_comp_name)) ;
  END is_central_mode;

  FUNCTION get_comp_status(p_comp_name VARCHAR2 DEFAULT 'CORE')
    RETURN NUMBER IS
    l_comp_status MGMT_VERSIONS.status%TYPE;
  BEGIN
    SELECT status INTO l_comp_status
      FROM MGMT_VERSIONS
     WHERE component_name = UPPER(p_comp_name);

    RETURN l_comp_status;
  END get_comp_status;


  PROCEDURE set_version(p_comp_name   VARCHAR2 DEFAULT 'CORE',
                        p_version     VARCHAR2 DEFAULT NULL,
			p_compat_ver  VARCHAR2 DEFAULT NULL,
			p_comp_mode   VARCHAR2 DEFAULT NULL,
                        p_comp_status NUMBER   DEFAULT G_STATUS_INITIALIZED) IS
    l_version        MGMT_VERSIONS.version%TYPE;
    l_compat_version MGMT_VERSIONS.compat_core_version%TYPE;
    l_comp_mode      MGMT_VERSIONS.component_mode%TYPE;
  BEGIN
    -- Default version 
    IF (p_version IS NULL) THEN
      l_version := G_VERSION;
    ELSE
      l_version := p_version;
    END IF;

    IF (p_compat_ver IS NULL) THEN
      l_compat_version := G_COMPAT_VERSION;
    ELSE
      l_compat_version := p_compat_ver;
    END IF;

    IF (p_comp_mode IS NULL) THEN
      l_comp_mode := G_COMPONENT_MODE_CENTRAL;
    ELSE
      l_comp_mode := p_comp_mode;
    END IF;

    UPDATE MGMT_VERSIONS
       SET version = l_version,
           compat_core_version = l_compat_version,
	   component_mode = l_comp_mode,
	   status         = p_comp_status
     WHERE component_name = p_comp_name;

    IF (SQL%ROWCOUNT = 0) THEN
      INSERT INTO MGMT_VERSIONS
          (component_name, version, compat_core_version, component_mode, status)
        VALUES 
	  (p_comp_name, l_version, l_compat_version, l_comp_mode, p_comp_status);
    END IF;

  END set_version;
  

  PROCEDURE set_comp_status(p_comp_name   VARCHAR2 DEFAULT 'CORE',
                            p_comp_status NUMBER   DEFAULT G_STATUS_INITIALIZED) IS
  BEGIN

    UPDATE MGMT_VERSIONS
       SET status         = p_comp_status
     WHERE component_name = p_comp_name;

  END set_comp_status;


  FUNCTION  get_sdk_list
      RETURN EM_SDK_COMPS IS
    l_sdk_list   EM_SDK_COMPS;
  BEGIN

    l_sdk_list := EM_SDK_COMPS (
      EM_SDK_COMP('MGMT_METRICS',                'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_TARGETS',                'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_TYPE_PROPERTIES',        'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_TARGET_PROPERTIES',      'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_METRICS_RAW',            'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_CURRENT_METRICS',        'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_STRING_METRIC_HISTORY',  'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_LONG_TEXT',              'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_METRICS_COMPOSITE_KEYS', 'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_METRICS_1HOUR',          'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_METRICS_1DAY',           'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_METRIC_ERRORS',          'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_CURRENT_METRIC_ERRORS',  'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_SEVERITY',               'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_CURRENT_SEVERITY',       'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_AVAILABILITY',           'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_CURRENT_AVAILABILITY',   'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_METRIC_COLLECTIONS',     'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_COLLECTION_PROPERTIES',  'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_METRIC_THRESHOLDS',      'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_TARGET_ASSOCS',          'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_JOB',                    'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_JOB_PARAMETER',          'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_JOB_TARGET',             'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_JOB_SCHEDULE',           'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_JOB_EXECPLAN',           'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_JOB_EXEC_SUMMARY',       'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_JOB_OUTPUT',             'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_JOB_EXECUTION',          'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_DELTA_IDS',              'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_DELTA_ID_VALUES',        'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_DELTA_ENTRY',            'TABLE', 'SELECT'),
      EM_SDK_COMP('MGMT_DELTA_ENTRY_VALUES',     'TABLE', 'SELECT'),

      -- Types
      EM_SDK_COMP('MGMT_SEVERITY_OBJ',           'TYPE',  'EXECUTE'),
      EM_SDK_COMP('MGMT_SEVERITY_ARRAY',         'TYPE',  'EXECUTE'),
      EM_SDK_COMP('MGMT_GUID_OBJ',               'TYPE',  'EXECUTE'),
      EM_SDK_COMP('MGMT_GUID_ARRAY',             'TYPE',  'EXECUTE'),
      EM_SDK_COMP('MGMT_DELTA_VALUE',            'TYPE',  'EXECUTE'),
      EM_SDK_COMP('MGMT_DELTA_VALUES',           'TYPE',  'EXECUTE'),
      EM_SDK_COMP('MGMT_NAME_VALUE',             'TYPE',  'EXECUTE'),
      EM_SDK_COMP('MGMT_NAME_VALUES',            'TYPE',  'EXECUTE'),
      EM_SDK_COMP('SMP_EMD_NVPAIR',              'TYPE',  'EXECUTE'),
      EM_SDK_COMP('SMP_EMD_NVPAIR_ARRAY',        'TYPE',  'EXECUTE'),
      EM_SDK_COMP('SMP_EMD_STRING_ARRAY',        'TYPE',  'EXECUTE'),
      EM_SDK_COMP('SMP_EMD_INTEGER_ARRAY',       'TYPE',  'EXECUTE'),
      EM_SDK_COMP('SMP_EMD_AVAIL_OBJ',           'TYPE',  'EXECUTE'),
      EM_SDK_COMP('SMP_EMD_TARGET_OBJ',          'TYPE',  'EXECUTE'),
      EM_SDK_COMP('SMP_EMD_TARGET_OBJ_ARRAY',    'TYPE',  'EXECUTE'),

      -- Packages
      EM_SDK_COMP('MGMT_ADMIN',                  'PACKAGE', 'EXECUTE'),
      EM_SDK_COMP('MGMT_CREDENTIAL',             'PACKAGE', 'EXECUTE'),
      EM_SDK_COMP('MGMT_GLOBAL',                 'PACKAGE', 'EXECUTE'),
      EM_SDK_COMP('MGMT_JOBS',                   'PACKAGE', 'EXECUTE'),
      EM_SDK_COMP('MGMT_LOG',                    'PACKAGE', 'EXECUTE'),
      EM_SDK_COMP('MGMT_PREFERENCES',            'PACKAGE', 'EXECUTE'),
      EM_SDK_COMP('MGMT_TARGET',                 'PACKAGE', 'EXECUTE'),
      EM_SDK_COMP('MGMT_USER',                   'PACKAGE', 'EXECUTE'),

      EM_SDK_COMP('MGMT_DELTA',                  'PACKAGE', 'EXECUTE'),
      EM_SDK_COMP('ECM_UTIL',                    'PACKAGE', 'EXECUTE'),
      EM_SDK_COMP('EMD_MNTR',                    'PACKAGE', 'EXECUTE')
    );

    RETURN l_sdk_list;

  END get_sdk_list;


  PROCEDURE create_sdk_synonyms
  IS
    l_sdk_list   EM_SDK_COMPS;
    err_no       NUMBER;
    l_object_name all_objects.object_name%TYPE ;
  BEGIN

    -- Create public synonyms for SDK tables/types/packages.
    l_sdk_list := get_sdk_list;
    FOR i IN 1..l_sdk_list.count LOOP
      BEGIN
        EXECUTE IMMEDIATE 'CREATE PUBLIC SYNONYM ' || 
             em_check.enquote_name(l_sdk_list(i).name) ||
	    ' FOR ' || em_check.enquote_name(l_sdk_list(i).name) ;

      EXCEPTION 
        WHEN OTHERS THEN
	  err_no := SQLCODE;
	  IF err_no = -955
	  THEN
            DBMS_OUTPUT.PUT_LINE('ERROR CREATING PUBLIC SYNONYM ' || 
	      l_sdk_list(i).name);
          ELSE
            DBMS_OUTPUT.PUT_LINE('ERROR CREATING PUBLIC SYNONYM ' || 
	      l_sdk_list(i).name || '. ERROR = ' || SQLERRM);
          END IF;
	  -- continue creating other public synonyms
      END;
    END LOOP;

    -- Create public synonyms for views
    FOR crec IN (SELECT object_name from all_objects
                  WHERE object_name like 'MGMT$%'
                    AND object_type='VIEW')
    LOOP
      BEGIN
        l_object_name := crec.object_name;

        EXECUTE IMMEDIATE 'CREATE PUBLIC SYNONYM ' || 
              em_check.enquote_name(l_object_name) ||
	    ' FOR ' || em_check.enquote_name(l_object_name);

      EXCEPTION 
        WHEN OTHERS THEN
	  
          DBMS_OUTPUT.PUT_LINE('ERROR CREATING PUBLIC SYNONYM ' || 
	      crec.object_name);
	  -- continue creating other public synonyms
      END;
    END LOOP;

  END create_sdk_synonyms;


  PROCEDURE grant_core_sdk
      (new_user_name_in VARCHAR2)
  IS
    l_current_user VARCHAR2(256) := MGMT_USER.GET_CURRENT_EM_USER;
    l_sdk_list   EM_SDK_COMPS;
    l_new_user_name_in VARCHAR2(30) := new_user_name_in;
  BEGIN

    -- Ensure that the caller is a super-user.
    IF MGMT_USER.has_priv(l_current_user, 'SUPER_USER') = 0 THEN
        raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR,
         'Only superusers can use this procedure');
    END IF;

    COMMIT;

    l_sdk_list := get_sdk_list;

    -- grant privileges for SDK tables/types/packages
    FOR i IN 1..l_sdk_list.count LOOP
      BEGIN
        EXECUTE IMMEDIATE 'GRANT ' || 
               em_check.simple_sql_name(l_sdk_list(i).grant_type) ||
            ' ON ' || em_check.enquote_name(l_sdk_list(i).name) || 
            ' TO ' || em_check.enquote_name(l_new_user_name_in) ||
            ' WITH GRANT OPTION';
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('ERROR GRANTING ' || SQLERRM);
          -- Keep granting other privileges..
          NULL;
      END;

    END LOOP;

    -- Grant select privileges for views that start with MGMT$
    FOR crec IN (SELECT object_name from all_objects
                  WHERE object_name like 'MGMT$%'
                    AND object_type='VIEW')
    LOOP
      BEGIN
	EXECUTE IMMEDIATE 'GRANT SELECT ON ' || 
              em_check.enquote_name(crec.object_name)||
	    ' TO ' || em_check.enquote_name(l_new_user_name_in) ||
            ' WITH GRANT OPTION';

      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('ERROR GRANTING SELECT ON VIEW ' || SQLERRM);
      END;
    END LOOP;
  END grant_core_sdk;
--
-- This procedure will prepopulate partitions for the table specified to
-- number of days specified.
--
PROCEDURE add_partitions(v_table_name IN VARCHAR2, 
                         v_max_days IN PLS_INTEGER, 
                         v_day_partitions IN BOOLEAN) IS

  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);

  v_maxPartition   DATE;
  v_partitionDate  DATE;
  v_tempDate       DATE;
  v_partitionAdded BOOLEAN := FALSE;
  v_newMaxDate     DATE := TRUNC(SYSDATE+v_max_days,'DD');
  v_daysBetween    PLS_INTEGER := 0;
  v_startDay       PLS_INTEGER := 1; 
  v_starthour      PLS_INTEGER := 0; 
  can_not_access_overflow_table EXCEPTION;
  PRAGMA EXCEPTION_INIT(can_not_access_overflow_table, -25191);
BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'Oracle Enterprise Manager.Add Partitions',
    action_name => 'start');

  IF NOT (PARTITIONS_ENABLED(v_table_name)) THEN
    RETURN; 
  END IF;

  OPEN max_timestamp_cursor(v_table_name);
  FETCH max_timestamp_cursor INTO v_maxPartition;
  CLOSE max_timestamp_cursor;

  IF v_day_partitions THEN
    v_daysBetween := ROUND((v_newMaxDate - v_maxPartition),0);
  ELSE
    v_daysBetween := CEIL(v_newMaxDate-(1/24)-v_maxPartition) ;
    -- if the last partition before is not  23:00 hours, then
    -- start partition creation from the next hour
    -- from previous date
    IF to_number(to_char(v_maxPartition,'HH24')) != 23
    THEN
      v_maxPartition := v_maxPartition -1 ;
      v_startHour := to_number(to_char(v_maxPartition,'HH24'))+1;
    END IF ;
  END IF ; 

  -- 
  -- This loop creates the new partitions, if necessary.
  --
  FOR x IN v_startDay..v_daysBetween LOOP
    v_partitionDate := TRUNC(v_maxPartition+x, 'DD');
    --
    -- The table is partitioned by hours.
    --
    IF NOT (v_day_partitions) THEN
      FOR y IN v_startHour..23 LOOP
        v_tempDate := v_partitionDate + (y/24);
        EXECUTE IMMEDIATE 'ALTER TABLE ' || 
           EM_CHECK.enquote_name(v_table_name) || 
           ' ADD PARTITION ' || 
            EM_CHECK.enquote_name(TO_CHAR(v_tempDate, 'YYYY-MM-DD HH24:MI')) ||
           ' VALUES LESS THAN (TO_DATE(' ||
          EM_CHECK.ENQUOTE_LITERAL(TO_CHAR(v_tempDate, 'YYYY-MM-DD HH24:MI')) ||
           ', ''YYYY-MM-DD HH24:MI''))';
      END LOOP;
      -- for the next day onwards, start from 0
      v_startHour := 0 ;
    ELSE  -- The table is partitioned by whole days.
      EXECUTE IMMEDIATE 'ALTER TABLE ' || 
         EM_CHECK.ENQUOTE_NAME(v_table_name)|| 
         ' ADD PARTITION ' || 
        EM_CHECK.ENQUOTE_NAME(TO_CHAR(v_partitionDate, 'YYYY-MM-DD HH24:MI')) ||
         ' VALUES LESS THAN (TO_DATE(' || 
     EM_CHECK.ENQUOTE_LITERAL(TO_CHAR(v_partitionDate, 'YYYY-MM-DD HH24:MI')) ||
           ', ''YYYY-MM-DD HH24:MI''))';
    END IF;
    v_partitionAdded := TRUE;      
  END LOOP; -- End v_daysBetween LOOP

  DBMS_APPLICATION_INFO.SET_MODULE(
         module_name => ' ',
         action_name => ' ');

EXCEPTION
  WHEN OTHERS THEN
    IF max_timestamp_cursor%ISOPEN THEN
      CLOSE max_timestamp_cursor;
    END IF;
    p_ErrMsg := SUBSTR(SQLERRM, 1, 2048);
    DBMS_APPLICATION_INFO.READ_MODULE(p_Module,p_Action);
    MGMT_LOG.LOG_ERROR('MAINT', null, 'Error during ' || p_Module || ' : ' ||
       p_ErrMsg,p_Action);
END ADD_PARTITIONS;
--
--  This procedure will drop any partitions for the input table that
--  are older than the input number of days from the current day.
--
PROCEDURE DROP_PARTITIONS (v_table_name IN VARCHAR2, 
                           v_retention_days IN PLS_INTEGER) IS

  CURSOR old_partition_cursor (c_table_name VARCHAR2, c_keep_date DATE) IS
    SELECT PARTITION_NAME
    FROM USER_TAB_PARTITIONS
    WHERE TABLE_NAME = UPPER(c_table_name)
    AND PARTITION_NAME < TO_CHAR(c_keep_date,'YYYY-MM-DD HH24:MI')
    ORDER BY PARTITION_NAME DESC;

  v_partitionDate  DATE;
  v_keepDate       DATE;
  v_part_names     mgmt_medium_string_table := mgmt_medium_string_table() ;

BEGIN

  v_keepDate := TRUNC((SYSDATE - v_retention_days), 'HH24');

  IF NOT (PARTITIONS_ENABLED(v_table_name)) THEN
    RETURN; 
  END IF;
  -- Doing bulk collect up front to avoid snapshot too old
  OPEN old_partition_cursor(v_table_name,v_keepDate) ;
  FETCH old_partition_cursor BULK COLLECT INTO v_part_names ;
  CLOSE old_partition_cursor ;

  IF v_part_names IS NULL or v_part_names.COUNT = 0
  THEN
    RETURN ;
  END IF ;

  FOR part_rec IN v_part_names.FIRST..v_part_names.LAST
  LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE '||
             EM_CHECK.ENQUOTE_NAME(v_table_name)
        ||' DROP PARTITION ' || 
             EM_CHECK.ENQUOTE_NAME(v_part_names(part_rec),FALSE) ;
  END LOOP;

EXCEPTION
  WHEN OTHERS THEN
    p_ErrMsg := SUBSTR(SQLERRM, 1, 2048);
    DBMS_APPLICATION_INFO.READ_MODULE(p_Module,p_Action);
    MGMT_LOG.LOG_ERROR('MAINT', null, 'Error during ' || p_Module || ' : ' ||
       p_ErrMsg,p_Action);
END DROP_PARTITIONS ;
--
--  This manual procedure will drop any partitions for the partitioned base
--  tables that are older than the keep dates.  It is expected that the central
--  OMS(s) will be down and the central repository will be quiet before and 
--  during the the execution of this procedure.
--
-- Starting 102DB, we do not need to stop job before dropping
-- partition. We would be calling this from analyze_emd_schema to drop partition
-- with p_stop_job set to FALSE, if DB version is >= 10.2
PROCEDURE PARTITION_MAINTENANCE
(
  p_stop_job BOOLEAN DEFAULT TRUE
) IS

BEGIN
  EMD_MAINT_UTIL.PARTITION_MAINTENANCE(p_stop_job) ;
END PARTITION_MAINTENANCE;
--
--  This procedure will truncate any partitions for the input table that
--  are older than the input number of days from the current day.
--
PROCEDURE TRUNCATE_PARTITIONS (v_table_name IN VARCHAR2, 
                               v_retention_days IN PLS_INTEGER) IS

  CURSOR old_partition_cursor (c_table_name VARCHAR2, c_keep_date DATE,
                               v_last_partition VARCHAR2) IS
    SELECT PARTITION_NAME
    FROM USER_TAB_PARTITIONS
    WHERE TABLE_NAME = UPPER(c_table_name)
    AND PARTITION_NAME < TO_CHAR(c_keep_date,'YYYY-MM-DD HH24:MI')
    AND ( v_last_partition IS NULL OR
          PARTITION_NAME > v_last_partition )
    ORDER BY PARTITION_NAME ;

  v_partitionDate  DATE;
  v_keepDate       DATE;
  v_last_partition VARCHAR2(256) ;
  v_curr_partition VARCHAR2(256) ;
  v_part_names     mgmt_medium_string_table := mgmt_medium_string_table() ;
BEGIN

  v_keepDate := TRUNC((SYSDATE - v_retention_days), 'HH24');

  IF NOT (PARTITIONS_ENABLED(v_table_name)) THEN
    RETURN; 
  END IF;
  BEGIN
    SELECT parameter_value
      INTO v_last_partition
      FROM mgmt_parameters 
    WHERE parameter_name= v_table_name||g_trunc_suffix ;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN NULL ;
  END ;   
  -- Doing bulk collect up front to avoid snapshot too old
  OPEN old_partition_cursor(v_table_name,v_keepDate,v_last_partition) ;
  FETCH old_partition_cursor BULK COLLECT INTO v_part_names ;
  CLOSE old_partition_cursor ;
  IF v_part_names IS NULL or v_part_names.COUNT = 0
  THEN
    RETURN ;
  END IF ;
  FOR part_rec IN v_part_names.FIRST..v_part_names.LAST
  LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE '|| EM_CHECK.ENQUOTE_NAME(v_table_name)
       ||' TRUNCATE PARTITION ' || 
            EM_CHECK.ENQUOTE_NAME(v_part_names(part_rec)) || ' DROP STORAGE';

    v_curr_partition := v_part_names(part_rec) ;
  END LOOP;
  upd_trunc_partition(v_table_name,v_curr_partition) ;
EXCEPTION
  WHEN OTHERS THEN
    p_ErrMsg := SUBSTR(SQLERRM, 1, 2048);
    DBMS_APPLICATION_INFO.READ_MODULE(p_Module,p_Action);
    MGMT_LOG.LOG_ERROR('MAINT', null, 'Error during ' || p_Module || ' : ' ||
       p_ErrMsg,p_Action);
END TRUNCATE_PARTITIONS;
--
-- Determine whether or not partitioning is in use for the EM repository.
-- Returns true if partitions enabled, false otherwise.
--
FUNCTION PARTITIONS_ENABLED(v_table_name IN VARCHAR2) RETURN BOOLEAN IS
    v_partitioned VARCHAR2(3);
BEGIN
  RETURN(EMD_MAINT_UTIL.PARTITIONS_ENABLED(v_table_name)) ;
END PARTITIONS_ENABLED;

--
-- Determine the database version of the repository.
-- Returns true if the rep database version is higher than 9, false otherwise.
-- Since the earliest repository for this version of EM repository is 9 
-- (lowest supported version) all we need to do is verify if the repository
-- is v9 or not.  If not, it has to be 10g or higher.
--
FUNCTION REPOSITORY_DB_V9 RETURN BOOLEAN IS
    v_version VARCHAR2(3);
BEGIN
    --
    -- Get the first digit of the db version from the v$instance table.
    -- 
    SELECT substr(version,1,1) INTO v_version
        FROM v$instance;
     
    IF v_version = '9' THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;

END REPOSITORY_DB_V9;


--
-- Returns TRUE of the DB version is 9.0  or 9.1 ELSE FALSE
--
FUNCTION REPOSITORY_DB_V91 RETURN BOOLEAN IS
    v_version VARCHAR2(3);
    v_minor_version VARCHAR2(3) ;
BEGIN
    --
    -- Get the first/third digit of the db version from the v$instance table.
    -- If version is 9, then the 3rd digit would be 0,1 or 2, if version is 10
    -- then we do not care about 3rd digit
    -- 
    SELECT substr(version,1,1) ,
           substr(version,3,1)
      INTO v_version,
           v_minor_version
      FROM v$instance;

    IF v_version = '9' AND v_minor_version IN ('0','1')
    THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;

END REPOSITORY_DB_V91;
--
-- Returns TRUE of the DB version is 10 
--
FUNCTION REPOSITORY_DB_V10 RETURN BOOLEAN IS
    v_version VARCHAR2(3);
BEGIN

--
-- Get the first two/fourth digit of the db version from the v$instance table.
-- 
    SELECT substr(version,1,2) 
      INTO v_version
      FROM v$instance;

    IF (v_version = 10) THEN 
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
    
END REPOSITORY_DB_V10;

--
-- Returns TRUE of the DB version is 10.2 or higher
--
FUNCTION REPOSITORY_DB_V102 RETURN BOOLEAN IS
    v_version VARCHAR2(3);
    v_minor_version VARCHAR2(3) ;
BEGIN

    --
    -- Get the first two/fourth digit of the db version from the v$instance table.
    -- 
    SELECT substr(version,1,2) ,
           substr(version,4,1)
      INTO v_version,
           v_minor_version
      FROM v$instance;

    IF (v_version > 10) OR (v_version = '10' AND v_minor_version > 1) THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;

END REPOSITORY_DB_V102;

--
-- Procedure to compress indexes.
--
-- Parameters
--   rebuild_index_name - name of index to rebuild
--   rebuild_prefix_length - prefixe length to compress
--   do_online - do it online or not
--
--  If the index is already compressed with the given prefix length the procedure return
--  without atempting to compress the index.  
--
--  The procedure works with IOT's.  It will figure out what hte table name is and move it.
--
PROCEDURE compress_index(rebuild_index_name VARCHAR2, rebuild_prefix_length NUMBER, 
                         do_online BOOLEAN) IS
    current_compression   VARCHAR(8);
    current_prefix_length NUMBER;
    current_index_type    VARCHAR2(32);
    current_table_name    VARCHAR2(32);
    alter_sql_stmt        VARCHAR(128);
  BEGIN
    SELECT index_type, table_name, compression, NVL(prefix_length, 0) 
      INTO current_index_type, current_table_name, current_compression, 
           current_prefix_length
      FROM user_indexes WHERE index_name = UPPER(rebuild_index_name);

    -- if index is already compressed correctly return
    IF rebuild_prefix_length = current_prefix_length THEN
      RETURN;
    END IF;

    IF current_index_type = 'IOT - TOP' THEN
      --Temporarily returning  due to Bug:4527081
      RETURN ;
      -- if it is an IOT move the base table  
      alter_sql_stmt := 'ALTER TABLE ' || 
             EM_CHECK.ENQUOTE_NAME(current_table_name)
                         || ' MOVE ';
    ELSE
      alter_sql_stmt := 'ALTER INDEX ' || 
             EM_CHECK.ENQUOTE_NAME(rebuild_index_name) || ' REBUILD ';
    END IF;

    IF do_online = TRUE THEN
      alter_sql_stmt := alter_sql_stmt || 'ONLINE ';
    END IF;

    -- if prefix length is 0 uncompress table
    IF rebuild_prefix_length = 0 THEN
      alter_sql_stmt := alter_sql_stmt || 'NOCOMPRESS';
    ELSE
      alter_sql_stmt := alter_sql_stmt || 'COMPRESS ' || rebuild_prefix_length;
    END IF;

    EXECUTE IMMEDIATE alter_sql_stmt;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      MGMT_LOG.LOG_ERROR('COMPRESS INDEX', null, 'Index ' || rebuild_index_name 
                         || ' does not exist');
    WHEN OTHERS THEN
      MGMT_LOG.LOG_ERROR('COMPRESS INDEX', null, 'Error compressing index ' ||
                         rebuild_index_name || ' Error: ' || SUBSTR(SQLERRM, 1, 1600));
  END;

  PROCEDURE cleanup_deleted_targets
  IS
  BEGIN
   -- Delete all those records where delete complete time < SYSDATE - 5
   -- delete in batch of 5000
     DELETE 
       FROM MGMT_TARGETS_DELETE
      WHERE delete_complete_time < (SYSDATE - 5) 
        AND ROWNUM < 5001;

     WHILE (SQL%ROWCOUNT = 5000)
      LOOP  
        COMMIT;
        DELETE 
          FROM MGMT_TARGETS_DELETE
         WHERE delete_complete_time < (SYSDATE - 5) 
           AND ROWNUM < 5001;

      END LOOP;
 
     COMMIT;
  END cleanup_deleted_targets;  

  -- set the reschedule execs on new jobtype flag in MGMT_PARAMETERS
  PROCEDURE set_reschd_on_new_jobtype(p_enable BOOLEAN DEFAULT TRUE) IS
  l_value MGMT_PARAMETERS.parameter_value%TYPE := 'TRUE';
  BEGIN
    IF NOT p_enable THEN
      l_value := 'FALSE';
    END IF;

    INSERT INTO MGMT_PARAMETERS
           (parameter_name, parameter_value, parameter_comment, internal_flag)
    VALUES (RESCHEDULE_ON_NEW_JOBTYPE, l_value, RESCHEDULE_ON_NEW_JOBTYPE_CMT, 1);
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      UPDATE MGMT_PARAMETERS
         SET parameter_value = l_value
       WHERE parameter_name = RESCHEDULE_ON_NEW_JOBTYPE;
  END set_reschd_on_new_jobtype;

  -- check if central mode and set reschedule execs on new jobtype flag
  -- accordingly: central -> TRUE, others -> FALSE
  PROCEDURE calc_reschd_on_new_jobtype IS
  BEGIN
    set_reschd_on_new_jobtype(is_central_mode > 0);
  END calc_reschd_on_new_jobtype;

  -- remove the reschedule execs on new jobtype flag
  PROCEDURE clear_reschd_on_new_jobtype IS
  BEGIN
    DELETE FROM MGMT_PARAMETERS
    WHERE  parameter_name = RESCHEDULE_ON_NEW_JOBTYPE;
  END clear_reschd_on_new_jobtype;

  -- return whether the reschedule execs on new jobtype flag is set or not
  FUNCTION should_reschd_on_new_jobtype RETURN BOOLEAN IS
  l_value MGMT_PARAMETERS.parameter_value%TYPE := 'TRUE';
  BEGIN
    BEGIN
      SELECT parameter_value INTO l_value
        FROM MGMT_PARAMETERS
       WHERE parameter_name = RESCHEDULE_ON_NEW_JOBTYPE;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        NULL;
    END;

    -- TRUE is default, so check only if value is not FALSE
    RETURN (l_value != 'FALSE');
  END should_reschd_on_new_jobtype;
end emd_maintenance;
/

show errors;