生活随笔
收集整理的這篇文章主要介紹了
【试验】三个用于日常监控开发库与对应测试库的存储过程
小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.
?
-檢查表,列是否一致??create?or?replace?procedure?check_tab_col(v_schema?varchar2)?as??????isfindtab?number;??????isfindcol?number;?begin?????dbms_output.put_line('Sechema?'||v_schema||'?begin?searching...');?????dbms_output.put_line(chr(10));?????for?i?in?(select?table_name?from?dba_tables?where?owner=v_schema)?loop?????????????select?count(*)?into?isfindtab?from?dba_tables@dblink_testdbc?where?owner=v_schema???????????????????????????????????????????????????????????????????????and?table_name=i.table_name;?????????????if?isfindtab=0?then????????????????????????????????dbms_output.put_line(rpad(i.table_name,30,'?')||'?is?lost?in?testdbc');?????????????else????????????????????????????????for?j?in?(select?column_name?from?dba_tab_columns?where?owner=v_schema?and?table_name=i.table_name)?loop???????????????????????select?count(*)?into?isfindcol?from?dba_tab_columns@dblink_testdbc?where?owner=v_schema??????????????????????????????????????????????????????????????????????and?table_name=i.table_name??????????????????????????????????????????????????????????????????????and?column_name=j.column_name;???????????????????????if?isfindcol=0?then???????????????????????????????????????????????????????dbms_output.put_line('alter?table?'||i.table_name||'?add('||j.column_name);???????????????????????end?if;????????????????end?loop;?????????????end?if;?????end?loop;?????dbms_output.put_line(chr(10));?????dbms_output.put_line('Sechema?'||v_schema||'?end?searching...');?????exception?when?others?then?????????null;?end?check_tab_col;????create?or?replace?procedure?check_indexes(v_schema?varchar2)?as?????n_isfind?number;?begin????dbms_output.put_line(v_schema||'??Index?Searching');????for?c?in(select?index_name,table_name?from?dba_indexes?where?table_owner=v_schema)?loop???????????select?count(*)?into?n_isfind?from?dba_indexes@dblink_testdbc?????????????????????????????????????????????????where?index_name=c.index_name?????????????????????????????????????????????????and?table_name=c.table_name?????????????????????????????????????????????????and?table_owner=v_schema;???????????if?n_isfind=0?then????????????????dbms_output.put_line(rpad(c.table_name,30,'?')||'??'||c.index_name);???????????end?if;????end?loop;?end?check_indexes;????create?or?replace?procedure?check_seq(v_schema?varchar2)?as?????n_isfind?number;?begin????dbms_output.put_line(v_schema||'?sequence?');????for?c?in(select?SEQUENCE_NAME?from?dba_sequences?where?SEQUENCE_OWNER=v_schema)?loop???????????select?count(*)?into?n_isfind?from?dba_sequences@dblink_testdbc?????????????????????????????????????????????????where?SEQUENCE_NAME=c.SEQUENCE_NAME?????????????????????????????????????????????????and?SEQUENCE_OWNER=v_schema;???????????if?n_isfind=0?then????????????????dbms_output.put_line('sequence?'||rpad(c.SEQUENCE_NAME,30,'?')||'?is?lost?in?testdbc');???????????end?if;????end?loop;?end;? ?
轉(zhuǎn)載于:https://blog.51cto.com/lya041/672827
總結(jié)
以上是生活随笔為你收集整理的【试验】三个用于日常监控开发库与对应测试库的存储过程的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。
