Rem drv: <migrate type="schema_upgrade" version="10.2.0.1" pos="assoc/assoc_schema_upgrade.sql+"/>
Rem
Rem $Header: basic_schema_upgrade.sql 23-apr-2007.23:28:51 jsadras Exp $
Rem
Rem basic_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      basic_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    jsadras     04/23/07 - Backport jsadras_bug-5934433 from main
Rem    jsadras     04/03/07 - move last_sync_load_details to gc
Rem    rpinnama    10/02/05 - Drop delete trigger on mgmt_metrics 
Rem    chyu        08/23/05 - adding the is_renderable column for the 
Rem                           mgmt_metric_table 
Rem    chyu        07/31/05 - adding the default parameter values 
Rem    chyu        07/25/05 - modifying the upgrade header 
Rem    chyu        07/18/05 - adding the upgrade 
Rem                           header
Rem    neearora    07/13/05 - neearora_bug-4447004
Rem    neearora    07/07/05 - Created
Rem

DEFINE EM_TARGET_NAME_SIZE=256
DEFINE EM_TARGET_TYPE_SIZE=64
DEFINE EM_META_VER_SIZE=8
DEFINE EM_URL_SIZE=1024
DEFINE EM_CLASS_NAME_SIZE=64
DEFINE EM_CATEGORY_NAME_SIZE=64
DEFINE EM_DEFAULT_NLSID_SIZE=64


  -- Bug:4487552 Index added
Rem    
Rem Indices for MGMT_TARGET_AGENT_ASSOC table
Rem

CREATE UNIQUE INDEX MGMT_TARGET_AGENT_ASSOC_UK ON MGMT_TARGET_AGENT_ASSOC (agent_guid, target_guid);


-- Bug: 4448311/4314999 - Assoc count column has been moved to separate table
ALTER TABLE MGMT_TARGETS
 DROP COLUMN  last_assoc_load_time;

ALTER TABLE MGMT_TARGETS
 DROP COLUMN  assoc_count;

ALTER TABLE MGMT_METRICS
ADD (
    is_renderable              NUMBER(1)       DEFAULT 1
);

PROMPT Dropping metrics_delete trigger, if exists
DECLARE
  l_trig_cnt NUMBER := 0;
BEGIN
  SELECT COUNT(1) INTO l_trig_cnt FROM all_objects
   WHERE owner = '&&EM_REPOS_USER'
     AND object_type = 'TRIGGER'
     AND object_name = 'METRICS_DELETE';

  IF (l_trig_cnt > 0) THEN
    EXECUTE IMMEDIATE 'DROP TRIGGER METRICS_DELETE';
  END IF;

END;
/

-- Drop and recreate the mgmt_targets_idx_02 index
DROP INDEX MGMT_TARGETS_IDX_02;

CREATE INDEX MGMT_TARGETS_IDX_02
  ON MGMT_TARGETS
     (emd_url, target_type)
  INITRANS 4;