Rem drv: <create type="pkgbodys" />
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_maint_util_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/1 2008/09/26 14:26:37 jsadras Exp $
Rem
Rem admin_maint_util_pkgbody.sql
Rem
Rem Copyright (c) 2006, 2008, Oracle and/or its affiliates. All rights reserved.
Rem
Rem    NAME
Rem      admin_maint_util_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem    Maintenance Utility to do the following
Rem    1. Recompile Invalid Objects
Rem    2. Partition Maintenance
Rem    **Note: 
Rem    This Package spec should not depend on any EM related
Rem    Objects
Rem    The package body should not depend on any EM related
Rem    objects other than MGMT_VERSIONS, MGMT_LOG,
Rem    MGMT_PARAMETERS
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    jsadras     09/23/08 - Bug:7425931, add dbms_assert
Rem    edemembe    07/17/07 - Backport edemembe_bug-6157164 from main
Rem    edemembe    07/11/07 - Bug 6157164
Rem    minfan      05/23/07 - Bug 5894442
Rem    minfan      05/23/07 - Bug 5894442
Rem    minfan      05/23/07 - Bug 5894442
Rem    minfan      05/23/07 - Bug 5894442
Rem    rrawat      05/21/07 - Backport rrawat_bug-5502664 from main
Rem    rrawat      09/06/06 - Bug-5502664
Rem    jsadras     08/09/06 - Created
Rem

CREATE OR REPLACE PACKAGE BODY EMD_MAINT_UTIL AS 
p_ErrMsg                 VARCHAR(2048);
p_Module                 VARCHAR(128); 
p_Action                 VARCHAR(128);
--
-- Names of basic tables which have partition maintenance enabled
--
METRICS_RAW_TABLE    CONSTANT VARCHAR(30) := 'MGMT_METRICS_RAW';
METRICS_1HOUR_TABLE  CONSTANT VARCHAR(30) := 'MGMT_METRICS_1HOUR';
METRICS_1DAY_TABLE   CONSTANT VARCHAR(30) := 'MGMT_METRICS_1DAY';
-- Variable to control the retention in metrics_raw/1hour/1day tables
-- Days of partitions to keep
PART_RETENTION_RAW   CONSTANT PLS_INTEGER := 7;
PART_RETENTION_1HOUR CONSTANT PLS_INTEGER := 31;
PART_RETENTION_1DAY  CONSTANT PLS_INTEGER := 365;
 
-- Component modes
COMPONENT_MODE_CENTRAL    CONSTANT VARCHAR2(16) := 'CENTRAL';

MAX_RECOMPILES       CONSTANT NUMBER := 5 ;
--
-- Get central mode
--
FUNCTION is_central_mode
  (p_comp_name VARCHAR2 DEFAULT 'CORE')
RETURN NUMBER IS
l_is_central NUMBER := 0;
BEGIN
  SELECT COUNT(1) INTO l_is_central
    FROM MGMT_VERSIONS
   WHERE component_name = UPPER(p_comp_name)
     AND component_mode = COMPONENT_MODE_CENTRAL;

  RETURN l_is_central;

END is_central_mode;
--
-- Check if the table has partitioning enabled
--
FUNCTION partitions_enabled(v_table_name IN VARCHAR2) 
RETURN BOOLEAN
IS
 v_partitioned VARCHAR2(3);
BEGIN
  --
  -- Check for partitions on a core repository table.
  -- 
  SELECT decode(bitand(t.property, 32), 32, 'YES', 'NO') INTO v_partitioned
    FROM  sys.obj$ o,sys.tab$ t
   WHERE  o.owner# = USERENV('SCHEMAID')
    AND o.name = UPPER(v_table_name)
    AND o.obj#=t.obj#;

  IF v_partitioned = 'YES' 
  THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;

END partitions_enabled ;
---
--- Gets the retention period for fetching raw data from repository
---
FUNCTION GET_RAW_RETENTION_WINDOW   
RETURN NUMBER IS
l_custom_raw_window NUMBER;
l_dflt_value        NUMBER;
BEGIN
  l_custom_raw_window:=NULL;
  --   Get the default custom raw value, into l_dflt_value, based on mode..
  IF (is_central_mode > 0) 
  THEN
    l_dflt_value := PART_RETENTION_RAW;
  ELSE
    l_dflt_value := (DEFAULT_RAW_KEEP/24);
  END IF;

  BEGIN
    SELECT NVL(TO_NUMBER(parameter_value), l_dflt_value) 
      INTO l_custom_raw_window
      FROM mgmt_parameters
     WHERE parameter_name = RAW_KEEP_PARAM;

  EXCEPTION
  WHEN OTHERS THEN
    l_custom_raw_window := l_dflt_value;
  END;

  RETURN l_custom_raw_window;

