主備資料庫狀態手工比對（二）

接上篇===========================================================================

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'

   /