DROP TABLE OraJobsTest CASCADE CONSTRAINTS;
CREATE TABLE OraJobsTest(
TRGT_HOST       varchar2(32),
PORT_NO         varchar2(32),
SITE_NAME       varchar2(64),
INFO            varchar2(128),
MSMT_HOST       varchar2(32),
MSMT_TIME       date,
TOTAL_FAILED_JOBS Number(20,4) NOT NULL,
TOTAL_FAILED_JOBS_ST varchar2(32) NOT NULL,
TOTAL_BROKEN_JOBS Number(20,4) NOT NULL,
TOTAL_BROKEN_JOBS_ST varchar2(32) NOT NULL,
NUM_RUNNING_JOBS Number(20,4) NOT NULL,
NUM_RUNNING_JOBS_ST varchar2(32) NOT NULL,
MAX_CPU_TIME Number(20,4) NOT NULL,
MAX_CPU_TIME_ST varchar2(32) NOT NULL,
MAX_RUN_TIME Number(20,4) NOT NULL,
MAX_RUN_TIME_ST varchar2(32) NOT NULL,
SUCCESSFULL_JOBS Number(20,4) NOT NULL,
SUCCESSFULL_JOBS_ST varchar2(32) NOT NULL
);

CREATE INDEX IDX_OraJobsTest ON OraJobsTest(MSMT_TIME);
ALTER TABLE OraJobsTest ADD constraint PK_OraJobsTest PRIMARY KEY(TRGT_HOST,PORT_NO,SITE_NAME,INFO,MSMT_HOST, MSMT_TIME);

DROP TABLE OraJobsTest_TREND CASCADE CONSTRAINTS;

CREATE TABLE OraJobsTest_TREND(
TRGT_HOST       varchar2(32),
PORT_NO         varchar2(32),
SITE_NAME       varchar2(64),
INFO            varchar2(128),
MSMT_HOST       varchar2(32),
MSMT_TIME       date,
PERIOD       	varchar2(32),
TOTAL_FAILED_JOBS_MIN Number(20,4) NOT NULL,
TOTAL_FAILED_JOBS_MAX Number(20,4) NOT NULL,
TOTAL_FAILED_JOBS_STCNT varchar2(32) NOT NULL,
TOTAL_BROKEN_JOBS_MIN Number(20,4) NOT NULL,
TOTAL_BROKEN_JOBS_MAX Number(20,4) NOT NULL,
TOTAL_BROKEN_JOBS_STCNT varchar2(32) NOT NULL,
NUM_RUNNING_JOBS_MIN Number(20,4) NOT NULL,
NUM_RUNNING_JOBS_MAX Number(20,4) NOT NULL,
NUM_RUNNING_JOBS_STCNT varchar2(32) NOT NULL,
MAX_CPU_TIME_MIN Number(20,4) NOT NULL,
MAX_CPU_TIME_MAX Number(20,4) NOT NULL,
MAX_CPU_TIME_STCNT varchar2(32) NOT NULL,
MAX_RUN_TIME_MIN Number(20,4) NOT NULL,
MAX_RUN_TIME_MAX Number(20,4) NOT NULL,
MAX_RUN_TIME_STCNT varchar2(32) NOT NULL,
SUCCESSFULL_JOBS_MIN Number(20,4) NOT NULL,
SUCCESSFULL_JOBS_MAX Number(20,4) NOT NULL,
SUCCESSFULL_JOBS_STCNT varchar2(32) NOT NULL
);


ALTER TABLE OraJobsTest_TREND ADD constraint PK_OraJobsTest_TREND PRIMARY KEY(TRGT_HOST,PORT_NO,SITE_NAME,INFO,MSMT_HOST, MSMT_TIME,PERIOD);

DROP TABLE OraJobsTest_THRESH CASCADE CONSTRAINTS;

CREATE TABLE OraJobsTest_THRESH(
TRGT_HOST       varchar2(32),
PORT_NO         varchar2(32),
SITE_NAME       varchar2(64),
INFO            varchar2(128)
,MSMT_HOST       varchar2(32),
MSMT_TIME_START date,
MSMT_TIME_END   date,
TOTAL_FAILED_JOBS_MIN VARCHAR2(256) NOT NULL,
TOTAL_FAILED_JOBS_MAX VARCHAR2(256) NOT NULL,
TOTAL_BROKEN_JOBS_MIN VARCHAR2(256) NOT NULL,
TOTAL_BROKEN_JOBS_MAX VARCHAR2(256) NOT NULL,
NUM_RUNNING_JOBS_MIN VARCHAR2(256) NOT NULL,
NUM_RUNNING_JOBS_MAX VARCHAR2(256) NOT NULL,
MAX_CPU_TIME_MIN VARCHAR2(256) NOT NULL,
MAX_CPU_TIME_MAX VARCHAR2(256) NOT NULL,
MAX_RUN_TIME_MIN VARCHAR2(256) NOT NULL,
MAX_RUN_TIME_MAX VARCHAR2(256) NOT NULL,
SUCCESSFULL_JOBS_MIN VARCHAR2(256) NOT NULL,
SUCCESSFULL_JOBS_MAX VARCHAR2(256) NOT NULL
);

