-- Script to create neccesary tables and their prequisites for the FESA DB. In particular for the Directory server. -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- -- FESA_CLASS_TYPE -- -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ CREATE TABLE "FESA"."FESA_CLASS_TYPE" ("CLASS_TYPE" VARCHAR2(14 BYTE) NOT NULL ENABLE, CONSTRAINT "FESA_CLASS_TYPE_PK" PRIMARY KEY ("CLASS_TYPE") 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA" ENABLE) SEGMENT CREATION IMMEDIATE 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA" ; -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- -- FESA_DEVICECLASSES -- -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ CREATE TABLE "FESA"."FESA_DEVICECLASSES" ("CLASSNAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, "CLASS_ID" NUMBER, CONSTRAINT "FESA_DEVICECLASSES_PK" PRIMARY KEY ("CLASSNAME") 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA" ; CREATE OR REPLACE TRIGGER "FESA"."FESA_DEVICECLASSES_D_ABC" after delete on fesa_deviceclasses begin delete from abc_fesa_deviceclasses a where a.implementation = 'FESA' and not exists (select 'x' fesa_deviceclasses f a.classname = f.classname); end; / ALTER TRIGGER "FESA"."FESA_DEVICECLASSES_D_ABC" ENABLE; CREATE OR REPLACE TRIGGER "FESA"."FESA_DEVICECLASSES_IU_ABC" after insert or update on fesa_deviceclasses begin MERGE INTO abc_fesa_deviceclasses v USING (select classname from fesa_deviceclasses) t ON (v.classname = t.classname) WHEN MATCHED THEN UPDATE SET v.implementation = 'FESA' WHEN NOT MATCHED THEN INSERT (v.classname, v.implementation, v.lastupdated) VALUES (t.classname, 'FESA', sysdate); end; / ALTER TRIGGER "FESA"."FESA_DEVICECLASSES_IU_ABC" ENABLE; -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- -- FESA_CLASSVERSIONS -- -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ CREATE TABLE "FESA"."FESA_CLASSVERSIONS" ("CLASSNAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, "VERSION" NUMBER(3,0) NOT NULL ENABLE, "CLASS_TYPE" VARCHAR2(14 BYTE) DEFAULT 'standard-class' NOT NULL ENABLE, "DESCRIPTION" VARCHAR2(4000 BYTE), "FRAMEWORK_VERSION" VARCHAR2(4 BYTE), CONSTRAINT "FESA_CLASSVERSIONS_PK" PRIMARY KEY ("CLASSNAME", "VERSION") 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA" ENABLE, CONSTRAINT "FESA_CLASSVERSIONS_CHK1" CHECK ( TRIM(FRAMEWORK_VERSION)=FRAMEWORK_VERSION) ENABLE, CONSTRAINT "FESA_CLASSVERSIONS_FK2" FOREIGN KEY ("CLASS_TYPE") REFERENCES "FESA"."FESA_CLASS_TYPE" ("CLASS_TYPE") ENABLE, CONSTRAINT "FESA_CLASSVERSIONS_FK1" FOREIGN KEY ("CLASSNAME") REFERENCES "FESA"."FESA_DEVICECLASSES" ("CLASSNAME") ON DELETE CASCADE ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA"; CREATE INDEX "FESA"."FESA_CLASSVERSIONS_IDX1" ON "FESA"."FESA_CLASSVERSIONS" ("CLASS_TYPE") 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA"; -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- -- ABC_FESA_DEVICECLASS -- -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ CREATE TABLE "FESA"."ABC_FESA_DEVICECLASSES" ("CLASSNAME" VARCHAR2(20 BYTE), "IMPLEMENTATION" VARCHAR2(10 BYTE) CONSTRAINT "DEVC_IMPLEMENTATION_NN" NOT NULL ENABLE, "ABGROUP" VARCHAR2(10 BYTE), "ALARMS" CHAR(1 BYTE), "PPMCOUNT" NUMBER(2,0), "CREADATE" DATE, "CREATOR" VARCHAR2(30 BYTE), "LASTUPDATED" DATE, "UPDATER" VARCHAR2(30 BYTE), "IMPLEMENTS" VARCHAR2(100 BYTE), "GM_SUPERCLASS" VARCHAR2(20 BYTE), "GM_CLASSCAT" CHAR(2 BYTE), "GM_CLASSNO" NUMBER(6,0), "GM_MULTIMACH" CHAR(1 BYTE), "CLASSDOMAIN" VARCHAR2(16 BYTE), "DESCRIPTION" VARCHAR2(80 BYTE), CONSTRAINT "DEVICECLASSES_PK" PRIMARY KEY ("CLASSNAME") 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA" ENABLE, CONSTRAINT "DEVC_GM_CLASSNO_CHK" CHECK ( IMPLEMENTATION = 'GM' AND GM_CLASSNO IS NOT NULL OR IMPLEMENTATION != 'GM' ) ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA"; -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- -- ABC_TGM_NETWORKS -- -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ CREATE TABLE "FESA"."ABC_TGM_NETWORKS" ("TGM_NETWORK_ID" NUMBER(38,0), "TGM_NETWORK_NAME" VARCHAR2(32 BYTE) CONSTRAINT "TGMN_TGM_NETWORK_NAME_NN" NOT NULL ENABLE, "DESCRIPTION" VARCHAR2(60 BYTE), CONSTRAINT "TGM_NETWORKS_PK" PRIMARY KEY ("TGM_NETWORK_ID") 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA"; -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- -- ABC_FESA_DEVICECLASSES -- -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ CREATE TABLE "FESA"."FESA_DEVICECLASSES" ("CLASSNAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, "CLASS_ID" NUMBER, CONSTRAINT "FESA_DEVICECLASSES_PK" PRIMARY KEY ("CLASSNAME") 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA" ; CREATE OR REPLACE TRIGGER "FESA"."FESA_DEVICECLASSES_D_ABC" after delete on fesa_deviceclasses begin delete from abc_fesa_deviceclasses a where a.implementation = 'FESA' and not exists ( select 'x' from fesa_deviceclasses f where a.classname = f.classname ); end; / ALTER TRIGGER "FESA"."FESA_DEVICECLASSES_D_ABC" ENABLE; CREATE OR REPLACE TRIGGER "FESA"."FESA_DEVICECLASSES_IU_ABC" after insert or update on fesa_deviceclasses begin MERGE INTO abc_fesa_deviceclasses v USING (select classname from fesa_deviceclasses) t ON (v.classname = t.classname) WHEN MATCHED THEN UPDATE SET v.implementation = 'FESA' WHEN NOT MATCHED THEN INSERT (v.classname, v.implementation, v.lastupdated) VALUES (t.classname, 'FESA', sysdate); end; / ALTER TRIGGER "FESA"."FESA_DEVICECLASSES_IU_ABC" ENABLE; -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- -- ABC_COMPUTERS -- -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ CREATE TABLE "FESA"."ABC_COMPUTERS" ("COMPNAME" VARCHAR2(20 BYTE), "COMPTYPE" VARCHAR2(8 BYTE) CONSTRAINT "COMPUTERS_COMPTYPE_NN" NOT NULL ENABLE, "COMPNO" NUMBER(4,0), "DIRECTORY" VARCHAR2(8 BYTE), "COMPDESCRIP" VARCHAR2(40 BYTE), "PLSMACHINE" VARCHAR2(6 BYTE), "DIGIOMBNO" NUMBER(5,0), "RESETVAL" NUMBER(6,0), "LASTUSER" VARCHAR2(40 BYTE), "CONCENTRATOR" VARCHAR2(8 BYTE), "CONSPORT" NUMBER(5,0), "SERVER" VARCHAR2(8 BYTE), "SERVPORT" NUMBER(5,0), "BUILDING" VARCHAR2(5 BYTE), "ROOMCODE" VARCHAR2(10 BYTE), "RACK" VARCHAR2(20 BYTE), "HOSTNAME" VARCHAR2(20 BYTE), "CONSOLE" VARCHAR2(8 BYTE), "PRIORITY" NUMBER(3,0) DEFAULT 0, "TGM_NETWORK_ID" NUMBER(38,0), "TEL" VARCHAR2(4 BYTE), "OPERATIONAL_FLAG" VARCHAR2(2 BYTE), CONSTRAINT "COMPUTERS_COMPNAME_CHK" CHECK ( compname = lower(compname) ) ENABLE, CONSTRAINT "COMPUTERS_PK" PRIMARY KEY ("COMPNAME") 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA" ENABLE, CONSTRAINT "COMPUTERS_TGM_NETWORKS_FK" FOREIGN KEY ("TGM_NETWORK_ID") REFERENCES "FESA"."ABC_TGM_NETWORKS" ("TGM_NETWORK_ID") ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA" ; -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- -- ABC_DEVICES -- -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ CREATE TABLE "FESA"."ABC_DEVICES" ("DEVICE_ID" NUMBER(10,0), "DEVICENAME" VARCHAR2(30 BYTE) CONSTRAINT "DEVICES_DEVICENAME_NN" NOT NULL ENABLE, "ALIAS" VARCHAR2(30 BYTE), "ACCELERATOR" VARCHAR2(10 BYTE), "SUBSYSTEM" VARCHAR2(24 BYTE), "FECNAME" VARCHAR2(20 BYTE), "BUSTYPE" VARCHAR2(10 BYTE), "SERVERNAME" VARCHAR2(50 BYTE), "CLASSNAME" VARCHAR2(20 BYTE), "DEVICETYPE" VARCHAR2(20 BYTE), "PPM" CHAR(1 BYTE), "ALARMS" CHAR(1 BYTE), "DESCRIPTION" VARCHAR2(80 BYTE), "GM_MBNO" NUMBER(5,0), "SL_FAMILY" VARCHAR2(10 BYTE), "SL_MEMNUMBER" VARCHAR2(10 BYTE), "SL_ACCESSTYPE" VARCHAR2(5 BYTE), "SL_EQUIPMENTSORT" VARCHAR2(10 BYTE), "SL_MEMHISTID" NUMBER(10,0), "SL_LOCALEDU" VARCHAR2(1 BYTE), "SL_FIELD5" VARCHAR2(9 BYTE), "SL_FIELD6" VARCHAR2(9 BYTE), "SL_FIELD7" VARCHAR2(9 BYTE), "SL_FIELD8" VARCHAR2(9 BYTE), "SL_FIELD9" VARCHAR2(9 BYTE), "SL_FIELD10" VARCHAR2(9 BYTE), "DERIVED1" VARCHAR2(80 BYTE), "DERIVED2" VARCHAR2(80 BYTE), "DERIVED3" VARCHAR2(80 BYTE), "DERIVED4" VARCHAR2(80 BYTE), "DERIVED5" VARCHAR2(80 BYTE), "DERIVED6" VARCHAR2(80 BYTE), "DERIVED7" VARCHAR2(80 BYTE), "DERIVED8" VARCHAR2(80 BYTE), "DERIVED9" VARCHAR2(80 BYTE), "DERIVED10" VARCHAR2(80 BYTE), "INSTALL_CLASSVERSION" NUMBER(4,0), "INSTALL_GM_CLASSNO" NUMBER(5,0), "TEMPORARY" CHAR(1 BYTE), "SL_USERNAME" VARCHAR2(15 BYTE), "SL_DATETIME" DATE, "UPDATED" DATE, "UPDATER" VARCHAR2(20 BYTE), "TIMING_DOMAIN" VARCHAR2(10 BYTE), "REFIMPL" VARCHAR2(4 BYTE) DEFAULT 'DIR' CONSTRAINT "DEVICES_REFIMPL_NN" NOT NULL ENABLE, "CLASS_ID" NUMBER, CONSTRAINT "DEVICES_PK" PRIMARY KEY ("DEVICE_ID") 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA" ENABLE, CONSTRAINT "DEVICES_UK" UNIQUE ("DEVICENAME") 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA" ENABLE, CONSTRAINT "DEVICES_FECNAME_CHK" CHECK ( fecname = lower(fecname) ) ENABLE, CONSTRAINT "DEVICES_REFIMPL_CHK" CHECK ( refimpl in ('LSA', 'DIR', 'NONE') ) ENABLE, CONSTRAINT "DEVICES_COMPUTERS_FK" FOREIGN KEY ("FECNAME") REFERENCES "FESA"."ABC_COMPUTERS" ("COMPNAME") ON DELETE SET NULL DISABLE, CONSTRAINT "DEV_DEVC_CLASSNAME_FK" FOREIGN KEY ("CLASSNAME") REFERENCES "FESA"."ABC_FESA_DEVICECLASSES" ("CLASSNAME") ON DELETE CASCADE ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA" ; CREATE INDEX "FESA"."DEVICES5" ON "FESA"."ABC_DEVICES" ("SL_FAMILY", "SL_MEMNUMBER") 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA" ; CREATE INDEX "FESA"."DEVICES_ALIAS_I" ON "FESA"."ABC_DEVICES" ("ALIAS") 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA" ; CREATE SEQUENCE "FESA"."ABC_DEVICE_ID_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 12218 CACHE 20 NOORDER NOCYCLE; -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- -- CMW_DEVICES -- -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ CREATE OR REPLACE FORCE VIEW "FESA"."CMW_DEVICES" ("DEVICE_ID", "DEVICENAME", "ALIAS", "ACCELERATOR", "SUBSYSTEM", "FECNAME", "BUSTYPE", "SERVERNAME", "CLASSNAME", "DEVICETYPE", "PPM", "ALARMS", "DESCRIPTION", "GM_MBNO", "SL_FAMILY", "SL_MEMNUMBER", "SL_ACCESSTYPE", "SL_EQUIPMENTSORT", "SL_MEMHISTID", "SL_LOCALEDU", "SL_FIELD5", "SL_FIELD6", "SL_FIELD7", "SL_FIELD8", "SL_FIELD9", "SL_FIELD10", "DERIVED1", "DERIVED2", "DERIVED3", "DERIVED4", "DERIVED5", "DERIVED6", "DERIVED7", "DERIVED8", "DERIVED9", "DERIVED10", "INSTALL_CLASSVERSION", "INSTALL_GM_CLASSNO", "TEMPORARY", "SL_USERNAME", "SL_DATETIME", "UPDATED", "UPDATER", "TIMING_DOMAIN", "REFIMPL", "PROXYNAME") AS SELECT "DEVICE_ID", "DEVICENAME", "ALIAS", "ACCELERATOR", "SUBSYSTEM", "FECNAME", "BUSTYPE", "SERVERNAME", "CLASSNAME", "DEVICETYPE", "PPM", "ALARMS", "DESCRIPTION", "GM_MBNO", "SL_FAMILY", "SL_MEMNUMBER", "SL_ACCESSTYPE", "SL_EQUIPMENTSORT", "SL_MEMHISTID", "SL_LOCALEDU", "SL_FIELD5", "SL_FIELD6", "SL_FIELD7", "SL_FIELD8", "SL_FIELD9", "SL_FIELD10", "DERIVED1","DERIVED2", "DERIVED3", "DERIVED4", "DERIVED5", "DERIVED6", "DERIVED7", "DERIVED8", "DERIVED9", "DERIVED10", "INSTALL_CLASSVERSION", "INSTALL_GM_CLASSNO", "TEMPORARY", "SL_USERNAME", "SL_DATETIME", "UPDATED", "UPDATER", "TIMING_DOMAIN", "REFIMPL", null as "PROXYNAME" FROM "FESA"."ABC_DEVICES" order by "DEVICENAME"; -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- -- SERVERS_DIRECTORY -- -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ CREATE SEQUENCE "FESA"."SERVERS_DIRECTORY_ID_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1801 CACHE 20 NOORDER NOCYCLE; CREATE TABLE "FESA"."SERVERS_DIRECTORY" ("SERVERNAME" VARCHAR2(50 BYTE) CONSTRAINT "SDIR_SERVERNAME_NN" NOT NULL ENABLE, "LOCATION" VARCHAR2(1024 BYTE), "ID" NUMBER, "CONNECTED" TIMESTAMP (6) DEFAULT systimestamp CONSTRAINT "SERVERS_DIRECTORY_CONNECTED_NN" NOT NULL ENABLE, "UPDATED_BY_CMW" TIMESTAMP (6), "IP_ADDRESS" VARCHAR2(40 BYTE), "PROTECT_IP" VARCHAR2(1 BYTE) DEFAULT 'N' CONSTRAINT "SERVERS_DIR_PROTECT_IP_NN" NOT NULL ENABLE, "PROTECT_LOCATION" VARCHAR2(1 BYTE) DEFAULT 'N' CONSTRAINT "SERVERS_DIR_PROTECT_LOC_NN" NOT NULL ENABLE, "HOSTNAME" VARCHAR2(30 BYTE), CONSTRAINT "SERVERS_DIRECTORY_UK" UNIQUE ("SERVERNAME") 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA" ENABLE, CONSTRAINT "SERVERS_DIR_PROTECT_IP_CHK" CHECK (protect_ip in ('Y','N')) ENABLE, CONSTRAINT "SERVERS_DIR_PROTECT_LOC_CHK" CHECK (protect_location in ('Y','N')) ENABLE, CONSTRAINT "SERVERS_DIRECTORY_PK" PRIMARY KEY ("ID") 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FESA" ; CREATE OR REPLACE TRIGGER "FESA"."SERVERS_DIRECTORY_ID_CHECK" before insert on servers_directory for each row declare l_error_message varchar2 (4000) := 'Trigger servers_directory_id_check:'; begin if (:new.id is null) then select servers_directory_id_seq.nextval into :new.id from dual; end if; exception when others then l_error_message := l_error_message||substr (sqlerrm, 1, 500) ; -- error.report_and_stop (null, l_error_message) ; end; / ALTER TRIGGER "FESA"."SERVERS_DIRECTORY_ID_CHECK" ENABLE; CREATE OR REPLACE TRIGGER "FESA"."SDIR_BEFORE_UPDATE_FER" before update of location on servers_directory for each row declare l_error_message varchar2 (1000) := 'Trigger servers_directory_before_update_fer:'; begin :new.connected := systimestamp; exception when others then l_error_message := l_error_message||substr (sqlerrm, 1, 500) ; -- error.report_and_go (null, l_error_message) ; end; / ALTER TRIGGER "FESA"."SDIR_BEFORE_UPDATE_FER" ENABLE;