Rem DO NOT ADD HEADER FOR THIS FILE SINCE THIS FILE IS CONDITIONAL REFERENCED
Rem in the chronos_schema_upgrade_cond.sql
Rem
Rem $Header: chronos_schema_upgrade.sql 23-apr-2007.23:28:46 jsadras Exp $
Rem
Rem chronos_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      chronos_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/04/07 - Bug:5964374, handle ORA-01450 on 2k block size db
Rem    jsadras     04/23/07 - Backport jsadras_bug-5934433 from main
Rem    chyu        07/18/05 - adding the <migration> header 
Rem    yxie        07/06/05 - fix bug 4473366 
Rem    eporter     05/04/05 - Bug 4305869: Add MGMT_RT_BOOTSTRAP_TIMES index
Rem    eporter     04/14/05 - Fix difference with raw table 
Rem    eporter     03/29/05 - Add indexes for bootstrap tables 
Rem    eporter     02/28/05 - Add new rollup tables 
Rem    eporter     02/16/05 - eporter_bug-4167002
Rem    eporter     02/15/05 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100

-- Add database time fields to MGMT_RT_METRICS_RAW
ALTER TABLE &EM_REPOS_USER..MGMT_RT_METRICS_RAW ADD DATABASE_TIME NUMBER DEFAULT 0;
ALTER TABLE &EM_REPOS_USER..MGMT_RT_METRICS_RAW ADD ICX_SESSION_ID VARCHAR2(32) DEFAULT NULL;

-- Add database time fields to the rollup tables
ALTER TABLE &EM_REPOS_USER..MGMT_RT_URL_1HOUR 
  ADD (DB_TIME_AVERAGE  NUMBER DEFAULT 0,
       DB_TIME_MINIMUM  NUMBER DEFAULT 0,
       DB_TIME_MAXIMUM  NUMBER DEFAULT 0,
       DB_TIME_SDEV     NUMBER DEFAULT 0,
       DB_TIME_VARIANCE NUMBER DEFAULT 0);

ALTER TABLE &EM_REPOS_USER..MGMT_RT_DOMAIN_1HOUR 
  ADD (DB_TIME_AVERAGE  NUMBER DEFAULT 0,
       DB_TIME_MINIMUM  NUMBER DEFAULT 0,
       DB_TIME_MAXIMUM  NUMBER DEFAULT 0,
       DB_TIME_SDEV     NUMBER DEFAULT 0,
       DB_TIME_VARIANCE NUMBER DEFAULT 0);

ALTER TABLE &EM_REPOS_USER..MGMT_RT_IP_1HOUR 
  ADD (DB_TIME_AVERAGE  NUMBER DEFAULT 0,
       DB_TIME_MINIMUM  NUMBER DEFAULT 0,
       DB_TIME_MAXIMUM  NUMBER DEFAULT 0,
       DB_TIME_SDEV     NUMBER DEFAULT 0,
       DB_TIME_VARIANCE NUMBER DEFAULT 0);

ALTER TABLE &EM_REPOS_USER..MGMT_RT_URL_1DAY
  ADD (DB_TIME_AVERAGE  NUMBER DEFAULT 0,
       DB_TIME_MINIMUM  NUMBER DEFAULT 0,
       DB_TIME_MAXIMUM  NUMBER DEFAULT 0,
       DB_TIME_SDEV     NUMBER DEFAULT 0,
       DB_TIME_VARIANCE NUMBER DEFAULT 0);

ALTER TABLE &EM_REPOS_USER..MGMT_RT_DOMAIN_1DAY 
  ADD (DB_TIME_AVERAGE  NUMBER DEFAULT 0,
       DB_TIME_MINIMUM  NUMBER DEFAULT 0,
       DB_TIME_MAXIMUM  NUMBER DEFAULT 0,
       DB_TIME_SDEV     NUMBER DEFAULT 0,
       DB_TIME_VARIANCE NUMBER DEFAULT 0);

ALTER TABLE &EM_REPOS_USER..MGMT_RT_IP_1DAY 
  ADD (DB_TIME_AVERAGE  NUMBER DEFAULT 0,
       DB_TIME_MINIMUM  NUMBER DEFAULT 0,
       DB_TIME_MAXIMUM  NUMBER DEFAULT 0,
       DB_TIME_SDEV     NUMBER DEFAULT 0,
       DB_TIME_VARIANCE NUMBER DEFAULT 0);

