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

Oracle数据库开发应用中经常对数据库治理员有这样的需求，对比两个不同实例间某模式下对象的差异或者对比两个不同实例某模式下表定义的差异性，这在涉及到数据库软件的开发应用中是经常碰到的。一般数据库软件的开发都是首先在开发数据库上进行，开发到一定程度后，系统投入运行，此时软件处于维护阶段。针对在系统运行中碰到的错误、bug等，还有应用系统的升级，经常需要调整后台程序，数据库开发人员经常碰到这样一种尴尬的事情，维护到一定时期，开发库和运行库之间出现了一些差异，而这些差异又是什么。还有一种情况就是假如应用在很多地方同时使用，每次的后台升级，那些数据库作了升级，那些还没有作升级，假如没有具体记录的话，也会造成我们的尴尬事情。

　　另外，假如要搭建Oracle的高级表复制环境，在规划好了复制的架构以及复制的表后，在正式将需要复制的表加入到复制组之前，一个重要工作就是检查这些需要复制的表在主节点和主定义节点间的差异性。假如这些表定义间存在任何细微的差别，如某个字段在一个节点是答应Null，而在另一个节点是Not Null，在将表加入到复制组时都会出现这样的错误，“ORA-23309 object string.string of type string exists”，其实造成这个错误的原因就是复制主定义节点和主节点之间表的定义存在某些差异，系统认为是两个不同的表，但是在两个数据库中具有同样的名称。假如需要复制的表很多，手工对比两个节点复制表之间的差异是需要很大工作量，而且不一定准确。

　　业界有名的数据库软件辅助产品提供商美国Embarcadero公司（www.embarcadero.com）在其全套数据库解决方案中就相应提供了一个叫Change Manager的产品，该产品一个很大的用途就是对比分析不同实例间的差异。Oracle公司的OEM（oracle enterprise manager）包中也相应提供了一个工具叫做DB-Diff，也用来比较两个数据库间的差异。

　　其实对于普通的数据库开发在某种程度上说根本不需要上面提到的这些工具软件，况且这些产品不是随便可以得到并且使用的。针对上面提到的这些问题，提供了两个SQL脚本，可以轻松实现对比分析两个不同数据库实例间对象的差异以及表定义的差异。

　　一、对比两个不同实例下某模式中对象差异

　　在运行下面的脚本之前，必须首先创建当然数据库用户和需要对比的两个实例间的数据库联接。下面是运行该脚本的一个例子。在SQL/PLUS下运行该脚本，系统出现如下提示：

　　对象属主(模式): repadmin

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

　　第一个实例的数据库联接名称 (包括 @):@ora_sjjk 其中程序要求输入对象属主，也就是对比那个用户，这里是repadmin用户。然后是需要对比的实例联接名称，这里是ora_zs和ora_sjjk，最后给出对比报告。

　　附：PL/SQL程序脚本清单： set linesize 80
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
set newpage 0
ttitle col 25 '对象比较结果报告单' -
col 53 '日期: ' datetime -
skip 1 col 60 '页: ' sql.pno -
skip 1 col 10 '属主: ' obj_owner -
skip 1 center '对象在 &inst_code1_name 但不在 &inst_code2_name ' -
skip 2
column object_type format a15 heading '对象类型';
column object_name format a35 heading '对象名称';
column status format a10 heading '状态';
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS
FROM ALL_OBJECTS&inst_1_dblink
WHERE OWNER = UPPER('&OBJ_OWNER')
AND OBJECT_TYPE != 'SYNONYM'
MINUS
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS
FROM ALL_OBJECTS&inst_2_dblink
WHERE OWNER = UPPER('&OBJ_OWNER')
AND OBJECT_TYPE != 'SYNONYM'
ORDER BY 2,3
/
ttitle col 25 '对象比较结果报告单' -
col 53 '日期: ' datetime -
skip 1 col 60 '页: ' sql.pno -
skip 1 col 10 '属主: ' obj_owner -
skip 1 center '对象在 &inst_code2_name 但不在 &inst_code1_name ' -
skip 2
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS
FROM ALL_OBJECTS&inst_2_dblink
WHERE OWNER = UPPER('&OBJ_OWNER')
AND OBJECT_TYPE != 'SYNONYM'
MINUS
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS
FROM ALL_OBJECTS&inst_1_dblink
WHERE OWNER = UPPER('&OBJ_OWNER')
AND OBJECT_TYPE != 'SYNONYM'
ORDER BY 2,3
/
　　二、对比两个不同模式下表定义的差异

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

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