SQL> @dba_compare_schemas ===================== Compare Schema Script ===================== USAGE: ======================================================================== This SQL script should be run while connected to the the Oracle database as one of the schemas you would like to compare. You will be prompted to enter the Oracle username, password and Oracle Net Service Name of the second schema you would like to compare against. Lastly, you will be ased for the filename of the report you would like this script to create for all generated discrepencies. (You can hit [ENTER] to accept the default file name.) NOTE: ======================================================================== The following database objects will be created for use by this script: - Database Link (remote_schema_link) - Table (schema_compare_temp) Both objects will be dropped at the end of this script. Enter username for remote schema: scott2 Enter password for remote schema: Enter Oracle Net service name for remote schema: ora920_alex Specify the Discrepency Report File Name ====================================================================== The default report file name is compare_scott_scott2_ora920_alex.lst. To use this name, press [ENTER] to continue, otherwise enter an alternative. Enter value for report_name: Using the report name: compare_scott_scott2_ora920_alex Report Date and Time ------------------------- 15-NOV-2004 11:35:23 Local Schema Remote Schema ----------------------------------- ----------------------------------- SCOTT@ORA920.IDEVELOPMENT.INFO SCOTT2@ORA920.IDEVELOPMENT.INFO ============= END OF REPORT ============= Report output written to compare_scott_scott2_ora920_alex.lst ============================================================== SQL> ##################################################################################################### GENERATED REPORT compare_scott_scott2_ora920_alex.lst ##################################################################################################### Report Date and Time ------------------------- 15-NOV-2004 11:35:23 Local Schema Remote Schema ----------------------------------- ----------------------------------- SCOTT@ORA920.IDEVELOPMENT.INFO SCOTT2@ORA920.IDEVELOPMENT.INFO +----------------------------------------------------------------------+ | OBJECT SUMMARY | +----------------------------------------------------------------------+ ======================================================== Objects missing from local schema - (Summary) ======================================================== Object Type Object Count ------------------------------ ------------ FUNCTION 1 PACKAGE 1 PROCEDURE 1 SYNONYM 1 TABLE 1 VIEW 1 6 rows selected. ======================================================== Extraneous objects in local schema - (Summary) ======================================================== Object Type Object Count ------------------------------ ------------ DATABASE LINK 1 INDEX 1 LOB 1 SEQUENCE 1 SYNONYM 1 TABLE 9 TRIGGER 1 TYPE 1 VIEW 3 9 rows selected. +----------------------------------------------------------------------+ | PRIVILEGE DIFFERENCES | +----------------------------------------------------------------------+ ======================================================== Role privilege discrepencies ======================================================== Granted Role Schema Admin Option? Default Role O/S Granted ---------------------- ------- ------------- ---------------------- ----------- AQ_ADMINISTRATOR_ROLE Remote NO YES NO CONNECT Local NO YES NO 2 rows selected. ======================================================== System privilege discrepencies ======================================================== Privilege Schema Admin Option? ------------------------- ------- ------------- CREATE USER Local NO 1 row selected. ======================================================== Object-level grant discrepencies ======================================================== Owner Table Name Schema Grantee Privilege Grantable? --------------- ------------------------- ------- --------------- ------------------------- ---------- HR EMPLOYEES Local SCOTT ALTER NO HR EMPLOYEES Local SCOTT DEBUG NO HR EMPLOYEES Local SCOTT DELETE NO HR EMPLOYEES Local SCOTT FLASHBACK NO HR EMPLOYEES Local SCOTT INDEX NO HR EMPLOYEES Local SCOTT INSERT NO HR EMPLOYEES Local SCOTT ON COMMIT REFRESH NO HR EMPLOYEES Local SCOTT QUERY REWRITE NO HR EMPLOYEES Local SCOTT REFERENCES NO HR EMPLOYEES Local SCOTT SELECT NO HR EMPLOYEES Local SCOTT UPDATE NO 11 rows selected. +----------------------------------------------------------------------+ | OBJECT DIFFERENCES | +----------------------------------------------------------------------+ ======================================================== Objects missing from local schema ======================================================== Object Name Object Type ------------------------------ ------------------------------ BAD_FUNCTION FUNCTION BAD_PACKAGE PACKAGE BAD_PROC PROCEDURE DEPARTMENT SYNONYM TABLE3 TABLE ANOTHER VIEW 6 rows selected. ======================================================== Extraneous objects in local schema ======================================================== Object Name Object Type ------------------------------ ------------------------------ TEMP_LINK.IDEVELOPMENT.INFO DATABASE LINK SYS_C INDEX SYS_LOB LOB EMP_SEQ SEQUENCE EMPLOYEE SYNONYM CREATE$JAVA$LOB$TABLE TABLE EXTENTS TABLE JAVA$CLASS$MD5$TABLE TABLE JAVA$OPTIONS TABLE PEOPLE TABLE TABLE2 TABLE TABLE_TEMP1 TABLE TEST TABLE TEST2 TABLE DEPT_TRG1 TRIGGER POINTTYPE TYPE ANOTHER_VIEW VIEW BAD_VIEW VIEW DEPT_V2 VIEW 19 rows selected. ======================================================== Objects in local schema that are not valid ======================================================== Object Name Object Type STATUS ------------------------------ ------------------------------ ------- ANOTHER_VIEW VIEW INVALID BAD_VIEW VIEW INVALID 2 rows selected. ======================================================== Objects in remote schema that are not valid ======================================================== Object Name Object Type STATUS ------------------------------ ------------------------------ ------- ANOTHER VIEW INVALID BAD_FUNCTION FUNCTION INVALID BAD_PACKAGE PACKAGE INVALID BAD_PROC PROCEDURE INVALID 4 rows selected. +----------------------------------------------------------------------+ | TABLE COLUMN DIFFERENCES | +----------------------------------------------------------------------+ ======================================================== Table columns missing from one schema (Discrepencies are not listed in column order) ======================================================== Table Name Column Name Missing in Schema ------------------------------ ------------------------------ ----------------- TABLE1 NAME Local 1 row selected. ======================================================== Datatype discrepencies for table columns that exist in both schemas ======================================================== Table Name Column Name Schema Nullable Data Type Length Precision Scale Length of Default Value ------------------------------ ------------------------------ ------- -------- --------- ------ --------- ----- ----------------------- TEST4 NUM Local Y NUMBER 22 15 0 TEST4 NUM Remote Y NUMBER 22 16 0 TEST6 NAME Local N VARCHAR2 100 TEST6 NAME Remote Y VARCHAR2 100 4 rows selected. +----------------------------------------------------------------------+ | INDEX DIFFERENCES | +----------------------------------------------------------------------+ ======================================================== Index discrepencies for indexes that exist in both schemas ======================================================== Index Name Schema UNIQUENES Table Name Column Name Order ------------------------------ ------- --------- ------------------------------ ------------------------------ ----- TEST4_IDX Local NONUNIQUE TEST4 NUM 1 TEST4_IDX Remote UNIQUE TEST4 NUM 1 TEST5_IDX Local NONUNIQUE TEST5 ID 1 TEST5_IDX Local NONUNIQUE TEST5 NAME 2 TEST5_IDX Remote NONUNIQUE TEST5 NAME 1 TEST5_IDX Remote NONUNIQUE TEST5 ID 2 6 rows selected. +----------------------------------------------------------------------+ | CONSTRAINT DIFFERENCES | +----------------------------------------------------------------------+ ======================================================== Constraint discrepencies for tables that exist in both schemas ======================================================== Constraint Constraint Table R Constraint Delete Object Name Schema Type Name Name Rule Status Text --------------- ------- ---------- ------------------------- --------------- ---------- --------- -------------------- EMP_FK Local R EMP DEPT_PK CASCADE ENABLED EMP_FK Remote R EMP DEPT_PK NO ACTION ENABLED SYS_C Local C TEST6 ENABLED "NAME" IS NOT NULL 3 rows selected. +----------------------------------------------------------------------+ | SEQUENCE DIFFERENCES | +----------------------------------------------------------------------+ ======================================================== Sequence discrepencies ======================================================== Sequence Min. Max. Increment Cycle Order Cache Name Schema Value Value By Flag Flag Size --------------- ------- ---------- ---------- ---------- ----- ----- ---------- EMP_SEQ Local 1 1.0000E+27 1 N N 20 1 row selected. +----------------------------------------------------------------------+ | PRIVATE SYNONYM DIFFERENCES | +----------------------------------------------------------------------+ ======================================================== Private synonym discrepencies ======================================================== Synonym Table Table DB Name Schema Owner Name Link Name --------------- ------- ---------- ------------------------------ ------------------------- DEPARTMENT Remote SCOTT2 DEPT EMPLOYEE Local SCOTT EMP 2 rows selected. +----------------------------------------------------------------------+ | PL/SQL DIFFERENCES | +----------------------------------------------------------------------+ ======================================================== Source code discrepencies for all packages, procedures, and functions that exist in both schemas (CASE SENSITIVE COMPARISON) ======================================================== Source Source Number Name Type Discrepencies ------------------------- -------------------- ------------- TEST_PROC1 PROCEDURE 3 TEST_PROC2 PROCEDURE 2 2 rows selected. ======================================================== Source code discrepencies for packages, procedures, and functions that exist in both schemas (CASE INSENSITIVE COMPARISON) ======================================================== Source Source Number Name Type Discrepencies ------------------------- -------------------- ------------- TEST_PROC1 PROCEDURE 3 1 row selected. +----------------------------------------------------------------------+ | TRIGGER DIFFERENCES | +----------------------------------------------------------------------+ ======================================================== Trigger discrepencies ======================================================== Trigger Trigger Triggering Table Referencing When Name Schema Type Event Name Names Clause Status Hash Value -------------------- ------- ---------------- -------------------- --------------- -------------------- -------------------- --------- ---------- DEPT_TRG1 Local AFTER STATEMENT INSERT DEPT REFERENCING NEW AS N ENABLED 47788 1 row selected. +----------------------------------------------------------------------+ | VIEW DIFFERENCES | +----------------------------------------------------------------------+ ======================================================== View discrepencies for views that exist in both schemas ======================================================== View Name Schema Hash Value -------------------- ------- ---------- EMP_V2 Local 28816 EMP_V2 Remote 9621 2 rows selected. +----------------------------------------------------------------------+ | JOB QUEUE DIFFERENCES | +----------------------------------------------------------------------+ ======================================================== Job queue discrepencies ======================================================== no rows selected +----------------------------------------------------------------------+ | DATABASE LINK DIFFERENCES | +----------------------------------------------------------------------+ ======================================================== Database link discrepencies ======================================================== DB Link Name Schema User Name Host ------------------------------ ------- --------------- -------------------- TEMP_LINK.IDEVELOPMENT.INFO Local AP ORA920_ALEX 1 row selected.