-- Modify to be NOT NULL
ALTER TABLE &EM_REPOS_USER..MGMT_RT_URL_1HOUR 
  MODIFY (DB_TIME_AVERAGE  NOT NULL,
          DB_TIME_MINIMUM  NOT NULL,
          DB_TIME_MAXIMUM  NOT NULL,
          DB_TIME_SDEV     NOT NULL,
          DB_TIME_VARIANCE NOT NULL);

ALTER TABLE &EM_REPOS_USER..MGMT_RT_DOMAIN_1HOUR 
  MODIFY (DB_TIME_AVERAGE  NOT NULL,
          DB_TIME_MINIMUM  NOT NULL,
          DB_TIME_MAXIMUM  NOT NULL,
          DB_TIME_SDEV     NOT NULL,
          DB_TIME_VARIANCE NOT NULL);

ALTER TABLE &EM_REPOS_USER..MGMT_RT_IP_1HOUR 
  MODIFY (DB_TIME_AVERAGE  NOT NULL,
          DB_TIME_MINIMUM  NOT NULL,
          DB_TIME_MAXIMUM  NOT NULL,
          DB_TIME_SDEV     NOT NULL,
          DB_TIME_VARIANCE NOT NULL);

ALTER TABLE &EM_REPOS_USER..MGMT_RT_URL_1DAY
  MODIFY (DB_TIME_AVERAGE  NOT NULL,
          DB_TIME_MINIMUM  NOT NULL,
          DB_TIME_MAXIMUM  NOT NULL,
          DB_TIME_SDEV     NOT NULL,
          DB_TIME_VARIANCE NOT NULL);

ALTER TABLE &EM_REPOS_USER..MGMT_RT_DOMAIN_1DAY 
  MODIFY (DB_TIME_AVERAGE  NOT NULL,
          DB_TIME_MINIMUM  NOT NULL,
          DB_TIME_MAXIMUM  NOT NULL,
          DB_TIME_SDEV     NOT NULL,
          DB_TIME_VARIANCE NOT NULL);

ALTER TABLE &EM_REPOS_USER..MGMT_RT_IP_1DAY 
  MODIFY (DB_TIME_AVERAGE  NOT NULL,
          DB_TIME_MINIMUM  NOT NULL,
          DB_TIME_MAXIMUM  NOT NULL,
          DB_TIME_SDEV     NOT NULL,
          DB_TIME_VARIANCE NOT NULL);


CREATE TABLE &EM_REPOS_USER..MGMT_RT_BOOTSTRAP_TIMES
    (target_guid          RAW(16)    NOT NULL,
     rollup_timestamp     DATE       NOT NULL)
  MONITORING;

CREATE TABLE &EM_REPOS_USER..MGMT_RT_URL_BOOTSTRAP
    (target_guid                  RAW(16)         NOT NULL,
     metric_name                  VARCHAR2(64)    NOT NULL,
     url_filename                 VARCHAR2(1024)  NOT NULL,
     url_link                     VARCHAR2(1280)  DEFAULT ' ',
     rollup_timestamp             DATE            NOT NULL,
     hits                         NUMBER          NOT NULL,
     response_time_average        NUMBER          NOT NULL,
     response_time_minimum        NUMBER          NOT NULL,
     response_time_maximum        NUMBER          NOT NULL,
     response_time_sdev           NUMBER          NOT NULL,
     response_time_variance       NUMBER          NOT NULL,
     server_time_average          NUMBER          NOT NULL,
     server_time_minimum          NUMBER          NOT NULL,
     server_time_maximum          NUMBER          NOT NULL,
     server_time_sdev             NUMBER          NOT NULL,
     server_time_variance         NUMBER          NOT NULL,
     db_time_average              NUMBER          NOT NULL,
     db_time_minimum              NUMBER          NOT NULL,
     db_time_maximum              NUMBER          NOT NULL,
     db_time_sdev                 NUMBER          NOT NULL,
     db_time_variance             NUMBER          NOT NULL )
  PCTFREE 0
  MONITORING;