ALTER TABLE OraJobsTest_THRESH ADD constraint PK_OraJobsTest_THRESH PRIMARY KEY(TRGT_HOST,PORT_NO,SITE_NAME,INFO,MSMT_HOST,MSMT_TIME_START);

CREATE INDEX IDX_OraJobsTest_THRESH ON OraJobsTest_THRESH (MSMT_HOST,MSMT_TIME_START,MSMT_TIME_END);
CREATE INDEX IDX_TH_OraJobsTest ON OraJobsTest_THRESH (MSMT_TIME_START);
CREATE INDEX IDX_TR_OraJobsTest ON OraJobsTest_TREND (MSMT_TIME,PERIOD);

DROP TABLE CtxXcXALoginTest_REPORT CASCADE CONSTRAINTS;
CREATE TABLE CtxXcXALoginTest_REPORT ( 
	SESSION_KEY VARCHAR2(128),  
	SESSION_NAME VARCHAR2(32),  
	SESSION_ID VARCHAR2(32),  
	TRGT_HOST VARCHAR2(32),  
	PORT_NO VARCHAR2(32),  
	USERNAME VARCHAR2(128) NOT NULL,
	MSMT_TIME DATE NOT NULL,
	LOGIN_TIME DATE NOT NULL,
	DURATION NUMBER (20,4) NOT NULL,
	ACTIVE_TIME NUMBER (20,4) NOT NULL,
	IDLE_TIME NUMBER (20,4) NOT NULL,
	CLIENT_IP_ADDRESS VARCHAR2(128),
	CLIENT_NAME VARCHAR2(128),
	CLIENT_VERSION VARCHAR2(128),
	CLIENT_TYPE VARCHAR2(128),
	CLIENT_ID VARCHAR2(128),
	CLIENT_CATEGORY VARCHAR2(128),
	CTX_STATE VARCHAR2(128),
	HDX_DISPLAY_MODE VARCHAR2(128),
	HDX_ACTIVE_ENCODER VARCHAR2(128),
	HDX_PROTOCOL VARCHAR2(128),
	ENCRYPTION_LEVEL VARCHAR2(128),
	NETSCALER_RPTNAME VARCHAR2(128),
	NETSCALER_IP_ADDRESS VARCHAR2(128),
	XENAPP_IP_ADDRESS VARCHAR2(128),
	ENDPOINT_IP_ADDRESS VARCHAR2(128),
	COUNTRY_NAME VARCHAR2(128),
	COUNTRY_CODE VARCHAR2(128),
	REGION VARCHAR2(128),
	CITY VARCHAR2(128),
	LATITUDE VARCHAR2(128),
	LONGITUDE VARCHAR2(128),
	LOGON_DURATION NUMBER (20,4) NOT NULL,
	ACTUAL_LOGON_DURATION NUMBER (20,4) NOT NULL,
	CPU_UTIL NUMBER (20,4) NOT NULL,
	MEM_UTIL NUMBER (20,4) NOT NULL,
	LATENCY_AVG NUMBER (20,4) NOT NULL,
	ICA_NWLATENCY NUMBER (20,4) NOT NULL,
	FRAME_RATE NUMBER (20,4) NOT NULL,
	FRAMEHAWK NUMBER (20,4) NOT NULL,
	IO_READS NUMBER (20,4) NOT NULL,
	IO_WRITES NUMBER (20,4) NOT NULL,
	ROUND_TRIP_TIME NUMBER (20,4) NOT NULL,
	CONNECTION_STRENGTH NUMBER (20,4) NOT NULL,
	OUTPUT_LINE_SPEED NUMBER (20,4) NOT NULL
);
CREATE INDEX IDX_CtxXcXALoginTest_REPORT ON CtxXcXALoginTest_REPORT(MSMT_TIME);

DROP TABLE EsxLoginTest_REPORT CASCADE CONSTRAINTS;
CREATE TABLE EsxLoginTest_REPORT ( 
	SESSION_KEY VARCHAR2(128), 
	TRGT_HOST VARCHAR2(32),  
	PORT_NO VARCHAR2(32),  
	MSMT_TIME DATE NOT NULL,
	USERNAME VARCHAR2(128) NOT NULL,
	GUESTNAME VARCHAR2(128) NOT NULL,
	LOGIN_TIME DATE NOT NULL,
	DURATION NUMBER (20,4) NOT NULL,
	ACTIVE_TIME NUMBER (20,4) NOT NULL,
	IDLE_TIME NUMBER (20,4) NOT NULL,
	CLIENT_IP_ADDRESS VARCHAR2(128),
	CLIENT_NAME VARCHAR2(128),
	CLIENT_ID VARCHAR2(128),
	CLIENT_CATEGORY VARCHAR2(128),
	CTX_STATE VARCHAR2(128),
	HDX_DISPLAY_MODE VARCHAR2(128),
	HDX_ACTIVE_ENCODER VARCHAR2(128),
	HDX_PROTOCOL VARCHAR2(128),
	ENCRYPTION_LEVEL VARCHAR2(128),
	NETSCALER_RPTNAME VARCHAR2(128),
	NETSCALER_IP_ADDRESS VARCHAR2(128),
	XENAPP_IP_ADDRESS VARCHAR2(128),
	ENDPOINT_IP_ADDRESS VARCHAR2(128),
	COUNTRY_NAME VARCHAR2(128),
	COUNTRY_CODE VARCHAR2(128),
	REGION VARCHAR2(128),
	CITY VARCHAR2(128),
	LATITUDE VARCHAR2(128),
	LONGITUDE VARCHAR2(128),
	LOGON_DURATION NUMBER (20,4) NOT NULL,
	ACTUAL_LOGON_DURATION NUMBER (20,4) NOT NULL,
	CPU_USED NUMBER (20,4) NOT NULL,
	ACTIVE_MEMORY NUMBER (20,4) NOT NULL,
	DATA_READS NUMBER (20,4) NOT NULL,
	DATA_WRITES NUMBER (20,4) NOT NULL,
	DATA_TRANSMITTED NUMBER (20,4) NOT NULL,
	DATA_RECEIVED NUMBER (20,4) NOT NULL,
	CLIENT_IPADDRESS VARCHAR2(32) NOT NULL,
	CLIENT_VERSION VARCHAR2(32) NOT NULL
);
CREATE INDEX IDX_EsxLoginTest_REPORT ON EsxLoginTest_REPORT(MSMT_TIME); 