END GET_RAW_RETENTION_WINDOW;
----------------------------------------------------------------------
---
--- Gets the retention period for fetching 1 hour data from repository
---
FUNCTION GET_1HOUR_RETENTION_WINDOW 
RETURN NUMBER IS
l_custom_hour_window NUMBER;
l_dflt_value         NUMBER;
BEGIN
  l_custom_hour_window:=NULL;
  --   Get the default custom 1hour value, into l_dflt_value, based on mode..
  IF (is_central_mode > 0) 
  THEN
    l_dflt_value := PART_RETENTION_1HOUR;
  ELSE
    l_dflt_value := (DEFAULT_HOUR_KEEP/24);
  END IF;

  BEGIN
    SELECT NVL(TO_NUMBER(parameter_value), l_dflt_value) INTO l_custom_hour_window
      FROM mgmt_parameters
     WHERE parameter_name = HOUR_KEEP_PARAM;

  EXCEPTION
  WHEN OTHERS THEN
    l_custom_hour_window := l_dflt_value;
  END;

  RETURN l_custom_hour_window;

END GET_1HOUR_RETENTION_WINDOW;
----------------------------------------------------------------------
---
--- Gets the retention period for fetching 1 day data from repository
---
FUNCTION GET_1DAY_RETENTION_WINDOW 
RETURN NUMBER IS
l_custom_day_window   NUMBER;
l_dflt_value          NUMBER;
BEGIN
  l_custom_day_window :=null;
   --   Get the default custom 1day value, into l_dflt_value, based on mode..
  IF (is_central_mode > 0) 
  THEN
    l_dflt_value := PART_RETENTION_1DAY;
  ELSE
    l_dflt_value := DEFAULT_DAY_KEEP;
  END IF;

  BEGIN
    SELECT NVL(TO_NUMBER(parameter_value), l_dflt_value) 
      INTO l_custom_day_window
      FROM mgmt_parameters
     WHERE parameter_name = DAY_KEEP_PARAM;

  EXCEPTION
  WHEN OTHERS THEN
    l_custom_day_window := l_dflt_value;
  END;

  RETURN l_custom_day_window;

END GET_1DAY_RETENTION_WINDOW;
--
-- Recreate Public Synonyms for views that start with MGMT$
-- and public synonyms for mgmt_metrics_raw,1hour,1day tables if present
--
PROCEDURE recreate_synonyms 
IS
BEGIN
  -- drop and recreate invalid public synonyms pointing to repository owner
  -- to make them valid.
  FOR rec IN ( SELECT syn.synonym_name,syn.table_name,syn.table_owner
                 FROM all_objects ao,
                      all_synonyms syn
                WHERE ao.status        = 'INVALID'
                  AND ao.owner         = 'PUBLIC'
                  AND ao.object_type   = 'SYNONYM'
                  AND syn.owner        = 'PUBLIC'
                  AND syn.table_owner  = USER
                  AND syn.synonym_name = ao.object_name)
  LOOP
    BEGIN
      EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM '||DBMS_ASSERT.ENQUOTE_NAME(rec.synonym_name,FALSE)
                         ||' FOR '|| DBMS_ASSERT.ENQUOTE_NAME(rec.table_owner,FALSE)||'.'||
                                     DBMS_ASSERT.ENQUOTE_NAME(rec.synonym_name,FALSE) ;
    EXCEPTION
      WHEN OTHERS THEN 
	MGMT_LOG.LOG_ERROR('MAINT', null, 'Error during recreate_synoyms:'||sqlerrm) ;
    END ;
  END LOOP ;
END recreate_synonyms;
--
-- Private procedure
-- Recompile the invalid objects
-- p_recompile_no contains the number of times this procedure has been called
-- if it is greater then MAX_RECOMPILE_TIMES then return
--
PROCEDURE recompile_invalid_objects
  (p_recompile_no IN NUMBER)
