Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE "CURRENT_USER_ID"(
- USER_ID VARCHAR2(20 CHAR)
- );
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from ANNOTATION M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from ANNOTATION M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.USER_ISID from "AUDIT" M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from CONTAINER M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from CONTAINER M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from JOB M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from JOB M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from JOB_PARAMETER M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from JOB_PARAMETER M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from MODEL M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from MODEL M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from MODEL_ITEM M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from MODEL_ITEM M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from MODEL_PARAMETER M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from MODEL_PARAMETER M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from MODEL_STEP M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from MODEL_STEP M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from MODEL_STEP_PARAMETER M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from MODEL_STEP_PARAMETER M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.USER_ISID from PERMISSION_AUDIT M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.TARGET_USER_ISID from PERMISSION_AUDIT M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.USERNAME from SUBSCRIPTION M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.USERNAME from SUBSCRIPTION_MESSAGE M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from SVN_ENTRY M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from SVN_ENTRY M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from USER_PERMISSION M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from USER_PERMISSION M;
- Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.USER_ID from USER_PERMISSION M;
- CREATE TABLE "UNIQUE_USER_ID"(
- USER_ID VARCHAR2(20 CHAR)
- );
- Insert into "UNIQUE_USER_ID" (USER_ID) Select distinct M.USER_ID from "CURRENT_USER_ID" M;
- DROP TABLE "CURRENT_USER_ID";
- MERGE into "USER" U using "UNIQUE_USER_ID" ALL_U on (U.ID = ALL_U.USER_ID)
- WHEN MATCHED THEN UPDATE SET U.NAME = U.NAME
- WHEN NOT MATCHED THEN
- INSERT (ID, NAME, ACTIVE, CREATEDON, MODIFIEDON, CREATEDBY, MODIFIEDBY) VALUES (ALL_U.USER_ID, ALL_U.USER_ID, 1, CURRENT_TIMESTAMP,
- CURRENT_TIMESTAMP, 'system', 'system');
- DROP TABLE "UNIQUE_USER_ID";
- --FOREIGN KEY
- ALTER TABLE ANNOTATION ADD (
- CONSTRAINT ANNOTATION_FK_1
- FOREIGN KEY (CREATEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE ANNOTATION ADD (
- CONSTRAINT ANNOTATION_FK_2
- FOREIGN KEY (MODIFIEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE "AUDIT" ADD (
- CONSTRAINT AUDIT_FK_1
- FOREIGN KEY (USER_ISID)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE CONTAINER ADD (
- CONSTRAINT CONTAINER_FK_1
- FOREIGN KEY (CREATEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE CONTAINER ADD (
- CONSTRAINT CONTAINER_FK_2
- FOREIGN KEY (MODIFIEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE JOB ADD (
- CONSTRAINT JOB_FK_1
- FOREIGN KEY (CREATEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE JOB ADD (
- CONSTRAINT JOB_FK_2
- FOREIGN KEY (MODIFIEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE JOB_PARAMETER ADD (
- CONSTRAINT JOB_PARAMETER_FK_1
- FOREIGN KEY (CREATEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE JOB_PARAMETER ADD (
- CONSTRAINT JOB_PARAMETER_FK_2
- FOREIGN KEY (MODIFIEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE MODEL ADD (
- CONSTRAINT MODEL_FK_1
- FOREIGN KEY (CREATEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE MODEL ADD (
- CONSTRAINT MODEL_FK_2
- FOREIGN KEY (MODIFIEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE MODEL_ITEM ADD (
- CONSTRAINT MODEL_ITEM_FK_1
- FOREIGN KEY (CREATEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE MODEL_ITEM ADD (
- CONSTRAINT MODEL_ITEM_FK_2
- FOREIGN KEY (MODIFIEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE MODEL_PARAMETER ADD (
- CONSTRAINT MODEL_PARAMETER_FK_1
- FOREIGN KEY (CREATEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE MODEL_PARAMETER ADD (
- CONSTRAINT MODEL_PARAMETER_FK_2
- FOREIGN KEY (MODIFIEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE MODEL_STEP ADD (
- CONSTRAINT MODEL_STEP_FK_1
- FOREIGN KEY (CREATEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE MODEL_STEP ADD (
- CONSTRAINT MODEL_STEP_FK_2
- FOREIGN KEY (MODIFIEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE MODEL_STEP_PARAMETER ADD (
- CONSTRAINT MODEL_STEP_PARAMETER_FK_1
- FOREIGN KEY (CREATEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE MODEL_STEP_PARAMETER ADD (
- CONSTRAINT MODEL_STEP_PARAMETER_FK_2
- FOREIGN KEY (MODIFIEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE PERMISSION_AUDIT ADD (
- CONSTRAINT PERMISSION_AUDIT_FK_1
- FOREIGN KEY (USER_ISID)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE PERMISSION_AUDIT ADD (
- CONSTRAINT PERMISSION_AUDIT_FK_2
- FOREIGN KEY (TARGET_USER_ISID)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE SUBSCRIPTION ADD (
- CONSTRAINT SUBSCRIPTION_FK_1
- FOREIGN KEY (USERNAME)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE SUBSCRIPTION_MESSAGE ADD (
- CONSTRAINT SUBSCRIPTION_MESSAGE_FK_1
- FOREIGN KEY (USERNAME)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE SVN_ENTRY ADD (
- CONSTRAINT SVN_ENTRY_FK_CR_1
- FOREIGN KEY (CREATEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE SVN_ENTRY ADD (
- CONSTRAINT SVN_ENTRY_FK_CR_2
- FOREIGN KEY (MODIFIEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE USER_PERMISSION ADD (
- CONSTRAINT USER_PERMISSION_FK_1
- FOREIGN KEY (CREATEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE USER_PERMISSION ADD (
- CONSTRAINT USER_PERMISSION_FK_2
- FOREIGN KEY (MODIFIEDBY)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- ALTER TABLE USER_PERMISSION ADD (
- CONSTRAINT USER_PERMISSION_FK_3
- FOREIGN KEY (USER_ID)
- REFERENCES "USER" (ID)
- ON DELETE CASCADE
- ENABLE VALIDATE);
- --INDEX
- CREATE INDEX IDX_ANNOTATION_CREATEDBY ON ANNOTATION (CREATEDBY);
- CREATE INDEX IDX_ANNOTATION_MODIFIEDBY ON ANNOTATION (MODIFIEDBY);
- CREATE INDEX IDX_CONTAINER_CREATEDBY ON CONTAINER (CREATEDBY);
- CREATE INDEX IDX_CONTAINER_MODIFIEDBY ON CONTAINER (MODIFIEDBY);
- CREATE INDEX IDX_JOB_CREATEDBY ON JOB (CREATEDBY);
- CREATE INDEX IDX_JOB_MODIFIEDBY ON JOB (MODIFIEDBY);
- CREATE INDEX IDX_JOB_PARAMETER_CREATEDBY ON JOB_PARAMETER (CREATEDBY);
- CREATE INDEX IDX_JOB_PARAMETER_MODIFIEDBY ON JOB_PARAMETER (MODIFIEDBY);
- CREATE INDEX IDX_MODEL_CREATEDBY ON MODEL (CREATEDBY);
- CREATE INDEX IDX_MODEL_MODIFIEDBY ON MODEL (MODIFIEDBY);
- CREATE INDEX IDX_MODEL_ITEM_CREATEDBY ON MODEL_ITEM (CREATEDBY);
- CREATE INDEX IDX_MODEL_ITEM_MODIFIEDBY ON MODEL_ITEM (MODIFIEDBY);
- CREATE INDEX IDX_MODEL_PARAMETER_CREATEDBY ON MODEL_PARAMETER (CREATEDBY);
- CREATE INDEX IDX_MODEL_PARAMETER_MODIFIEDBY ON MODEL_PARAMETER (MODIFIEDBY);
- CREATE INDEX IDX_MODEL_STEP_CREATEDBY ON MODEL_STEP (CREATEDBY);
- CREATE INDEX IDX_MODEL_STEP_MODIFIEDBY ON MODEL_STEP (MODIFIEDBY);
- CREATE INDEX IDX_M_S_P_CREATEDBY ON MODEL_STEP_PARAMETER (CREATEDBY);
- CREATE INDEX IDX_M_S_P_MODIFIEDBY ON MODEL_STEP_PARAMETER (MODIFIEDBY);
- CREATE INDEX IDX_P_A_TARGET_ISID ON PERMISSION_AUDIT (TARGET_USER_ISID);
- CREATE INDEX IDX_P_A_ISID ON PERMISSION_AUDIT (USER_ISID);
- CREATE INDEX IDX_SUBSCRIPTION_USERNAME ON SUBSCRIPTION (USERNAME);
- CREATE INDEX IDX_SUBS_MESSAGE_USERNAME ON SUBSCRIPTION_MESSAGE (USERNAME);
- CREATE INDEX IDX_SVN_ENTRY_CREATEDBY ON SVN_ENTRY (CREATEDBY);
- CREATE INDEX IDX_SVN_ENTRY_MODIFIEDBY ON SVN_ENTRY (MODIFIEDBY);
- CREATE INDEX IDX_U_P_CREATEDBY ON USER_PERMISSION (CREATEDBY);
- CREATE INDEX IDX_U_P_MODIFIEDBY ON USER_PERMISSION (MODIFIEDBY);
- CREATE INDEX IDX_U_P_USER_ID ON USER_PERMISSION (USER_ID);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement