全文

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

2001-12-06 11:19作者：石骁騑出处：Yesky责任编辑：

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

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

　　对象属主(模式): 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
/

共3页。 9 1 2 3

共3页。首页上一页 1 2 3