DROP TABLE EsxLoginTest_REPORT_INSIDE CASCADE CONSTRAINTS;
CREATE TABLE EsxLoginTest_REPORT_INSIDE ( 
	SESSION_KEY VARCHAR2(128), 
	TRGT_HOST VARCHAR2(32),  
	PORT_NO VARCHAR2(32),  
	MSMT_TIME DATE NOT NULL,
	USERNAME VARCHAR2(128) NOT NULL,
	GUESTNAME VARCHAR2(128) NOT NULL,
	LOGIN_TIME DATE NOT NULL,
	DURATION NUMBER (20,4) NOT NULL,
	ACTIVE_TIME NUMBER (20,4) NOT NULL,
	IDLE_TIME NUMBER (20,4) NOT NULL,
	CLIENT_IP_ADDRESS VARCHAR2(128),
	CLIENT_NAME VARCHAR2(128),
	CLIENT_ID VARCHAR2(128),
	CLIENT_CATEGORY VARCHAR2(128),
	CTX_STATE VARCHAR2(128),
	HDX_DISPLAY_MODE VARCHAR2(128),
	HDX_ACTIVE_ENCODER VARCHAR2(128),
	HDX_PROTOCOL VARCHAR2(128),
	ENCRYPTION_LEVEL VARCHAR2(128),
	NETSCALER_RPTNAME VARCHAR2(128),
	NETSCALER_IP_ADDRESS VARCHAR2(128),
	XENAPP_IP_ADDRESS VARCHAR2(128),
	ENDPOINT_IP_ADDRESS VARCHAR2(128),
	COUNTRY_NAME VARCHAR2(128),
	COUNTRY_CODE VARCHAR2(128),
	REGION VARCHAR2(128),
	CITY VARCHAR2(128),
	LATITUDE VARCHAR2(128),
	LONGITUDE VARCHAR2(128),
	LOGON_DURATION NUMBER (20,4) NOT NULL,
	ACTUAL_LOGON_DURATION NUMBER (20,4) NOT NULL,
	CPU_UTIL NUMBER (20,4) NOT NULL,
	TOTAL_PHY_MEM NUMBER (20,4) NOT NULL,
	PHYSICAL_MEM_UTILZE NUMBER (20,4) NOT NULL,
	PERCENT_BUSY NUMBER (20,4) NOT NULL,
	PERCENT_DISK_READS NUMBER (20,4) NOT NULL,
	PERCENT_DISK_WRITES NUMBER (20,4) NOT NULL,
	INCOMING_TRAFFIC NUMBER (20,4) NOT NULL,
	OUTGOING_TRAFFIC NUMBER (20,4) NOT NULL,
	SESSION_AVG_LATENCY NUMBER (20,4) NOT NULL,
	LAST_RECD_LATENCY NUMBER (20,4) NOT NULL,
	ICA_NWLATENCY NUMBER (20,4) NOT NULL,
	INPUT_SESSION_BANDWIDTH NUMBER (20,4) NOT NULL,
	OUTPUT_SESSION_BANDWIDTH NUMBER (20,4) NOT NULL,
	BROWSER_COUNT NUMBER (20,4) NOT NULL,
	AVG_CPU_UTIL NUMBER (20,4) NOT NULL,
	MEM_USED NUMBER (20,4) NOT NULL,
	CLIENT_IPADDRESS VARCHAR2(32) NOT NULL,
	CLIENT_VERSION VARCHAR2(32) NOT NULL
);
CREATE INDEX IDX_EsxLoginTest_RPT_INSIDE ON EsxLoginTest_REPORT_INSIDE(MSMT_TIME);

DROP TABLE NodeGCtest CASCADE CONSTRAINTS;

