AutoCAD 3DMAX C语言 Pro/E UG JAVA编程 PHP编程 Maya动画 Matlab应用 Android
Photoshop Word Excel flash VB编程 VC编程 Coreldraw SolidWorks A Designer Unity3D
 首页 > Oracle

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

51自学网 http://www.wanshiok.com

  其中程序要求输入对象属主,也就是对比那个用户,这里是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
/

 
 

上一篇:Oracle 8x中监控sysdba角色用户登陆情况  下一篇:在Oracle 8x中实现自动断开后再连接