Oracle不同数据库间对比分析脚本

　　二、对比两个不同模式下表定义的差异

　　运行方法和前面第一个脚本一样，这里不再详述。下面是一个运行实例结果：

　　对象属主(模式): db_zgxt

　　第一个实例的数据库联接名称 (包括 @):@ora_cx

　　第一个实例的数据库联接名称 (包括 @):@ora_zs

　　附：PL/SQL程序脚本清单：

SET PAGESIZE 60 SET LINESIZE 110 SET VERIFY OFF SET FEEDBACK OFF SET PAUSE OFF ACCEPT obj_owner PROMPT '对象属主(模式): ' ACCEPT inst_1_dblink PROMPT '第一个实例的数据库联接名称 (包括 @):' ACCEPT inst_2_dblink PROMPT '第一个实例的数据库联接名称 (包括 @):' clear breaks TTITLE off SET HEADING off COLUMN datetime noprint new_value datetime COLUMN inst_code1_name noprint new_value inst_code1_name COLUMN inst_code2_name noprint new_value inst_code2_name SELECT TO_CHAR(SYSDATE,'MM/DD/YY') datetime FROM DUAL / SELECT global_name inst_code1_name FROM global_name&inst_1_dblink / SELECT global_name inst_code2_name FROM global_name&inst_2_dblink / SET feedback ON SET HEADING ON TTITLE COL 30 '对象比较结果报告单' - COL 63 '日期: ' datetime - SKIP 1 COL 68 '页: ' sql.pno - SKIP 1 COL 10 '属主: ' obj_owner - SKIP 1 CENTER '&inst_code1_name 和 &inst_code2_name 之间表定义的差别明细' - SKIP 2 COLUMN table_name format a25 HEADING '表名'; COLUMN column_name format a25 HEADING '列名'; COLUMN data_type format a8 HEADING '数据类型'; COLUMN data_length format 999 HEADING '长度'; COLUMN data_precision format 999 HEADING '精度'; COLUMN nullable format a5 HEADING '是否可空'; COLUMN inst_code format a15 HEADING '实例'; SELECT '&inst_code1_name' inst_code, table_name, column_name, data_type, data_length, data_precision, nullable FROM all_tab_COLUMNs&inst_1_dblink WHERE owner = UPPER('&obj_owner') AND table_name in (SELECT table_name FROM all_tables&inst_2_dblink WHERE owner = UPPER('&obj_owner')) MINUS SELECT '&inst_code1_name' inst_code, table_name, column_name, data_type, data_length, data_precision, nullable FROM all_tab_columns&inst_2_dblink WHERE owner = UPPER('&obj_owner') UNION SELECT '&inst_code2_name' inst_code, table_name, column_name, data_type, data_length, data_precision, nullable FROM all_tab_COLUMNs&inst_2_dblink WHERE owner = UPPER('&obj_owner') AND table_name in (SELECT table_name FROM all_tables&inst_1_dblink WHERE owner = UPPER('&obj_owner')) MINUS SELECT '&inst_code2_name' inst_code, table_name, column_name, data_type, data_length, data_precision, nullable FROM all_tab_columns&inst_1_dblink WHERE owner = UPPER('&obj_owner') ORDER BY 2, 3 /