CREATE TABLE &EM_REPOS_USER..MGMT_RT_URL_DIST_BOOTSTRAP
    (target_guid                  RAW(16)         NOT NULL,
     metric_name                  VARCHAR2(64)    NOT NULL,
     url_filename                 VARCHAR2(1024)  NOT NULL,
     rollup_timestamp             DATE            NOT NULL,
     hits                         NUMBER          DEFAULT 0,
     num_seconds                  NUMBER          DEFAULT 0,
     dist_value_type              NUMBER(4)       DEFAULT 0)
  PCTFREE 0
  MONITORING;

CREATE TABLE &EM_REPOS_USER..MGMT_RT_DOMAIN_BOOTSTRAP
    (target_guid                  RAW(16)         NOT NULL,
     metric_name                  VARCHAR2(64)    NOT NULL,
     visitor_domain               VARCHAR2(1024)  NOT NULL,
     visitor_subnet               VARCHAR2(15)    NOT NULL,
     rollup_timestamp             DATE            NOT NULL,
     hits                         NUMBER          NOT NULL,
     response_time_average        NUMBER          NOT NULL,
     response_time_minimum        NUMBER          NOT NULL,
     response_time_maximum        NUMBER          NOT NULL,
     response_time_sdev           NUMBER          NOT NULL,
     response_time_variance       NUMBER          NOT NULL,
     server_time_average          NUMBER          NOT NULL,
     server_time_minimum          NUMBER          NOT NULL,
     server_time_maximum          NUMBER          NOT NULL,
     server_time_sdev             NUMBER          NOT NULL,
     server_time_variance         NUMBER          NOT NULL,
     visitor_subnet_num           NUMBER          NOT NULL,
     db_time_average              NUMBER          NOT NULL,
     db_time_minimum              NUMBER          NOT NULL,
     db_time_maximum              NUMBER          NOT NULL,
     db_time_sdev                 NUMBER          NOT NULL,
     db_time_variance             NUMBER          NOT NULL  )
  PCTFREE 0
  MONITORING;

CREATE TABLE &EM_REPOS_USER..MGMT_RT_DOMAIN_DIST_BOOTSTRAP
    (target_guid                  RAW(16)         NOT NULL,
     metric_name                  VARCHAR2(64)    NOT NULL,
     visitor_domain               VARCHAR2(1024)  NOT NULL,
     visitor_subnet               VARCHAR2(15)    NOT NULL,
     rollup_timestamp             DATE            NOT NULL,
     hits                         NUMBER          DEFAULT 0,
     num_seconds                  NUMBER          DEFAULT 0,
     dist_value_type              NUMBER(4)       DEFAULT 0,
     visitor_subnet_num           NUMBER          NOT NULL)
  PCTFREE 0
  MONITORING;

CREATE TABLE &EM_REPOS_USER..MGMT_RT_IP_BOOTSTRAP
    (target_guid                  RAW(16)         NOT NULL,
     metric_name                  VARCHAR2(64)    NOT NULL,
     visitor_node                 VARCHAR2(1024)  NOT NULL,
     rollup_timestamp             DATE            NOT NULL,
     hits                         NUMBER          NOT NULL,
     response_time_average        NUMBER          NOT NULL,
     response_time_minimum        NUMBER          NOT NULL,
     response_time_maximum        NUMBER          NOT NULL,
     response_time_sdev           NUMBER          NOT NULL,
     response_time_variance       NUMBER          NOT NULL,
     server_time_average          NUMBER          NOT NULL,
     server_time_minimum          NUMBER          NOT NULL,
     server_time_maximum          NUMBER          NOT NULL,
     server_time_sdev             NUMBER          NOT NULL,
     server_time_variance         NUMBER          NOT NULL,
     db_time_average              NUMBER          NOT NULL,
     db_time_minimum              NUMBER          NOT NULL,
     db_time_maximum              NUMBER          NOT NULL,
     db_time_sdev                 NUMBER          NOT NULL,
     db_time_variance             NUMBER          NOT NULL  )
  PCTFREE 0
  MONITORING;

CREATE TABLE &EM_REPOS_USER..MGMT_RT_IP_DIST_BOOTSTRAP
    (target_guid                  RAW(16)         NOT NULL,
     metric_name                  VARCHAR2(64)    NOT NULL,
     visitor_node                 VARCHAR2(1024)  NOT NULL,
     rollup_timestamp             DATE            NOT NULL,
     hits                         NUMBER          DEFAULT 0,
     num_seconds                  NUMBER          DEFAULT 0,
     dist_value_type              NUMBER(4)       DEFAULT 0)
  PCTFREE 0
  MONITORING;

