-- -- $Header: ocmrepcre.sql 17-oct-01.09:55:56 prabuck Exp $ -- -- ocmrepcre.sql -- -- Copyright (c) Oracle Corporation 2000, 2001. All Rights Reserved. -- -- NAME -- ocmrepcre.sql - Change Manager repostiory creation script. -- -- DESCRIPTION -- This is the main file for creating the repository objects for the latest -- version of change manager. -- -- NOTES -- -- -- MODIFIED (MM/DD/YY) -- prabuck 10/17/01 - widened dm_predicate column in vbz_exemplars -- prabuck 10/02/01 - added new column to VBZCHANGE_PLANS -- szhu 09/24/01 - Move PMO from destination level to plan level -- szhu 08/06/01 - PMO support for 9.0.2 -- abodge 07/20/01 - Add DM_PREDICATE column to VBZ$EXEMPLARS -- shuberma 12/26/00 - Naming constraints. -- lhan 12/13/00 - Fix bug 1459896 - add COMMIT_INTERVAL option -- dholail 10/25/00 - Adding an index on the history table -- shuberma 09/18/00 - -- shuberma 09/15/00 - New Create script. -- shuberma 09/15/00 - Created -- CREATE SEQUENCE "VBZ$DB_OBJ_NAMES_SEQ" INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E27 MINVALUE 1 NOCYCLE CACHE 20 NOORDER; CREATE SEQUENCE "VBZ$HISTORY_SEQ" INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E27 MINVALUE 1 NOCYCLE CACHE 20 NOORDER; CREATE SEQUENCE "VBZ$REP_OBJ_ID_SEQ" INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E27 MINVALUE 1 NOCYCLE CACHE 20 NOORDER; CREATE TABLE "VBZ$CHANGE_PLANS" ( "CP_ID" NUMBER NOT NULL, "CP_USER_NAME" VARCHAR2(256) NOT NULL, "CP_NAME" VARCHAR2(50) NOT NULL, "CP_SOURCE_DB" VARCHAR2(100), "CP_DESCRIPTION" VARCHAR2(2000), "CP_CREATE_DATE" DATE, "CP_OPTIONS" NUMBER DEFAULT 0 NOT NULL, "CP_TYPE" NUMBER DEFAULT 0, "CP_LATEST_VERSION_NO" NUMBER, "CP_SCOPE" LONG RAW, "DM_PERCENTAGE" NUMBER(3) DEFAULT 0 NOT NULL, "PMO_STORAGE_SCALE" NUMBER, "PMO_VALUE" BLOB, CONSTRAINT CP_PK PRIMARY KEY("CP_ID"), CONSTRAINT CP_UK UNIQUE("CP_NAME", "CP_TYPE", "CP_USER_NAME") ) ; CREATE TABLE "VBZ$COMPARISONS" ( "COMPARISON_ID" NUMBER, "USER_NAME" VARCHAR2(256), "NAME" VARCHAR2(50), "LEFT_NAME" VARCHAR2(50), "LEFT_OWNER" VARCHAR2(40), "LEFT_VERSION_NO" NUMBER, "RIGHT_NAME" VARCHAR2(50), "RIGHT_OWNER" VARCHAR2(40), "RIGHT_VERSION_NO" NUMBER, "OPTIONS" NUMBER, "LATEST_VERSION_NO" NUMBER, "SCOPE" LONG RAW, "DESCRIPTION" VARCHAR2(2000), CONSTRAINT CMP_PK UNIQUE("COMPARISON_ID"), CONSTRAINT CMP_UK UNIQUE("USER_NAME", "NAME") ); CREATE TABLE "VBZ$COMPARISON_RESULTS" ( "COMPARISON_ID" NUMBER NOT NULL, "VERSION_NO" NUMBER NOT NULL, "HISTORY_ID" NUMBER, "LAST_MOD_DATE" DATE, "COMPARE_DATA" LONG RAW, "LEFT_VERSION" NUMBER, "RIGHT_VERSION" NUMBER, CONSTRAINT CMP_RES_PK PRIMARY KEY("COMPARISON_ID", "VERSION_NO"), CONSTRAINT CMP_RES_UK UNIQUE("HISTORY_ID"), CONSTRAINT CMP_RES_CMP_FK FOREIGN KEY("COMPARISON_ID") REFERENCES "VBZ$COMPARISONS"("COMPARISON_ID") ON DELETE CASCADE ) ; CREATE TABLE "VBZ$DB_OBJ_NAMES" ( "CP_ID" NUMBER NOT NULL, "DB_OBJ_ID" NUMBER NOT NULL, "DB_OBJ_TYPE" NUMBER, "DB_OBJ_NAME" VARCHAR2(128) NOT NULL, "DB_OBJ_SCHEMA" VARCHAR2(30), "DB_OBJ_SCOPE" LONG RAW, CONSTRAINT ON_CP_FK FOREIGN KEY("CP_ID") REFERENCES "VBZ$CHANGE_PLANS"("CP_ID") ON DELETE CASCADE, CONSTRAINT ON_PK PRIMARY KEY("DB_OBJ_ID"), CONSTRAINT ON_UK UNIQUE("CP_ID", "DB_OBJ_TYPE", "DB_OBJ_NAME", "DB_OBJ_SCHEMA") ) ; CREATE TABLE "VBZ$DESTINATIONS" ( "CP_ID" NUMBER NOT NULL, "DB_NAME" VARCHAR2(256) NOT NULL, "DEST_DESCRIPTION" VARCHAR2(2000), "OPTIONS" NUMBER(4) DEFAULT 0 NOT NULL, "SCRATCH_TS" VARCHAR2(30), "VERSION_NO" NUMBER, "DEPLOYMENT_ID" NUMBER NOT NULL, "TRANS_STATUS" NUMBER, "TRANS_HISTORY_ID" NUMBER, "EXEC_STATUS" NUMBER, "EXEC_HISTORY_ID" NUMBER, "FLAGS" NUMBER, "MOD_DATE" DATE, "OPTION3_SINCEVERSION" NUMBER DEFAULT 0, "COMMIT_INTERVAL" NUMBER DEFAULT 0, CONSTRAINT DEST_PK PRIMARY KEY("DEPLOYMENT_ID"), CONSTRAINT DEST_UK UNIQUE("CP_ID", "DB_NAME", "VERSION_NO"), CONSTRAINT DEST_CP_FK FOREIGN KEY("CP_ID") REFERENCES "VBZ$CHANGE_PLANS"("CP_ID") ON DELETE CASCADE ); CREATE TABLE "VBZ$DIRECTIVES" ( "CP_ID" NUMBER NOT NULL, "DB_OBJ_ID" NUMBER NOT NULL, "VERSION_NO" NUMBER NOT NULL, "DEF_LENGTH" NUMBER DEFAULT 0, "DEFINITION" LONG RAW, "FLAGS" NUMBER DEFAULT 0, CONSTRAINT DIR_CP_FK FOREIGN KEY("CP_ID") REFERENCES "VBZ$CHANGE_PLANS"("CP_ID") ON DELETE CASCADE, CONSTRAINT DIR_PK PRIMARY KEY("DB_OBJ_ID", "VERSION_NO"), CONSTRAINT DIR_ON_FK FOREIGN KEY("DB_OBJ_ID") REFERENCES "VBZ$DB_OBJ_NAMES"("DB_OBJ_ID") ON DELETE CASCADE ); CREATE TABLE "VBZ$EDITED_SCRIPTS" ( "DEPLOYMENT_ID" NUMBER NOT NULL, "SCRIPT_LENGTH" NUMBER, "SCRIPT_CONTENT" LONG, "LINE_NUM" NUMBER DEFAULT -1, CONSTRAINT ES_PK PRIMARY KEY("DEPLOYMENT_ID", "LINE_NUM" ), CONSTRAINT ES_DEST_FK FOREIGN KEY("DEPLOYMENT_ID") REFERENCES "VBZ$DESTINATIONS"("DEPLOYMENT_ID") ON DELETE CASCADE ); CREATE TABLE "VBZ$EXEMPLARS" ( "CP_ID" NUMBER NOT NULL, "DB_OBJ_ID" NUMBER NOT NULL, "VERSION_NO" NUMBER NOT NULL, "OPTIONS" NUMBER, "DEF_LENGTH" NUMBER DEFAULT 0, "DEFINITION" LONG RAW, "FLAGS" NUMBER DEFAULT 0, "DM_PREDICATE" VARCHAR2(4000), CONSTRAINT EX_CP_FK FOREIGN KEY("CP_ID") REFERENCES "VBZ$CHANGE_PLANS"("CP_ID") ON DELETE CASCADE, CONSTRAINT EX_PK PRIMARY KEY("DB_OBJ_ID", "VERSION_NO"), CONSTRAINT EX_ON_FK FOREIGN KEY("DB_OBJ_ID") REFERENCES "VBZ$DB_OBJ_NAMES"("DB_OBJ_ID") ON DELETE CASCADE ); CREATE TABLE "VBZ$EX_UPDATES" ( "CP_ID" NUMBER NOT NULL, "DB_OBJ_ID" NUMBER NOT NULL, "VERSION_NO" NUMBER NOT NULL, "DEF_LENGTH" NUMBER DEFAULT 0, "DEFINITION" LONG RAW, "FLAGS" NUMBER DEFAULT 0, CONSTRAINT EXUPD_CP_FK FOREIGN KEY("CP_ID") REFERENCES "VBZ$CHANGE_PLANS"("CP_ID") ON DELETE CASCADE, CONSTRAINT EXUPD_PK PRIMARY KEY("DB_OBJ_ID", "VERSION_NO"), CONSTRAINT EXUPD_ON_FK FOREIGN KEY("DB_OBJ_ID") REFERENCES "VBZ$DB_OBJ_NAMES"("DB_OBJ_ID") ON DELETE CASCADE ); CREATE TABLE "VBZ$HISTORY" ( "HISTORY_ID" NUMBER NOT NULL, "ACTION_OWNER" VARCHAR(256), "OBJECT_TYPE" NUMBER, "OBJECT_OWNER" VARCHAR2(256), "OBJECT_NAME" VARCHAR2(50), "OBJECT_VERSION" NUMBER, "OPERATION" NUMBER, "OPERAND_1" VARCHAR2(64), "OPERAND_2" VARCHAR2(64), "STATUS" NUMBER, "MONITORED" NUMBER, "START_DATE" DATE, "END_DATE" DATE, "REP_OBJ_ID" NUMBER, CONSTRAINT HIST_PK PRIMARY KEY("HISTORY_ID") ); CREATE TABLE "VBZ$IMPACT_LOG" ( "DEPLOYMENT_ID" NUMBER NOT NULL, "LOG_LINE_NO" NUMBER NOT NULL, "LOG_LINE" VARCHAR2(2000) NOT NULL, CONSTRAINT IL_PK PRIMARY KEY("DEPLOYMENT_ID", "LOG_LINE_NO"), CONSTRAINT IL_DEST_FK FOREIGN KEY("DEPLOYMENT_ID") REFERENCES "VBZ$DESTINATIONS"("DEPLOYMENT_ID") ON DELETE CASCADE ); CREATE TABLE "VBZ$OBJECT_GRANTS" ( "CP_ID" NUMBER NOT NULL, "GRANTOR_OBJ_ID" NUMBER, "GRANTEE_OBJ_ID" NUMBER, "WITH_GRANT_OPT" VARCHAR2(3) NOT NULL, "PRIVILEGE" VARCHAR2(50) NOT NULL, "TARGET_OBJ_ID" NUMBER, "SUBOBJECT_NAME" VARCHAR2(30), "VERSION_NO" NUMBER, "FLAGS" NUMBER, CONSTRAINT OG_CP_FK FOREIGN KEY("CP_ID") REFERENCES "VBZ$CHANGE_PLANS"("CP_ID") ON DELETE CASCADE, CONSTRAINT OG_TEE_ON_FK FOREIGN KEY("GRANTEE_OBJ_ID") REFERENCES "VBZ$DB_OBJ_NAMES"("DB_OBJ_ID") ON DELETE CASCADE, CONSTRAINT OG_TOR_ON_FK FOREIGN KEY("GRANTOR_OBJ_ID") REFERENCES "VBZ$DB_OBJ_NAMES"("DB_OBJ_ID") ON DELETE CASCADE, CONSTRAINT OG_TAR_ON_FK FOREIGN KEY("TARGET_OBJ_ID") REFERENCES "VBZ$DB_OBJ_NAMES"("DB_OBJ_ID") ON DELETE CASCADE, CONSTRAINT OG_UK UNIQUE("CP_ID", "GRANTOR_OBJ_ID", "GRANTEE_OBJ_ID", "PRIVILEGE", "TARGET_OBJ_ID", "SUBOBJECT_NAME", "VERSION_NO") ); CREATE TABLE "VBZ$OUTPUT_LOG" ( "DEPLOYMENT_ID" NUMBER NOT NULL, "HISTORY_ID" NUMBER NOT NULL, "LOG_LINE_NO" NUMBER NOT NULL, "LOG_LINE" LONG, CONSTRAINT OL_PK PRIMARY KEY("HISTORY_ID", "LOG_LINE_NO") ); CREATE TABLE "VBZ$ROLE_GRANTS" ( "CP_ID" NUMBER NOT NULL, "GRANTEE_OBJ_ID" NUMBER, "WITH_ADMIN_OPT" VARCHAR2(3) NOT NULL, "DEFAULT_FLAG" VARCHAR2(3) NOT NULL, "ROLE_OBJ_ID" NUMBER, "VERSION_NO" NUMBER, "FLAGS" NUMBER, CONSTRAINT RG_CP_FK FOREIGN KEY("CP_ID") REFERENCES "VBZ$CHANGE_PLANS"("CP_ID") ON DELETE CASCADE, CONSTRAINT RG_TEE_ON_FK FOREIGN KEY("GRANTEE_OBJ_ID") REFERENCES "VBZ$DB_OBJ_NAMES"("DB_OBJ_ID") ON DELETE CASCADE, CONSTRAINT RG_OBJ_ON_FK FOREIGN KEY("ROLE_OBJ_ID") REFERENCES "VBZ$DB_OBJ_NAMES"("DB_OBJ_ID") ON DELETE CASCADE, CONSTRAINT RG_UK UNIQUE("CP_ID", "GRANTEE_OBJ_ID", "ROLE_OBJ_ID", "VERSION_NO") ); CREATE TABLE "VBZ$SCHEMAMAPS" ( "OCM_REP_OBJ_ID" NUMBER NOT NULL, "LEFT_SCHEMA" VARCHAR2(30), "RIGHT_SCHEMA" VARCHAR2(30) ); CREATE TABLE "VBZ$SCRIPTS" ( "DEPLOYMENT_ID" NUMBER NOT NULL, "SCRIPT_LINE_NO" NUMBER NOT NULL, "SCRIPT_STEP_NO" NUMBER DEFAULT 0 NOT NULL, "SCRIPT_LINE_TYPE" VARCHAR2(15) DEFAULT 'unknown' NOT NULL, "SCRIPT_SECTION" VARCHAR2(10) DEFAULT 'unknown' NOT NULL, "SCRIPT_LINE" LONG, CONSTRAINT SCR_PK PRIMARY KEY("DEPLOYMENT_ID", "SCRIPT_LINE_NO"), CONSTRAINT SCR_DEST_FK FOREIGN KEY("DEPLOYMENT_ID") REFERENCES "VBZ$DESTINATIONS"("DEPLOYMENT_ID") ON DELETE CASCADE ); CREATE TABLE "VBZ$SYS_PRIV_GRANTS" ( "CP_ID" NUMBER NOT NULL, "GRANTEE_OBJ_ID" NUMBER, "WITH_ADMIN_OPT" VARCHAR2(3) NOT NULL, "PRIVILEGE" VARCHAR2(50) NOT NULL, "VERSION_NO" NUMBER, "FLAGS" NUMBER, CONSTRAINT SG_CP_FK FOREIGN KEY("CP_ID") REFERENCES "VBZ$CHANGE_PLANS"("CP_ID") ON DELETE CASCADE, CONSTRAINT SG_TEE_ON_FK FOREIGN KEY("GRANTEE_OBJ_ID") REFERENCES "VBZ$DB_OBJ_NAMES"("DB_OBJ_ID") ON DELETE CASCADE, CONSTRAINT SG_UK UNIQUE("CP_ID", "GRANTEE_OBJ_ID", "PRIVILEGE", "VERSION_NO") ); CREATE TABLE "VBZ$VERSION" ( "OCM_REP_OBJ_ID" NUMBER NOT NULL, "VERSION_NO" NUMBER NOT NULL, "FLAGS" NUMBER, "LAST_MOD_DATE" DATE, "DESCRIPTION" VARCHAR2(2000), CONSTRAINT VER_PK PRIMARY KEY("OCM_REP_OBJ_ID", "VERSION_NO"), CONSTRAINT VER_CP_FK FOREIGN KEY("OCM_REP_OBJ_ID") REFERENCES "VBZ$CHANGE_PLANS"("CP_ID") ON DELETE CASCADE ); CREATE INDEX "OTS_INDEX" ON "VBZ$HISTORY" ( "OBJECT_OWNER", "OBJECT_TYPE", "STATUS" ) ; CREATE INDEX "TS_INDEX" ON "VBZ$HISTORY" ( "OBJECT_TYPE", "STATUS" ); CREATE INDEX "DATE_INDEX" ON "VBZ$HISTORY" ( "START_DATE" ) REVERSE; CREATE INDEX "TON_INDEX" ON "VBZ$HISTORY" ( "OBJECT_TYPE", "OBJECT_OWNER", "OBJECT_NAME" ) ; create index vbz$exemplars_cp_id_indx on vbz$exemplars(cp_id); create index vbz$directives_cp_id_indx on vbz$directives(cp_id); CREATE INDEX VBZ$OG_GEE_OIDX ON VBZ$OBJECT_GRANTS (GRANTEE_OBJ_ID); CREATE INDEX VBZ$OG_GOR_OIDX ON VBZ$OBJECT_GRANTS (GRANTOR_OBJ_ID); CREATE INDEX VBZ$OG_TGT_OIDX ON VBZ$OBJECT_GRANTS (target_obj_id); CREATE INDEX VBZ$RG_GEE_OIDX ON VBZ$ROLE_GRANTS (grantee_obj_id); CREATE INDEX VBZ$RG_ROLE_OIDX ON VBZ$ROLE_GRANTS (role_obj_id); CREATE INDEX VBZ$SG_GEE_OIDX ON VBZ$SYS_PRIV_GRANTS (grantee_obj_id);