CREATE TABLE NodeGCtest(
TRGT_HOST				VARCHAR2(32)	NOT NULL,
PORT_NO					VARCHAR2(32)	NOT NULL,
SITE_NAME				VARCHAR2(64)	NOT NULL,
INFO					VARCHAR2(128)	NOT NULL,
MSMT_HOST				VARCHAR2(32)	NOT NULL,
MSMT_TIME				DATE		NOT NULL,
FullGC_count				NUMBER(20,4)	NOT NULL,
FullGC_count_ST				VARCHAR2(32)	NOT NULL,
IncGC_count				NUMBER(20,4)	NOT NULL,
IncGC_count_ST				VARCHAR2(32)	NOT NULL,
MinorGC_count				NUMBER(20,4)	NOT NULL,
MinorGC_count_ST 			VARCHAR2(32)	NOT NULL,
FullGC_time				NUMBER(20,4)	NOT NULL,
FullGC_time_ST				VARCHAR2(32)	NOT NULL,
IncGC_time				NUMBER(20,4)	NOT NULL,
IncGC_time_ST				VARCHAR2(32)	NOT NULL,
MinorGC_time				NUMBER(20,4)	NOT NULL,
MinorGC_time_ST				VARCHAR2(32)	NOT NULL,
GC_time					NUMBER(20,4)	NOT NULL,
GC_time_ST				VARCHAR2(32)	NOT NULL,
GC_ticks				NUMBER(20,4)	NOT NULL,
GC_ticks_ST				VARCHAR2(32)	NOT NULL,
GC_released_mem				NUMBER(20,4)	NOT NULL,
GC_released_mem_ST			VARCHAR2(32)	NOT NULL,
Number_Of_Workers			NUMBER(20,4)	NOT NULL,
Number_Of_Workers_ST 			VARCHAR2(32)	NOT NULL,
FullGC_Percentage 			NUMBER(20,4)	NOT NULL,
FullGC_Percentage_ST 			VARCHAR2(32)	NOT NULL,
IncGC_Percentage 			NUMBER(20,4)	NOT NULL,
IncGC_Percentage_ST 			VARCHAR2(32)	NOT NULL,
MinorGC_Percentage 			NUMBER(20,4)	NOT NULL,
MinorGC_Percentage_ST 			VARCHAR2(32)	NOT NULL,
TotalGC_Percentage			NUMBER(20,4)	NOT NULL,
TotalGC_Percentage_ST			VARCHAR2(32)	NOT NULL
);


CREATE INDEX IDX_NodeGCtest ON NodeGCtest(MSMT_TIME);
ALTER TABLE NodeGCtest ADD constraint PK_NodeGCtest PRIMARY KEY(TRGT_HOST,PORT_NO,SITE_NAME,INFO,MSMT_HOST, MSMT_TIME);

DROP TABLE NodeGCtest_TREND CASCADE CONSTRAINTS;

CREATE TABLE NodeGCtest_TREND(
TRGT_HOST				VARCHAR2(32)	NOT NULL,
PORT_NO					VARCHAR2(32)	NOT NULL,
SITE_NAME				VARCHAR2(64)	NOT NULL,
INFO					VARCHAR2(128)	NOT NULL,
MSMT_HOST				VARCHAR2(32)	NOT NULL,
MSMT_TIME				DATE		NOT NULL,
PERIOD					VARCHAR2(32)	NOT NULL,
FullGC_count_MIN		NUMBER(20,4)	NOT NULL,
FullGC_count_MAX		NUMBER(20,4)	NOT NULL,
FullGC_count_STCNT		VARCHAR2(32)	NOT NULL,
IncGC_count_MIN			NUMBER(20,4)	NOT NULL,
IncGC_count_MAX			NUMBER(20,4)	NOT NULL,
IncGC_count_STCNT		VARCHAR2(32)	NOT NULL,
MinorGC_count_MIN		NUMBER(20,4)	NOT NULL,
MinorGC_count_MAX		NUMBER(20,4)	NOT NULL,
MinorGC_count_STCNT		VARCHAR2(32)	NOT NULL,
FullGC_time_MIN			NUMBER(20,4)	NOT NULL,
FullGC_time_MAX			NUMBER(20,4)	NOT NULL,
FullGC_time_STCNT		VARCHAR2(32)	NOT NULL,
IncGC_time_MIN			NUMBER(20,4)	NOT NULL,
IncGC_time_MAX			NUMBER(20,4)	NOT NULL,
IncGC_time_STCNT		VARCHAR2(32)	NOT NULL,
MinorGC_time_MIN		NUMBER(20,4)	NOT NULL,
MinorGC_time_MAX		NUMBER(20,4)	NOT NULL,
MinorGC_time_STCNT		VARCHAR2(32)	NOT NULL,
GC_time_MIN			NUMBER(20,4)	NOT NULL,
GC_time_MAX			NUMBER(20,4)	NOT NULL,
GC_time_STCNT			VARCHAR2(32)	NOT NULL,
GC_ticks_MIN			NUMBER(20,4)	NOT NULL,
GC_ticks_MAX			NUMBER(20,4)	NOT NULL,
GC_ticks_STCNT			VARCHAR2(32)	NOT NULL,
GC_released_mem_MIN	    	NUMBER(20,4)	NOT NULL,
GC_released_mem_MAX		NUMBER(20,4)	NOT NULL,
GC_released_mem_STCNT		VARCHAR2(32)	NOT NULL,
Number_Of_Workers_MIN		NUMBER(20,4)	NOT NULL,
Number_Of_Workers_MAX		NUMBER(20,4)	NOT NULL,
Number_Of_Workers_STCNT		VARCHAR2(32)	NOT NULL,
FullGC_Percentage_MIN 		NUMBER(20,4)	NOT NULL,
FullGC_Percentage_MAX 		NUMBER(20,4)	NOT NULL,
FullGC_Percentage_STCNT 	VARCHAR2(32)	NOT NULL,
IncGC_Percentage_MIN		NUMBER(20,4)	NOT NULL,
IncGC_Percentage_MAX		NUMBER(20,4)	NOT NULL,
IncGC_Percentage_STCNT 		VARCHAR2(32)	NOT NULL,
MinorGC_Percentage_MIN 		NUMBER(20,4)	NOT NULL,
MinorGC_Percentage_MAX 		NUMBER(20,4)	NOT NULL,
MinorGC_Percentage_STCNT 	VARCHAR2(32)	NOT NULL,
TotalGC_Percentage_MIN		NUMBER(20,4)	NOT NULL,
TotalGC_Percentage_MAX		NUMBER(20,4)	NOT NULL,
TotalGC_Percentage_STCNT	VARCHAR2(32)	NOT NULL
);