-- PR Mapping table
CREATE TABLE &EM_REPOS_USER..MGMT_RT_PR_MAPPING
	(TARGET_GUID               RAW(16)         NOT NULL,
	 PAGE_URL                  VARCHAR2(1024)  NOT NULL,
	 REQUEST_URL               VARCHAR2(1024)  NOT NULL,
         NUM_CACHE_HITS            NUMBER          DEFAULT 0,              
         CACHE_HITS_AVG_SVR_TIME   NUMBER(10,2)    DEFAULT 0,
         NUM_NON_CACHE_HITS        NUMBER          DEFAULT 0,
         NON_CACHE_HITS_AVG_SVR_TIME NUMBER(10,2)  DEFAULT 0,
         AGGREGATE_HOUR_TIMESTAMP DATE);

-- Incomplete Loads table
CREATE TABLE &EM_REPOS_USER..MGMT_RT_INCOMPLETE_LOADS
        (TARGET_GUID RAW(16)                      NOT NULL,
         PAGE_URL VARCHAR2(1024)                  NOT NULL,
         NUM_INCOMPLETE_LOADS NUMBER              DEFAULT 0,
         AVG_SERVER_TIME NUMBER(10,2)             DEFAULT 0,
         AGGREGATE_HOUR_TIMESTAMP DATE);

CREATE TABLE &EM_REPOS_USER..MGMT_RT_PR_MAPPING_1HOUR
   (TARGET_GUID                 RAW(16)         NOT NULL,
    PAGE_URL                    VARCHAR2(1024)  NOT NULL,
    REQUEST_URL                 VARCHAR2(1024)  NOT NULL,
    NUM_CACHE_HITS              NUMBER          DEFAULT 0,              
    CACHE_HITS_AVG_SVR_TIME     NUMBER(10,2)    DEFAULT 0,
    NUM_NON_CACHE_HITS          NUMBER          DEFAULT 0,
    NON_CACHE_HITS_AVG_SVR_TIME NUMBER(10,2)    DEFAULT 0,
    ROLLUP_TIMESTAMP            DATE);

CREATE TABLE &EM_REPOS_USER..MGMT_RT_INCOMPLETE_LOADS_1HOUR
   (TARGET_GUID                RAW(16)         NOT NULL,
    PAGE_URL                   VARCHAR2(1024)  NOT NULL,
    NUM_INCOMPLETE_LOADS       NUMBER          DEFAULT 0,
    AVG_SERVER_TIME            NUMBER(10,2)    DEFAULT 0,
    ROLLUP_TIMESTAMP           DATE);

CREATE TABLE &EM_REPOS_USER..MGMT_RT_PR_MAPPING_1DAY
   (TARGET_GUID                 RAW(16)         NOT NULL,
    PAGE_URL                    VARCHAR2(1024)  NOT NULL,
    REQUEST_URL                 VARCHAR2(1024)  NOT NULL,
    NUM_CACHE_HITS              NUMBER          DEFAULT 0,              
    CACHE_HITS_AVG_SVR_TIME     NUMBER(10,2)    DEFAULT 0,
    NUM_NON_CACHE_HITS          NUMBER          DEFAULT 0,
    NON_CACHE_HITS_AVG_SVR_TIME NUMBER(10,2)    DEFAULT 0,
    ROLLUP_TIMESTAMP            DATE);

CREATE TABLE &EM_REPOS_USER..MGMT_RT_INCOMPLETE_LOADS_1DAY
   (TARGET_GUID                RAW(16)         NOT NULL,
    PAGE_URL                   VARCHAR2(1024)  NOT NULL,
    NUM_INCOMPLETE_LOADS       NUMBER          DEFAULT 0,
    AVG_SERVER_TIME            NUMBER(10,2)    DEFAULT 0,
    ROLLUP_TIMESTAMP           DATE);



DROP TYPE &EM_REPOS_USER..MGMT_MNTR_USER_STATS_ARRAY;

