51 lines
2.3 KiB
SQL
51 lines
2.3 KiB
SQL
|
|
CREATE TABLE "PERFSTAT_CONTROL"."STATSPACK_MERGE_CONTROL"
|
|
( "DATABASE" VARCHAR2(30 BYTE) NOT NULL ENABLE,
|
|
"ACTIVE" VARCHAR2(1 BYTE) NOT NULL ENABLE,
|
|
"VERSION" VARCHAR2(10 BYTE) NOT NULL ENABLE,
|
|
"ENVIRONMENT" VARCHAR2(10 BYTE) NOT NULL ENABLE,
|
|
"SCHEMA_NAME" VARCHAR2(30 BYTE),
|
|
CONSTRAINT "STATSPACK_MERGE_CONTROL_PK" PRIMARY KEY ("DATABASE")
|
|
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
|
|
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
|
|
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
|
|
TABLESPACE "USERS" ENABLE,
|
|
CONSTRAINT "SMC_ACTIVE_CHECK" CHECK (ACTIVE IN ('Y','N')) ENABLE,
|
|
CONSTRAINT "SMC_VERSION_CHECK" CHECK (version IN ('9.2','10.2')) ENABLE,
|
|
CONSTRAINT "SMC_ENVIRONMENT_CHECK" CHECK (ENVIRONMENT IN ('DEV','TEST','PROD')) ENABLE
|
|
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
|
|
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
|
|
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
|
|
TABLESPACE "USERS" ;
|
|
|
|
COMMENT ON COLUMN "PERFSTAT_CONTROL"."STATSPACK_MERGE_CONTROL"."SCHEMA_NAME" IS 'Auto poplated through a trigger.';
|
|
|
|
CREATE OR REPLACE TRIGGER "PERFSTAT_CONTROL"."STATSPACK_MERGE_CONTROL_IU_TRG"
|
|
BEFORE INSERT OR UPDATE ON STATSPACK_MERGE_CONTROL
|
|
FOR EACH ROW
|
|
BEGIN
|
|
:NEW.DATABASE := UPPER(:new.DATABASE);
|
|
:NEW.ENVIRONMENT := UPPER(:new.ENVIRONMENT);
|
|
:NEW.ACTIVE := UPPER(:new.ACTIVE);
|
|
:NEW.schema_name := UPPER(:new.schema_name);
|
|
|
|
IF :NEW.version = '10.2' and :NEW.environment = 'PROD' THEN
|
|
:new.schema_name := 'PERFSTAT_PROD_10G';
|
|
ELSIF :NEW.version = '9.2' and :NEW.environment = 'PROD' THEN
|
|
:new.schema_name := 'PERFSTAT_PROD_9I';
|
|
ELSIF :NEW.version = '10.2' and :NEW.environment = 'TEST' THEN
|
|
:new.schema_name := 'PERFSTAT_TEST_10G';
|
|
ELSIF :NEW.version = '9.2' and :NEW.environment = 'TEST' THEN
|
|
:new.schema_name := 'PERFSTAT_TEST_9I';
|
|
ELSIF :NEW.version = '10.2' and :NEW.environment = 'DEV' THEN
|
|
:new.schema_name := 'PERFSTAT_DEV_10G';
|
|
ELSIF :NEW.version = '9.2' and :NEW.environment = 'DEV' THEN
|
|
:new.schema_name := 'PERFSTAT_DEV_9I';
|
|
END IF;
|
|
END;
|
|
|
|
|
|
/
|
|
ALTER TRIGGER "PERFSTAT_CONTROL"."STATSPACK_MERGE_CONTROL_IU_TRG" ENABLE;
|
|
|