ALTER TABLE NodeGCtest_TREND ADD constraint PK_NodeGCtest_TREND PRIMARY KEY(TRGT_HOST,PORT_NO,SITE_NAME,INFO,MSMT_HOST, MSMT_TIME,PERIOD);

DROP TABLE NodeGCtest_THRESH CASCADE CONSTRAINTS;
CREATE TABLE NodeGCtest_THRESH(
TRGT_HOST				VARCHAR2(32)	NOT NULL,
PORT_NO					VARCHAR2(32)	NOT NULL,
SITE_NAME				VARCHAR2(64)	NOT NULL,
INFO					VARCHAR2(128)	NOT NULL,
MSMT_HOST				VARCHAR2(32)	NOT NULL,
MSMT_TIME_START				DATE		NOT NULL,
MSMT_TIME_END				DATE		NOT NULL,
FullGC_count_MIN		VARCHAR2(256)	NOT NULL,
FullGC_count_MAX		VARCHAR2(256)	NOT NULL,
IncGC_count_MIN			VARCHAR2(256)	NOT NULL,
IncGC_count_MAX			VARCHAR2(256)	NOT NULL,
MinorGC_count_MIN		VARCHAR2(256)	NOT NULL,
MinorGC_count_MAX		VARCHAR2(256)	NOT NULL,
FullGC_time_MIN			VARCHAR2(256)	NOT NULL,
FullGC_time_MAX			VARCHAR2(256)	NOT NULL,
IncGC_time_MIN			VARCHAR2(256)	NOT NULL,
IncGC_time_MAX			VARCHAR2(256)	NOT NULL,
MinorGC_time_MIN		VARCHAR2(256)	NOT NULL,
MinorGC_time_MAX		VARCHAR2(256)	NOT NULL,
GC_time_MIN			VARCHAR2(256)	NOT NULL,
GC_time_MAX			VARCHAR2(256)	NOT NULL,
GC_ticks_MIN			VARCHAR2(256)	NOT NULL,
GC_ticks_MAX			VARCHAR2(256)	NOT NULL,
GC_released_mem_MIN		VARCHAR2(256)	NOT NULL,
GC_released_mem_MAX		VARCHAR2(256)	NOT NULL,
Number_Of_Workers_MIN		VARCHAR2(256)	NOT NULL,
Number_Of_Workers_MAX		VARCHAR2(256)	NOT NULL,
FullGC_Percentage_MIN 		VARCHAR2(256)	NOT NULL,
FullGC_Percentage_MAX 		VARCHAR2(256)	NOT NULL,
IncGC_Percentage_MIN		VARCHAR2(256)	NOT NULL,
IncGC_Percentage_MAX		VARCHAR2(256)	NOT NULL,
MinorGC_Percentage_MIN 		VARCHAR2(256)	NOT NULL,
MinorGC_Percentage_MAX 		VARCHAR2(256)	NOT NULL,
TotalGC_Percentage_MIN		VARCHAR2(256)	NOT NULL,
TotalGC_Percentage_MAX		VARCHAR2(256)	NOT NULL
);


ALTER TABLE NodeGCtest_THRESH ADD constraint PK_NodeGCtest_THRESH PRIMARY KEY(TRGT_HOST,PORT_NO,SITE_NAME,INFO,MSMT_HOST,MSMT_TIME_START);
CREATE INDEX IDX_NodeGCtest_THRESH ON NodeGCtest_THRESH (MSMT_HOST,MSMT_TIME_START,MSMT_TIME_END);
CREATE INDEX IDX_TH_NodeGCtest ON NodeGCtest_THRESH (MSMT_TIME_START);
CREATE INDEX IDX_TR_NodeGCtest ON NodeGCtest_TREND (MSMT_TIME,PERIOD);

