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

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 ArrayArrayArray HEADING "长度";

COLUMN data_precision format ArrayArrayArray 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

/