接上篇===========================================================================
6. 比对源端和目标端包、函数、存储过程、 type
比对源端和目标端 package 、 function 、 procedure 、 type 定义的行数（粗略比对 DDL 是否相同。）
select a.owner,a.name,a.type,a.ds_count, b.dt_count, ds_count - dt_count minus_count
from (select owner,name,type,count(line) ds_count
from dba_source@gsht69
where owner in ('TRADE')
group by owner,name,type) a
left join
(select owner,name,type,count(line) dt_count
from dba_source
where owner in ('TRADE')
group by owner,name,type) b
on a.owner=b.owner and a.name=b.name and a.type = b.type
where ds_count - dt_count <> 0
order by abs(ds_count - dt_count) desc;
7. 比对视图
比对源端和目标端 view 是否相同。
select owner,view_name,read_only
from dba_views
where owner in ('TRADE')
minus
select owner,view_name,read_only
from dba_views@gsht69
where owner in ('TRADE');
select ds.owner,ds.view_name,ds.text_length,dt.text_length,ds.text_length-dt.text_length minus_text_length
from dba_views@gsht69 ds left outer join dba_views dt
on ds.owner=dt.owner and ds.view_name=dt.view_name
where ds.text_length<>dt.text_length
and ds.owner in (‘TRADE’)
order by (ds.text_length-dt.text_length) desc;
8. 比对同义词
查看源端和目标端 synonymn 的 DDL 是否相同。
select owner,synonym_name,table_owner,table_name,db_link from dba_synonyms@gsht69
where owner in ('TRADE')
minus
select owner,synonym_name,table_owner,table_name,db_link from dba_synonyms
where owner in ('TRADE');
9. 比对触发器
比对源端和目标端 Trigger 的属性（粗略比对 DDL 是否相同）。
select owner,trigger_name,trigger_type,triggering_event
,table_owner,table_name
--,status
from dba_triggers@gsht69
where owner in ('TRADE')
minus
select owner,trigger_name,trigger_type,triggering_event
,table_owner,table_name
--,status
from dba_triggers
where owner in ('TRADE');
重编译目标端失效多象
b. 重新编译目标端失效对象。
set linesize 200
set pagesize 200
select 'alter '||object_type||' "'||owner||'"."'||object_name||'" compile;' from dba_objects
where owner in
('TRADE')
and status = 'INVALID' and object_type in ('PROCEDURE','FUNCTION','VIEW','PACKAGE','TRIGGER');
select 'alter package "'||owner||'"."'||object_name||'" compile body;' from dba_objects
where owner in
('TRADE')
and status = 'INVALID' and object_type in ('PACKAGE BODY');
10.抽取约束信息
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT',constraint_name,owner) from dba_constraints
where constraint_type like 'P'
and constraint_name not like 'SYS%'
and constraint_name not like 'BIN%' ;
11.提取外键信息
select 'alter table '
||a.owner
||'.'
|| a.table_name
|| ' add constraint '
|| a.constraint_name
|| ' foreign key ('
|| c.column_name
|| ') references '
||b.owner
||'.'
|| b.table_name
|| ' ('
|| b.column_name
|| ');'
as "Foreing Key SQL"
from DBA_CONSTRAINTS A, DBA_CONS_COLUMNS B, DBA_CONS_COLUMNS C
where A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
and A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
and A.TABLE_NAME = C.TABLE_NAME
and B.POSITION = C.POSITION
and A.owner = '&table_owner'
--and A.STATUS='ENABLED'
/