DROP TABLE CtxNsVpnVsTest CASCADE CONSTRAINTS;
CREATE TABLE CtxNsVpnVsTest(
TRGT_HOST       varchar2(32),
PORT_NO         varchar2(32),
SITE_NAME       varchar2(64),
INFO            varchar2(128),
MSMT_HOST       varchar2(32),
MSMT_TIME       date,
SERVER_STATE Number(20,4) NOT NULL,
SERVER_STATE_ST varchar2(32) NOT NULL,
REQ_DATA_RECEIVED Number(20,4) NOT NULL,
REQ_DATA_RECEIVED_ST varchar2(32) NOT NULL,
RES_DATA_RECEIVED Number(20,4) NOT NULL,
RES_DATA_RECEIVED_ST varchar2(32) NOT NULL,
REQ_RECEIVED Number(20,4) NOT NULL,
REQ_RECEIVED_ST varchar2(32) NOT NULL,
RES_RECEIVED Number(20,4) NOT NULL,
RES_RECEIVED_ST varchar2(32) NOT NULL,
CURRENT_USERS Number(20,4) NOT NULL,
CURRENT_USERS_ST varchar2(32) NOT NULL,
MAX_USERS Number(20,4) NOT NULL,
MAX_USERS_ST varchar2(32) NOT NULL,
CURR_TOT_USERS Number(20,4) NOT NULL,
CURR_TOT_USERS_ST varchar2(32) NOT NULL,
UP_SERVERS Number(20,4) NOT NULL,
UP_SERVERS_ST varchar2(32) NOT NULL,
DOWN_SERVERS Number(20,4) NOT NULL,
DOWN_SERVERS_ST varchar2(32) NOT NULL,
PCT_OF_UP_SERVERS Number(20,4) NOT NULL,
PCT_OF_UP_SERVERS_ST varchar2(32) NOT NULL);

CREATE INDEX IDX_CtxNsVpnVsTest ON CtxNsVpnVsTest(MSMT_TIME);
ALTER TABLE CtxNsVpnVsTest ADD constraint PK_CtxNsVpnVsTest PRIMARY KEY(TRGT_HOST,PORT_NO,SITE_NAME,INFO,MSMT_HOST, MSMT_TIME);
DROP TABLE CtxNsVpnVsTest_TREND CASCADE CONSTRAINTS;
CREATE TABLE CtxNsVpnVsTest_TREND(
TRGT_HOST       varchar2(32),
PORT_NO         varchar2(32),
SITE_NAME       varchar2(64),
INFO            varchar2(128),
MSMT_HOST       varchar2(32),
MSMT_TIME       date,
PERIOD       	varchar2(32),
SERVER_STATE_MIN Number(20,4) NOT NULL,
SERVER_STATE_MAX Number(20,4) NOT NULL,
SERVER_STATE_STCNT varchar2(32) NOT NULL,
REQ_DATA_RECEIVED_MIN Number(20,4) NOT NULL,
REQ_DATA_RECEIVED_MAX Number(20,4) NOT NULL,
REQ_DATA_RECEIVED_STCNT varchar2(32) NOT NULL,
RES_DATA_RECEIVED_MIN Number(20,4) NOT NULL,
RES_DATA_RECEIVED_MAX Number(20,4) NOT NULL,
RES_DATA_RECEIVED_STCNT varchar2(32) NOT NULL,
REQ_RECEIVED_MIN Number(20,4) NOT NULL,
REQ_RECEIVED_MAX Number(20,4) NOT NULL,
REQ_RECEIVED_STCNT varchar2(32) NOT NULL,
RES_RECEIVED_MIN Number(20,4) NOT NULL,
RES_RECEIVED_MAX Number(20,4) NOT NULL,
RES_RECEIVED_STCNT varchar2(32) NOT NULL,
CURRENT_USERS_MIN Number(20,4) NOT NULL,
CURRENT_USERS_MAX Number(20,4) NOT NULL,
CURRENT_USERS_STCNT varchar2(32) NOT NULL,
MAX_USERS_MIN Number(20,4) NOT NULL,
MAX_USERS_MAX Number(20,4) NOT NULL,
MAX_USERS_STCNT varchar2(32) NOT NULL,
CURR_TOT_USERS_MIN Number(20,4) NOT NULL,
CURR_TOT_USERS_MAX Number(20,4) NOT NULL,
CURR_TOT_USERS_STCNT varchar2(32) NOT NULL,
UP_SERVERS_MIN Number(20,4) NOT NULL,
UP_SERVERS_MAX Number(20,4) NOT NULL,
UP_SERVERS_STCNT varchar2(32) NOT NULL,
DOWN_SERVERS_MIN Number(20,4) NOT NULL,
DOWN_SERVERS_MAX Number(20,4) NOT NULL,
DOWN_SERVERS_STCNT varchar2(32) NOT NULL,
PCT_OF_UP_SERVERS_MIN Number(20,4) NOT NULL,
PCT_OF_UP_SERVERS_MAX Number(20,4) NOT NULL,
PCT_OF_UP_SERVERS_STCNT varchar2(32) NOT NULL);

