Rem drv: <migrate type="schema_upgrade" version="10.2.0.3" />
Rem
Rem $Header: severity_schema_upgrade.sql 08-may-2007.06:54:22 bram Exp $
Rem
Rem severity_schema_upgrade.sql
Rem
Rem Copyright (c) 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      severity_schema_upgrade.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    bram        05/03/07 - Create Index-mgmt_current_violation_idx_05 only if not present
Rem    neearora    07/27/06 - project 21470. Added object 
Rem                           MGMT_VIOLATION_SUMMARY_REC 
Rem    bram        04/17/06 - Created
Rem

DECLARE
    l_count  NUMBER DEFAULT 0;
BEGIN
    SELECT count(*)
    INTO l_count
    FROM all_indexes
    WHERE index_name = 'MGMT_CURRENT_VIOLATION_IDX_05';

    IF (l_count > 0)
    THEN
        -- drop the index 
        EXECUTE IMMEDIATE 'DROP INDEX MGMT_CURRENT_VIOLATION_IDX_05';
   END IF;

   EXECUTE IMMEDIATE
         'CREATE INDEX MGMT_CURRENT_VIOLATION_IDX_05
          ON mgmt_current_violation (target_guid, violation_type, violation_level)
          STORAGE (FREELISTS 4) INITRANS 4';
END;
/

CREATE OR REPLACE TYPE mgmt_violation_summary_rec AS OBJECT
(
    target_guid          RAW(16),
    policy_guid          RAW(16),
    key_value            VARCHAR2(256),
    collection_timestamp DATE,
    violation_level      NUMBER,
    message              VARCHAR2(4000),
    message_nlsid        VARCHAR2(64),
    message_params       VARCHAR2(4000),
    value                NUMBER,
    string_value         VARCHAR2(1024)
);
/