IS
l_invalid_count NUMBER ;
l_object_name all_objects.object_name%TYPE ;
BEGIN
  --
  -- If the recompiles have been done more than MAX_RECOMPILES
  -- times then exit
  --  
  IF p_recompile_no < 0 OR p_recompile_no > MAX_RECOMPILES
  THEN
    RETURN ;
  END IF ;

  dbms_utility.compile_schema(USER,FALSE);

  -- Compile any invalids left over by dbms_utility

  FOR crec IN (SELECT object_name, object_type,
                   DECODE(object_type, 'TYPE',              1,
                                       'FUNCTION',          2,
                                       'PROCEDURE',         3,
                                       'TRIGGER',           4,
                                       'VIEW',              5,
                                       'PACKAGE',           6,
                                       'TYPE BODY',         7,
                                       'PACKAGE BODY',      8,
                                       'MATERIALIZED VIEW', 9,
                                                           10) object_type_seq
                 FROM   all_objects
                WHERE  status ='INVALID'
                  AND  owner = USER
                  AND  object_type IN ('FUNCTION', 'PACKAGE',
                                       'PACKAGE BODY', 'PROCEDURE',
                                       'TRIGGER', 'VIEW', 'TYPE BODY', 
                                       'TYPE', 'MATERIALIZED VIEW')
                ORDER BY object_type_seq, object_type, created)
  LOOP
   BEGIN
      l_object_name := DBMS_ASSERT.ENQUOTE_NAME(crec.object_name,FALSE);

      IF (crec.object_type = 'PACKAGE BODY')
      THEN
        -- If package body is invalid, just compile the body and not
        -- the specification
        EXECUTE IMMEDIATE 'ALTER PACKAGE ' ||
                          l_object_name || ' compile body';
      ELSIF (crec.object_type = 'TYPE')
      THEN
        -- If type spec is invalid, just compile the specification and not
        -- the body
        EXECUTE IMMEDIATE 'ALTER TYPE ' ||
                          l_object_name || ' compile specification';
      ELSIF (crec.object_type = 'TYPE BODY')
      THEN
        -- If type body is invalid, just compile the body and not
        -- the specification
        EXECUTE IMMEDIATE 'ALTER TYPE ' ||
                           l_object_name || ' compile body';
      ELSIF (crec.object_type = 'MATERIALIZED VIEW')
      THEN
        EXECUTE IMMEDIATE 'ALTER MATERIALIZED VIEW '||l_object_name||' compile' ;
      ELSE
         EXECUTE IMMEDIATE 'ALTER ' || DBMS_ASSERT.SIMPLE_SQL_NAME(crec.object_type) || ' ' ||
                           l_object_name || ' compile';
      END IF;
    EXCEPTION
    WHEN OTHERS THEN
      NULL;  -- No action on failure.  Continue to next object.
    END;
  END LOOP;

  -- Check if there are any more invalids
  SELECT COUNT(*)
    INTO l_invalid_count
    FROM user_objects
   WHERE status = 'INVALID'
     AND object_type IN ('FUNCTION',     'PACKAGE',
                         'PACKAGE BODY', 'PROCEDURE',
                         'TRIGGER',      'VIEW',
                         'TYPE BODY', 'TYPE', 'MATERIALIZED VIEW');
  IF l_invalid_count > 0
  THEN
    -- recursively call recompile 
    recompile_invalid_objects(p_recompile_no+1);
  END IF ;  

EXCEPTION
-- No action on failure
WHEN OTHERS THEN NULL ;
END recompile_invalid_objects;
--
-- Recompile Invalid Objects
-- 
PROCEDURE recompile_invalid_objects
IS
BEGIN
  recompile_invalid_objects(1);
  -- recreate the invalid public synonyms
  recreate_synonyms ;
END recompile_invalid_objects;
-- Do Partition maintenance on the following tables
-- MGMT_METRICS_RAW
-- MGMT_METRICS_1HOUR
-- MGMT_METRICS_1DAY
--
PROCEDURE partition_maintenance
(
  p_stop_job BOOLEAN DEFAULT TRUE
) 
IS
   --
   -- Drop partitions for the table older than retention days
   -- This has been made a local procedure because the
   -- drop partitions accept table name as input, we need to
   -- call em_check procedure. By making this local, we are
   -- not accepting parameters from outside. 
   -- If we call em_check then em_check will become a dependency
   -- and we want to avoid dependencies
   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;
  
   TYPE local_string_table IS TABLE OF user_tab_partitions.partition_name%TYPE
   INDEX BY BINARY_INTEGER ;
 
   v_partitionDate  DATE;
   v_keepDate       DATE;
   v_part_names     local_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 '||v_table_name
              ||' DROP PARTITION "'
           ||v_part_names(part_rec) || '"';
     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 ;
BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'Oracle Enterprise Manager.Partition Maintenance',
    action_name => 'start');

  -- Starting 102DB, we do not need to stop job before dropping
  -- partition. 
  -- Stop/Remove all the EM jobs
  -- Execute Immediate is called instead of direct call to avoid
  -- making this package a dependent of emd_maintenance which gets
  -- invalidated by dropping of partitions
  -- 
  IF p_stop_job THEN
    EXECUTE IMMEDIATE 'CALL emd_maintenance.remove_em_dbms_jobs()';
  END IF;
  -- Drop old partitions

  --get_%_retention_window will get the retention days, if set in
  --mgmt_parameters else default value
  drop_partitions(metrics_raw_table, get_raw_retention_window);

  drop_partitions(metrics_1hour_table, get_1hour_retention_window);

  drop_partitions(metrics_1day_table, get_1day_retention_window);

  -- Recompile all the invalidated repository objects.
  recompile_invalid_objects;

  -- Submit all the EM jobs
  IF p_stop_job 
  THEN
    EXECUTE IMMEDIATE 'CALL emd_maintenance.submit_em_dbms_jobs() ' ;
  END IF;

  DBMS_APPLICATION_INFO.SET_MODULE(
         module_name => ' ',
         action_name => ' ');
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);
    DBMS_APPLICATION_INFO.SET_MODULE(
         module_name => ' ',
         action_name => ' ');
END partition_maintenance;

END  EMD_MAINT_UTIL ;
/