oracle 失效物件自動重新編譯
昨天看有個帖子說到的失效物件重新編譯的問題,然後發現自己公司裡也出現莫名其妙的失效物件。
SQL code
--建立自動編譯失效過程事務記錄表declare
tabcnt integer := 0;begin
select count(*) into tabcnt from dba_tables where table_name='RECOMPILE_LOG';
if tabcnt = 0 then
execute immediate 'create table recompile_log(rdate date,errmsg varchar2(200))';
end if;end;/--建立編譯失效物件的儲存過程create or replace procedure recompile_invalid_objects as
str_sql varchar2(200); --中間用到的sql語句
p_owner varchar2(20); --所有者名稱,即SCHEMA
errm varchar2(200); --中間錯誤資訊begin
/*****************************************************/
p_owner := 'owner';/***使用者名稱*************************/
/*****************************************************/
insert into recompile_log(rdate, errmsg) values(sysdate,'time to recompile invalid objects');
--編譯失效儲存過程
for invalid_procedures in (select object_name from all_objects
where status = 'INVALID' and object_type = 'PROCEDURE' and owner=upper(p_owner))
loop
str_sql := 'alter procedure ' ||invalid_procedures.object_name || ' compile';
begin
execute immediate str_sql;
exception
When Others Then
begin
errm := 'error by obj:'||invalid_procedures.object_name||' '||sqlerrm;
insert into recompile_log(rdate, errmsg) values(sysdate,errm);
end;
end;
end loop;
--編譯失效函式
for invalid_functions in (select object_name from all_objects
where status = 'INVALID' and object_type = 'FUNCTION' and owner=upper(p_owner))
loop
str_sql := 'alter function ' ||invalid_functions.object_name || ' compile';
begin
execute immediate str_sql;
exception
When Others Then
begin
errm := 'error by obj:'||invalid_functions.object_name||' '||sqlerrm;
insert into recompile_log(rdate, errmsg) values(sysdate,errm);
end;
end;
end loop;
--編譯失效包
for invalid_packages in (select object_name from all_objects
where status = 'INVALID' and object_type = 'PACKAGE' and owner=upper(p_owner))
loop
str_sql := 'alter package ' ||invalid_packages.object_name || ' compile';
begin
execute immediate str_sql;
exception
When Others Then
begin
errm := 'error by obj:'||invalid_packages.object_name||' '||sqlerrm;
insert into recompile_log(rdate, errmsg) values(sysdate,errm);
end;
end;
end loop;
--編譯失效型別
for invalid_types in (select object_name from all_objects
where status = 'INVALID' and object_type = 'TYPE' and owner=upper(p_owner))
loop
str_sql := 'alter type ' ||invalid_types.object_name || ' compile';
begin
execute immediate str_sql;
exception
When Others Then
begin
errm := 'error by obj:'||invalid_types.object_name||' '||sqlerrm;
insert into recompile_log(rdate, errmsg) values(sysdate,errm);
end;
end;
end loop;
--編譯失效索引
for invalid_indexs in (select object_name from all_objects
where status = 'INVALID' and object_type = 'INDEX' and owner=upper(p_owner))
loop
str_sql := 'alter index ' ||invalid_indexs.object_name || ' rebuild';
begin
execute immediate str_sql;
exception
When Others Then
begin
errm := 'error by obj:'||invalid_indexs.object_name||' '||sqlerrm;
insert into recompile_log(rdate, errmsg) values(sysdate,errm);
end;
end;
end loop;
--編譯失效觸發器
for invalid_triggers in (select object_name from all_objects
where status = 'INVALID' and object_type = 'TRIGGER' and owner=upper(p_owner))
loop
str_sql := 'alter trigger ' ||invalid_triggers.object_name || ' compile';
begin
execute immediate str_sql;
exception
When Others Then
begin
errm := 'error by obj:'||invalid_triggers.object_name||' '||sqlerrm;
insert into recompile_log(rdate, errmsg) values(sysdate,errm);
end;
end;
end loop;
end;/--建立任務計劃,每天早上8點整執行該任務,且保證此任務有且只有一個declare
jobcnt integer :=0;
job_recompile number := 0;
str_sql varchar2(200);begin
select count(*) into jobcnt from all_jobs where what = 'recompile_invalid_objects;' and broken = 'N';
if jobcnt > 0 then
for jobs in (select job from all_jobs where what = 'recompile_invalid_objects;' and broken = 'N')
loop
str_sql := 'begin dbms_job.remove('||jobs.job||'); end;';
begin
execute immediate str_sql;
exception
When Others Then null;
end;
end loop;
end if;
--建立任務計劃
dbms_job.submit(job_recompile,'recompile_invalid_objects;',sysdate,'TRUNC(SYSDATE + 1) + 8/24');
--啟動任務計劃 dbms_job.run(job_recompile);end;/
轉載:http://www.cnblogs.com/quanweiru/archive/2013/03/14/2958738.html
SQL code
--建立自動編譯失效過程事務記錄表declare
tabcnt integer := 0;begin
select count(*) into tabcnt from dba_tables where table_name='RECOMPILE_LOG';
if tabcnt = 0 then
execute immediate 'create table recompile_log(rdate date,errmsg varchar2(200))';
end if;end;/--建立編譯失效物件的儲存過程create or replace procedure recompile_invalid_objects as
str_sql varchar2(200); --中間用到的sql語句
p_owner varchar2(20); --所有者名稱,即SCHEMA
errm varchar2(200); --中間錯誤資訊begin
/*****************************************************/
p_owner := 'owner';/***使用者名稱*************************/
/*****************************************************/
insert into recompile_log(rdate, errmsg) values(sysdate,'time to recompile invalid objects');
--編譯失效儲存過程
for invalid_procedures in (select object_name from all_objects
where status = 'INVALID' and object_type = 'PROCEDURE' and owner=upper(p_owner))
loop
str_sql := 'alter procedure ' ||invalid_procedures.object_name || ' compile';
begin
execute immediate str_sql;
exception
When Others Then
begin
errm := 'error by obj:'||invalid_procedures.object_name||' '||sqlerrm;
insert into recompile_log(rdate, errmsg) values(sysdate,errm);
end;
end;
end loop;
--編譯失效函式
for invalid_functions in (select object_name from all_objects
where status = 'INVALID' and object_type = 'FUNCTION' and owner=upper(p_owner))
loop
str_sql := 'alter function ' ||invalid_functions.object_name || ' compile';
begin
execute immediate str_sql;
exception
When Others Then
begin
errm := 'error by obj:'||invalid_functions.object_name||' '||sqlerrm;
insert into recompile_log(rdate, errmsg) values(sysdate,errm);
end;
end;
end loop;
--編譯失效包
for invalid_packages in (select object_name from all_objects
where status = 'INVALID' and object_type = 'PACKAGE' and owner=upper(p_owner))
loop
str_sql := 'alter package ' ||invalid_packages.object_name || ' compile';
begin
execute immediate str_sql;
exception
When Others Then
begin
errm := 'error by obj:'||invalid_packages.object_name||' '||sqlerrm;
insert into recompile_log(rdate, errmsg) values(sysdate,errm);
end;
end;
end loop;
--編譯失效型別
for invalid_types in (select object_name from all_objects
where status = 'INVALID' and object_type = 'TYPE' and owner=upper(p_owner))
loop
str_sql := 'alter type ' ||invalid_types.object_name || ' compile';
begin
execute immediate str_sql;
exception
When Others Then
begin
errm := 'error by obj:'||invalid_types.object_name||' '||sqlerrm;
insert into recompile_log(rdate, errmsg) values(sysdate,errm);
end;
end;
end loop;
--編譯失效索引
for invalid_indexs in (select object_name from all_objects
where status = 'INVALID' and object_type = 'INDEX' and owner=upper(p_owner))
loop
str_sql := 'alter index ' ||invalid_indexs.object_name || ' rebuild';
begin
execute immediate str_sql;
exception
When Others Then
begin
errm := 'error by obj:'||invalid_indexs.object_name||' '||sqlerrm;
insert into recompile_log(rdate, errmsg) values(sysdate,errm);
end;
end;
end loop;
--編譯失效觸發器
for invalid_triggers in (select object_name from all_objects
where status = 'INVALID' and object_type = 'TRIGGER' and owner=upper(p_owner))
loop
str_sql := 'alter trigger ' ||invalid_triggers.object_name || ' compile';
begin
execute immediate str_sql;
exception
When Others Then
begin
errm := 'error by obj:'||invalid_triggers.object_name||' '||sqlerrm;
insert into recompile_log(rdate, errmsg) values(sysdate,errm);
end;
end;
end loop;
end;/--建立任務計劃,每天早上8點整執行該任務,且保證此任務有且只有一個declare
jobcnt integer :=0;
job_recompile number := 0;
str_sql varchar2(200);begin
select count(*) into jobcnt from all_jobs where what = 'recompile_invalid_objects;' and broken = 'N';
if jobcnt > 0 then
for jobs in (select job from all_jobs where what = 'recompile_invalid_objects;' and broken = 'N')
loop
str_sql := 'begin dbms_job.remove('||jobs.job||'); end;';
begin
execute immediate str_sql;
exception
When Others Then null;
end;
end loop;
end if;
--建立任務計劃
dbms_job.submit(job_recompile,'recompile_invalid_objects;',sysdate,'TRUNC(SYSDATE + 1) + 8/24');
--啟動任務計劃 dbms_job.run(job_recompile);end;/
轉載:http://www.cnblogs.com/quanweiru/archive/2013/03/14/2958738.html
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8494287/viewspace-1444330/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORACLE編譯失效物件小結Oracle編譯物件
- iOS自動化編譯打包iOS編譯
- 重新編譯python(親證有效)編譯Python
- GraphJin:GraphQL自動編譯轉為SQL編譯SQL
- 重編譯 invalid 物件(轉)編譯物件
- 從零開始學typescript— 自動編譯TypeScript編譯
- C++物件模型:編譯分析C++物件模型編譯
- 編譯器的自展和自舉、交叉編譯編譯
- CentOS 下重新編譯 nginx 新增模組CentOS編譯Nginx
- Sublime Text3 自動編譯less 的配置編譯
- 從fdk_aac編碼器到自動靜態編譯FFmpeg編譯
- 在使用make編譯時,修改原始檔的註釋內容,是否會重新編譯?編譯
- 元件化下EventBus的訊息型別自動編譯元件化型別編譯
- vscode的gulp-less自動把less編譯成cssVSCode編譯CSS
- 用Gulp編譯Sass並實時重新整理編譯
- nginx 重新編譯 lua 模組不停服(記錄)Nginx編譯
- javascript 返回,自動重新整理JavaScript
- nacos 自動重新整理配置
- Oracle RAC自啟動Oracle
- Sublime Text3中 less 自動編譯成 css 的方法編譯CSS
- 高峰期謹慎編譯業務物件編譯物件
- 雲物件 - 重新定義前後端互動物件後端
- Java動態編譯優化——提升編譯速度(N倍)Java編譯優化
- 編譯lua動態庫編譯
- Qt編譯MySQL驅動QT編譯MySql
- 自己動手編譯OpenJDK編譯JDK
- Webpack 自動重新整理和HMRWeb
- 如何自動重新整理網頁?Auto Refresh Page網頁自動重新整理工具分享網頁
- spring boot開發熱載入問題 自動編譯不生效Spring Boot編譯
- Gitee Webhook 實現自動拉取程式碼並編譯程式碼GiteeWebHook編譯
- 自動編碼器
- 【譯】自動生成整型序列
- Oracle 編譯儲存過程卡死解決方法Oracle編譯儲存過程
- [Flutter翻譯]通過重新編譯Flutter引擎對Flutter應用進行逆向工程。Flutter編譯
- 全網最深分析SpringBoot MVC自動配置失效的原因Spring BootMVC
- 2、Ktor學習-自動重新載入;
- SpringBoot整合Nacos自動重新整理配置Spring Boot
- WIN32 手動編譯Win32編譯
- Oracle 自動化運維-Python連線OracleOracle運維Python