CREATE OR REPLACE TYPE &EM_REPOS_USER..MGMT_MNTR_USER_STATS_OBJ as OBJECT (
    queryName VARCHAR2(1024),
    displayName VARCHAR2(128),
    avgVal VARCHAR2(20),
    servAvgVal VARCHAR2(20),
    dbAvgVal VARCHAR2(20),
    hitsVal VARCHAR2(20),
    minVal VARCHAR2(20),
    maxVal VARCHAR2(20),
    stddevVal VARCHAR2(20),
    varianceVal VARCHAR2(20),
    critThreshold VARCHAR2(20),
    warnThreshold VARCHAR2(20),
    urlLink VARCHAR2(1280),
    mostFavorite VARCHAR(5),
    incompleteHitsVal VARCHAR2(20),
    incompleteAvgVal VARCHAR2(20) 
);
/

CREATE OR REPLACE TYPE &EM_REPOS_USER..MGMT_MNTR_USER_STATS_ARRAY
  as TABLE of MGMT_MNTR_USER_STATS_OBJ;
/

ALTER TABLE MGMT_RT_BOOTSTRAP_TIMES
   ADD CONSTRAINT mgmt_rt_bootstrap_times_pk
       PRIMARY KEY (target_guid)
   USING INDEX PCTFREE 0;

BEGIN
EXECUTE IMMEDIATE
  'ALTER TABLE MGMT_RT_URL_BOOTSTRAP
   ADD CONSTRAINT url_bootstrap_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name,
                    url_filename, url_link)
   USING INDEX PCTFREE 0' ;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/

ALTER TABLE MGMT_RT_URL_DIST_BOOTSTRAP
   ADD CONSTRAINT url_dist_bootstrap_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name,
                    url_filename, num_seconds, dist_value_type)
   USING INDEX PCTFREE 0;

ALTER TABLE MGMT_RT_DOMAIN_BOOTSTRAP
   ADD CONSTRAINT domain_bootstrap_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name,
                    visitor_domain, visitor_subnet)
   USING INDEX PCTFREE 0;

ALTER TABLE MGMT_RT_DOMAIN_DIST_BOOTSTRAP
   ADD CONSTRAINT domain_dist_bs_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name,
                    visitor_domain, visitor_subnet, 
                    num_seconds, dist_value_type)
   USING INDEX PCTFREE 0;

ALTER TABLE MGMT_RT_IP_BOOTSTRAP
   ADD CONSTRAINT ip_bootstrap_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name, visitor_node)
   USING INDEX
  PCTFREE 0;

ALTER TABLE MGMT_RT_IP_DIST_BOOTSTRAP
   ADD CONSTRAINT ip_dist_bootstrap_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name,
                    visitor_node, num_seconds, dist_value_type)
   USING INDEX
  PCTFREE 0;


ALTER TABLE MGMT_RT_INCOMPLETE_LOADS
   ADD CONSTRAINT inc_loads_primary_key
       PRIMARY KEY (target_guid, aggregate_hour_timestamp, page_url)
   USING INDEX
  PCTFREE 0;

ALTER TABLE MGMT_RT_INCOMPLETE_LOADS_1HOUR
   ADD CONSTRAINT inc_loads_1hour_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, page_url)
   USING INDEX
  PCTFREE 0;

ALTER TABLE MGMT_RT_INCOMPLETE_LOADS_1DAY
   ADD CONSTRAINT inc_loads_1day_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, page_url)
   USING INDEX
  PCTFREE 0;

BEGIN
EXECUTE IMMEDIATE
  'ALTER TABLE MGMT_RT_PR_MAPPING
   ADD CONSTRAINT pr_mapping_primary_key
       PRIMARY KEY (target_guid, aggregate_hour_timestamp, 
                    page_url, request_url)
   USING INDEX
  PCTFREE 0';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/

BEGIN
EXECUTE IMMEDIATE
  'ALTER TABLE MGMT_RT_PR_MAPPING_1HOUR
   ADD CONSTRAINT pr_mapping_1hour_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, page_url, request_url)
   USING INDEX
  PCTFREE 0' ;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/

BEGIN
EXECUTE IMMEDIATE
  'ALTER TABLE MGMT_RT_PR_MAPPING_1DAY
   ADD CONSTRAINT pr_mapping_1day_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, page_url, request_url)
   USING INDEX
  PCTFREE 0' ;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/