/************************************************************************************************************************ * * * Date: November 02, 2002 04:00:34 * * This upgrade script was automatically generated by DBDiff for Oracle (www.dbdiff.com) * * SOURCE DATABASE: DEMO@SERVER * TARGET DATABASE: SCOTT@SERVER * * This script should be executed as SCOTT in database SCOTT@SERVER except where otherwise noted. * * IMPORTANT: As with any script, you should always perform a sanity check before executing it in your target database. * * ************************************************************************************************************************/ /************************************************************************************************************************ * * tables * * 10 tables were found in DEMO@SERVER * 4 tables were found in SCOTT@SERVER * ************************************************************************************************************************/ /************************************************************************************************************************ * * The following statements will drop tables that DO NOT exist in DEMO@SERVER * ************************************************************************************************************************/ DROP TABLE BONUS; DROP TABLE DEPT; DROP TABLE EMP; DROP TABLE SALGRADE; /************************************************************************************************************************ * * The following statements will create tables that DO NOT exist in SCOTT@SERVER * ************************************************************************************************************************/ /* CUSTOMER */ CREATE TABLE CUSTOMER ( CUSTOMER_ID NUMBER(6) NOT NULL ,NAME VARCHAR2(45) NULL ,ADDRESS VARCHAR2(40) NULL ,CITY VARCHAR2(30) NULL ,STATE VARCHAR2(2) NULL ,ZIP_CODE VARCHAR2(9) NULL ,AREA_CODE NUMBER(3) NULL ,PHONE_NUMBER NUMBER(7) NULL ,SALESPERSON_ID NUMBER(4) NULL ,CREDIT_LIMIT NUMBER(9,2) NULL ,COMMENTS LONG NULL ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE USER_DATA STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 ) ; ALTER TABLE CUSTOMER ADD ( CONSTRAINT SYS_C00574 PRIMARY KEY ( CUSTOMER_ID ) USING INDEX PCTFREE INITRANS MAXTRANS TABLESPACE ) ; ALTER TABLE CUSTOMER ADD ( CONSTRAINT SYS_C00584 FOREIGN KEY ( SALESPERSON_ID ) REFERENCES EMPLOYEE ( EMPLOYEE_ID ) ) ; ALTER TABLE CUSTOMER ADD ( CHECK -- original system constraint name was SYS_C00553 ( CUSTOMER_ID > 0 ) ) ; ALTER TABLE CUSTOMER ADD ( CHECK -- original system constraint name was SYS_C00554 ( STATE = UPPER(STATE) ) ) ; ALTER TABLE CUSTOMER ADD ( CHECK -- original system constraint name was SYS_C00555 ( LENGTH(NVL(ZIP_CODE, '99999')) IN (5, 9) ) ) ; CREATE UNIQUE INDEX I_CUSTOMER$CUSTOMER_ID ON CUSTOMER ( CUSTOMER_ID ) PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE USER_DATA ; /* DEPARTMENT */ CREATE TABLE DEPARTMENT ( DEPARTMENT_ID NUMBER(2) NOT NULL ,NAME VARCHAR2(14) NULL ,LOCATION_ID NUMBER(3) NULL ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE USER_DATA STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 ) ; ALTER TABLE DEPARTMENT ADD ( CONSTRAINT SYS_C00575 PRIMARY KEY ( DEPARTMENT_ID ) USING INDEX PCTFREE INITRANS MAXTRANS TABLESPACE ) ; ALTER TABLE DEPARTMENT ADD ( CONSTRAINT SYS_C00585 FOREIGN KEY ( LOCATION_ID ) REFERENCES LOCATION ( LOCATION_ID ) ) ; CREATE UNIQUE INDEX I_DEPARTMENT$DEPARTMENT_ID ON DEPARTMENT ( DEPARTMENT_ID ) PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE USER_DATA ; /* EMPLOYEE */ CREATE TABLE EMPLOYEE ( EMPLOYEE_ID NUMBER(4) NOT NULL ,LAST_NAME VARCHAR2(15) NULL ,FIRST_NAME VARCHAR2(15) NULL ,MIDDLE_INITIAL VARCHAR2(1) NULL ,JOB_ID NUMBER(3) NULL ,MANAGER_ID NUMBER(4) NULL ,HIRE_DATE DATE NULL ,SALARY NUMBER(7,2) NULL ,COMMISSION NUMBER(7,2) NULL ,DEPARTMENT_ID NUMBER(2) NULL ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE USER_DATA STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 ) ; ALTER TABLE EMPLOYEE ADD ( CONSTRAINT SYS_C00576 PRIMARY KEY ( EMPLOYEE_ID ) USING INDEX PCTFREE INITRANS MAXTRANS TABLESPACE ) ; ALTER TABLE EMPLOYEE ADD ( CONSTRAINT SYS_C00586 FOREIGN KEY ( JOB_ID ) REFERENCES JOB ( JOB_ID ) ) ; ALTER TABLE EMPLOYEE ADD ( CONSTRAINT SYS_C00587 FOREIGN KEY ( MANAGER_ID ) REFERENCES EMPLOYEE ( EMPLOYEE_ID ) ) ; ALTER TABLE EMPLOYEE ADD ( CONSTRAINT SYS_C00588 FOREIGN KEY ( DEPARTMENT_ID ) REFERENCES DEPARTMENT ( DEPARTMENT_ID ) ) ; CREATE UNIQUE INDEX I_EMPLOYEE$EMPLOYEE_ID ON EMPLOYEE ( EMPLOYEE_ID ) PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE USER_DATA ; /* ITEM */ CREATE TABLE ITEM ( ORDER_ID NUMBER(4) NOT NULL ,ITEM_ID NUMBER(4) NOT NULL ,PRODUCT_ID NUMBER(6) NULL ,ACTUAL_PRICE NUMBER(8,2) NULL ,QUANTITY NUMBER(8) NULL ,TOTAL NUMBER(8,2) NULL ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE USER_DATA STORAGE ( INITIAL 10K NEXT 16K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 ) ; ALTER TABLE ITEM ADD ( CONSTRAINT SYS_C00577 PRIMARY KEY ( ORDER_ID ,ITEM_ID ) USING INDEX PCTFREE INITRANS MAXTRANS TABLESPACE ) ; ALTER TABLE ITEM ADD ( CONSTRAINT SYS_C00589 FOREIGN KEY ( ORDER_ID ) REFERENCES SALES_ORDER ( ORDER_ID ) ) ; ALTER TABLE ITEM ADD ( CONSTRAINT SYS_C00590 FOREIGN KEY ( PRODUCT_ID ) REFERENCES PRODUCT ( PRODUCT_ID ) ) ; CREATE UNIQUE INDEX I_ITEM ON ITEM ( ORDER_ID ,ITEM_ID ) PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE USER_DATA ; /* JOB */ CREATE TABLE JOB ( JOB_ID NUMBER(3) NOT NULL ,FUNCTION VARCHAR2(30) NULL ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE USER_DATA STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 ) ; ALTER TABLE JOB ADD ( CONSTRAINT SYS_C00578 PRIMARY KEY ( JOB_ID ) USING INDEX PCTFREE INITRANS MAXTRANS TABLESPACE ) ; CREATE UNIQUE INDEX I_JOB$JOB_ID ON JOB ( JOB_ID ) PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE USER_DATA ; /* LOCATION */ CREATE TABLE LOCATION ( LOCATION_ID NUMBER(3) NOT NULL ,REGIONAL_GROUP VARCHAR2(20) NULL ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE USER_DATA STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 ) ; ALTER TABLE LOCATION ADD ( CONSTRAINT SYS_C00579 PRIMARY KEY ( LOCATION_ID ) USING INDEX PCTFREE INITRANS MAXTRANS TABLESPACE ) ; CREATE UNIQUE INDEX I_LOCATION$LOCATION_ID ON LOCATION ( LOCATION_ID ) PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE USER_DATA ; /* PRICE */ CREATE TABLE PRICE ( PRODUCT_ID NUMBER(6) NOT NULL ,LIST_PRICE NUMBER(8,2) NULL ,MIN_PRICE NUMBER(8,2) NULL ,START_DATE DATE NOT NULL ,END_DATE DATE NULL ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE USER_DATA STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 ) ; ALTER TABLE PRICE ADD ( CONSTRAINT SYS_C00580 PRIMARY KEY ( PRODUCT_ID ,START_DATE ) USING INDEX PCTFREE INITRANS MAXTRANS TABLESPACE ) ; ALTER TABLE PRICE ADD ( CONSTRAINT SYS_C00591 FOREIGN KEY ( PRODUCT_ID ) REFERENCES PRODUCT ( PRODUCT_ID ) ) ; ALTER TABLE PRICE ADD ( CHECK -- original system constraint name was SYS_C00566 ( LIST_PRICE IS NULL OR MIN_PRICE IS NULL OR MIN_PRICE <= LIST_PRICE ) ) ; ALTER TABLE PRICE ADD ( CHECK -- original system constraint name was SYS_C00567 ( END_DATE IS NULL OR START_DATE <= END_DATE ) ) ; CREATE UNIQUE INDEX I_PRICE ON PRICE ( PRODUCT_ID ,START_DATE ) PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE USER_DATA ; /* PRODUCT */ CREATE TABLE PRODUCT ( PRODUCT_ID NUMBER(6) NOT NULL ,DESCRIPTION VARCHAR2(30) NULL ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE USER_DATA STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 ) ; ALTER TABLE PRODUCT ADD ( CONSTRAINT SYS_C00581 PRIMARY KEY ( PRODUCT_ID ) USING INDEX PCTFREE INITRANS MAXTRANS TABLESPACE ) ; CREATE UNIQUE INDEX I_PRODUCT$PRODUCT_ID ON PRODUCT ( PRODUCT_ID ) PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE USER_DATA ; /* SALARY_GRADE */ CREATE TABLE SALARY_GRADE ( GRADE_ID NUMBER(3) NOT NULL ,LOWER_BOUND NUMBER(7,2) NULL ,UPPER_BOUND NUMBER(7,2) NULL ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE USER_DATA STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 ) ; ALTER TABLE SALARY_GRADE ADD ( CONSTRAINT SYS_C00582 PRIMARY KEY ( GRADE_ID ) USING INDEX PCTFREE INITRANS MAXTRANS TABLESPACE ) ; ALTER TABLE SALARY_GRADE ADD ( CHECK -- original system constraint name was SYS_C00570 ( (LOWER_BOUND IS NULL AND UPPER_BOUND IS NOT NULL) OR (LOWER_BOUND IS NOT NULL AND UPPER_BOUND IS NULL) OR (LOWER_BOUND <= UPPER_BOUND) ) ) ; CREATE UNIQUE INDEX I_SALARY_GRADE$GRADE_ID ON SALARY_GRADE ( GRADE_ID ) PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE USER_DATA ; /* SALES_ORDER */ CREATE TABLE SALES_ORDER ( ORDER_ID NUMBER(4) NOT NULL ,ORDER_DATE DATE NULL ,CUSTOMER_ID NUMBER(6) NULL ,SHIP_DATE DATE NULL ,TOTAL NUMBER(8,2) NULL ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE USER_DATA STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 ) ; ALTER TABLE SALES_ORDER ADD ( CONSTRAINT SYS_C00583 PRIMARY KEY ( ORDER_ID ) USING INDEX PCTFREE INITRANS MAXTRANS TABLESPACE ) ; ALTER TABLE SALES_ORDER ADD ( CONSTRAINT SYS_C00592 FOREIGN KEY ( CUSTOMER_ID ) REFERENCES CUSTOMER ( CUSTOMER_ID ) ) ; ALTER TABLE SALES_ORDER ADD ( CHECK -- original system constraint name was SYS_C00573 ( TOTAL >= 0 ) ) ; CREATE UNIQUE INDEX I_SALES_ORDER$ORDER_ID ON SALES_ORDER ( ORDER_ID ) PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE USER_DATA ; /************************************************************************************************************************ * * triggers * * 0 triggers were found in DEMO@SERVER * 0 triggers were found in SCOTT@SERVER * ************************************************************************************************************************/ /************************************************************************************************************************ * * views * * 1 views were found in DEMO@SERVER * 0 views were found in SCOTT@SERVER * ************************************************************************************************************************/ /************************************************************************************************************************ * * The following statements will create views that DO NOT exist in SCOTT@SERVER * ************************************************************************************************************************/ /* SALES */ CREATE OR REPLACE VIEW SALES (SALESPERSON_ID, CUSTOMER_ID, CUSTOMER, PRODUCT_ID, PRODUCT, AMOUNT ) AS SELECT SALESPERSON_ID, SALES_ORDER.CUSTOMER_ID, CUSTOMER.NAME CUSTOMER, PRODUCT.PRODUCT_ID, DESCRIPTION PRODUCT, SUM(ITEM.TOTAL) AMOUNT FROM SALES_ORDER, ITEM, CUSTOMER, PRODUCT WHERE SALES_ORDER.ORDER_ID = ITEM.ORDER_ID AND SALES_ORDER.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID AND ITEM.PRODUCT_ID = PRODUCT.PRODUCT_ID GROUP BY SALESPERSON_ID, SALES_ORDER.CUSTOMER_ID, CUSTOMER.NAME, PRODUCT.PRODUCT_ID, DESCRIPTION / /************************************************************************************************************************ * * functions * * 0 functions were found in DEMO@SERVER * 0 functions were found in SCOTT@SERVER * ************************************************************************************************************************/ /************************************************************************************************************************ * * packages * * 0 packages were found in DEMO@SERVER * 0 packages were found in SCOTT@SERVER * ************************************************************************************************************************/ /************************************************************************************************************************ * * packages body * * 0 packages body were found in DEMO@SERVER * 0 packages body were found in SCOTT@SERVER * ************************************************************************************************************************/ /************************************************************************************************************************ * * stored procedures * * 0 stored procedures were found in DEMO@SERVER * 0 stored procedures were found in SCOTT@SERVER * ************************************************************************************************************************/ /************************************************************************************************************************ * * types * * 0 types were found in DEMO@SERVER * 0 types were found in SCOTT@SERVER * ************************************************************************************************************************/ /************************************************************************************************************************ * * types body * * 0 types body were found in DEMO@SERVER * 0 types body were found in SCOTT@SERVER * ************************************************************************************************************************/ --- End of DBDiff for Oracle --- Elapsed Time: 00:00:01 ---