ALTER TABLE CtxNsVpnVsTest_TREND ADD constraint PK_CtxNsVpnVsTest_TREND PRIMARY KEY(TRGT_HOST,PORT_NO,SITE_NAME,INFO,MSMT_HOST, MSMT_TIME,PERIOD);
DROP TABLE CtxNsVpnVsTest_THRESH CASCADE CONSTRAINTS;
CREATE TABLE CtxNsVpnVsTest_THRESH(
TRGT_HOST       varchar2(32),
PORT_NO         varchar2(32),
SITE_NAME       varchar2(64),
INFO            varchar2(128)
,MSMT_HOST       varchar2(32),
MSMT_TIME_START date,
MSMT_TIME_END   date,
SERVER_STATE_MIN VARCHAR2(256) NOT NULL,
SERVER_STATE_MAX VARCHAR2(256) NOT NULL,
REQ_DATA_RECEIVED_MIN VARCHAR2(256) NOT NULL,
REQ_DATA_RECEIVED_MAX VARCHAR2(256) NOT NULL,
RES_DATA_RECEIVED_MIN VARCHAR2(256) NOT NULL,
RES_DATA_RECEIVED_MAX VARCHAR2(256) NOT NULL,
REQ_RECEIVED_MIN VARCHAR2(256) NOT NULL,
REQ_RECEIVED_MAX VARCHAR2(256) NOT NULL,
RES_RECEIVED_MIN VARCHAR2(256) NOT NULL,
RES_RECEIVED_MAX VARCHAR2(256) NOT NULL,
CURRENT_USERS_MIN VARCHAR2(256) NOT NULL,
CURRENT_USERS_MAX VARCHAR2(256) NOT NULL,
MAX_USERS_MIN VARCHAR2(256) NOT NULL,
MAX_USERS_MAX VARCHAR2(256) NOT NULL,
CURR_TOT_USERS_MIN VARCHAR2(256) NOT NULL,
CURR_TOT_USERS_MAX VARCHAR2(256) NOT NULL,
UP_SERVERS_MIN VARCHAR2(256) NOT NULL,
UP_SERVERS_MAX VARCHAR2(256) NOT NULL,
DOWN_SERVERS_MIN VARCHAR2(256) NOT NULL,
DOWN_SERVERS_MAX VARCHAR2(256) NOT NULL,
PCT_OF_UP_SERVERS_MIN VARCHAR2(256) NOT NULL,
PCT_OF_UP_SERVERS_MAX VARCHAR2(256) NOT NULL);

ALTER TABLE CtxNsVpnVsTest_THRESH ADD constraint PK_CtxNsVpnVsTest_THRESH PRIMARY KEY(TRGT_HOST,PORT_NO,SITE_NAME,INFO,MSMT_HOST,MSMT_TIME_START);
CREATE INDEX IDX_CtxNsVpnVsTest_THRESH ON CtxNsVpnVsTest_THRESH (MSMT_HOST,MSMT_TIME_START,MSMT_TIME_END);
CREATE INDEX IDX_TH_CtxNsVpnVsTest ON CtxNsVpnVsTest_THRESH (MSMT_TIME_START);
CREATE INDEX IDX_TR_CtxNsVpnVsTest ON CtxNsVpnVsTest_TREND (MSMT_TIME,PERIOD);

DROP TABLE CtxNsLicExpiryTest CASCADE CONSTRAINTS;
CREATE TABLE CtxNsLicExpiryTest(
TRGT_HOST       varchar2(32),
PORT_NO         varchar2(32),
SITE_NAME       varchar2(64),
INFO            varchar2(128),
MSMT_HOST       varchar2(32),
MSMT_TIME       date,
DAYS_TO_EXPIRE Number(20,4) NOT NULL,
DAYS_TO_EXPIRE_ST varchar2(32) NOT NULL,
STATUS Number(20,4) NOT NULL,
STATUS_ST varchar2(32) NOT NULL,
GRACE Number(20,4) NOT NULL,
GRACE_ST varchar2(32) NOT NULL,
GRACE_LEFT Number(20,4) NOT NULL,
GRACE_LEFT_ST varchar2(32) NOT NULL,
TOTAL_LICENSES Number(20,4) NOT NULL,
TOTAL_LICENSES_ST varchar2(32) NOT NULL,
AVAILABLE_LICENSES Number(20,4) NOT NULL,
AVAILABLE_LICENSES_ST varchar2(32) NOT NULL,
USED_LICENSES Number(20,4) NOT NULL,
USED_LICENSES_ST varchar2(32) NOT NULL,
LICENSE_UTIL Number(20,4) NOT NULL,
LICENSE_UTIL_ST varchar2(32) NOT NULL);

CREATE INDEX IDX_CtxNsLicExpiryTest ON CtxNsLicExpiryTest(MSMT_TIME);
ALTER TABLE CtxNsLicExpiryTest ADD constraint PK_CtxNsLicExpiryTest PRIMARY KEY(TRGT_HOST,PORT_NO,SITE_NAME,INFO,MSMT_HOST, MSMT_TIME);
DROP TABLE CtxNsLicExpiryTest_TREND CASCADE CONSTRAINTS;
CREATE TABLE CtxNsLicExpiryTest_TREND(
TRGT_HOST       varchar2(32),
PORT_NO         varchar2(32),
SITE_NAME       varchar2(64),
INFO            varchar2(128),
MSMT_HOST       varchar2(32),
MSMT_TIME       date,
PERIOD       	varchar2(32),
DAYS_TO_EXPIRE_MIN Number(20,4) NOT NULL,
DAYS_TO_EXPIRE_MAX Number(20,4) NOT NULL,
DAYS_TO_EXPIRE_STCNT varchar2(32) NOT NULL,
STATUS_MIN Number(20,4) NOT NULL,
STATUS_MAX Number(20,4) NOT NULL,
STATUS_STCNT varchar2(32) NOT NULL,
GRACE_MIN Number(20,4) NOT NULL,
GRACE_MAX Number(20,4) NOT NULL,
GRACE_STCNT varchar2(32) NOT NULL,
GRACE_LEFT_MIN Number(20,4) NOT NULL,
GRACE_LEFT_MAX Number(20,4) NOT NULL,
GRACE_LEFT_STCNT varchar2(32) NOT NULL,
TOTAL_LICENSES_MIN Number(20,4) NOT NULL,
TOTAL_LICENSES_MAX Number(20,4) NOT NULL,
TOTAL_LICENSES_STCNT varchar2(32) NOT NULL,
AVAILABLE_LICENSES_MIN Number(20,4) NOT NULL,
AVAILABLE_LICENSES_MAX Number(20,4) NOT NULL,
AVAILABLE_LICENSES_STCNT varchar2(32) NOT NULL,
USED_LICENSES_MIN Number(20,4) NOT NULL,
USED_LICENSES_MAX Number(20,4) NOT NULL,
USED_LICENSES_STCNT varchar2(32) NOT NULL,
LICENSE_UTIL_MIN Number(20,4) NOT NULL,
LICENSE_UTIL_MAX Number(20,4) NOT NULL,
LICENSE_UTIL_STCNT varchar2(32) NOT NULL);

ALTER TABLE CtxNsLicExpiryTest_TREND ADD constraint PK_CtxNsLicExpiryTest_TREND PRIMARY KEY(TRGT_HOST,PORT_NO,SITE_NAME,INFO,MSMT_HOST, MSMT_TIME,PERIOD);
DROP TABLE CtxNsLicExpiryTest_THRESH CASCADE CONSTRAINTS;
CREATE TABLE CtxNsLicExpiryTest_THRESH(
TRGT_HOST       varchar2(32),
PORT_NO         varchar2(32),
SITE_NAME       varchar2(64),
INFO            varchar2(128),
MSMT_HOST       varchar2(32),
MSMT_TIME_START date,
MSMT_TIME_END   date,
DAYS_TO_EXPIRE_MIN VARCHAR2(256) NOT NULL,
DAYS_TO_EXPIRE_MAX VARCHAR2(256) NOT NULL,
STATUS_MIN VARCHAR2(256) NOT NULL,
STATUS_MAX VARCHAR2(256) NOT NULL,
GRACE_MIN VARCHAR2(256) NOT NULL,
GRACE_MAX VARCHAR2(256) NOT NULL,
GRACE_LEFT_MIN VARCHAR2(256) NOT NULL,
GRACE_LEFT_MAX VARCHAR2(256) NOT NULL,
TOTAL_LICENSES_MIN VARCHAR2(256) NOT NULL,
TOTAL_LICENSES_MAX VARCHAR2(256) NOT NULL,
AVAILABLE_LICENSES_MIN VARCHAR2(256) NOT NULL,
AVAILABLE_LICENSES_MAX VARCHAR2(256) NOT NULL,
USED_LICENSES_MIN VARCHAR2(256) NOT NULL,
USED_LICENSES_MAX VARCHAR2(256) NOT NULL,
LICENSE_UTIL_MIN VARCHAR2(256) NOT NULL,
LICENSE_UTIL_MAX VARCHAR2(256) NOT NULL);

ALTER TABLE CtxNsLicExpiryTest_THRESH ADD constraint PK_CtxNsLicExpiryTest_THRESH PRIMARY KEY(TRGT_HOST,PORT_NO,SITE_NAME,INFO,MSMT_HOST,MSMT_TIME_START);
CREATE INDEX IDX_CtxNsLicExpiryTest_THRESH ON CtxNsLicExpiryTest_THRESH (MSMT_HOST,MSMT_TIME_START,MSMT_TIME_END);
CREATE INDEX IDX_TH_CtxNsLicExpiryTest ON CtxNsLicExpiryTest_THRESH (MSMT_TIME_START);
CREATE INDEX IDX_TR_CtxNsLicExpiryTest ON CtxNsLicExpiryTest_TREND (MSMT_TIME,PERIOD);

DROP TABLE RUM_ERROR_DETAILS CASCADE CONSTRAINTS;

CREATE TABLE RUM_ERROR_DETAILS 
(
	MSMT_TIME			DATE NOT NULL,
	REQUEST_DATE		DATE NOT NULL, 
	REQUEST_TIME_ZONE	VARCHAR2(32) NOT NULL,
	ERROR_GUID			VARCHAR2(128) NOT NULL,
	RUM_PARENT_GUID		VARCHAR2(128) NOT NULL,
	RUM_BASE_GUID		VARCHAR2(128) NOT NULL,
	ERROR_MESSAGE		VARCHAR2(1024) NOT NULL,
	ERROR_FILENAME		VARCHAR2(512) NOT NULL,
	ERROR_LINENUMBER	VARCHAR2(8) NOT NULL,
	ERROR_COLUMNNUMBER	VARCHAR2(8) NOT NULL,
	ERROR_STACK			BLOB NOT NULL,
	ERROR_METADATA		VARCHAR2(512) NOT NULL
);

ALTER TABLE RUM_ERROR_DETAILS ADD CONSTRAINT PK_RUM_ERROR_DETAILS PRIMARY KEY(ERROR_GUID);
CREATE INDEX IDX_RUM_ERROR_DETAILS ON RUM_ERROR_DETAILS(MSMT_TIME);
CREATE INDEX IDX1_RUM_ERROR_DETAILS ON RUM_ERROR_DETAILS(RUM_PARENT_GUID);