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編譯物件
- ORACLE編譯失效物件小結Oracle編譯物件
- Oracle EBS 重新編譯無效物件 invalid objectOracle編譯物件Object
- 編譯資料庫失效物件指令碼編譯資料庫物件指令碼
- oracle編譯無效物件Oracle編譯物件
- oracle 儲存過程重新編譯方法Oracle儲存過程編譯
- 自動重新啟動oracle例項 for windowsOracleWindows
- iOS自動化編譯打包iOS編譯
- ORACLE自動重編譯包導致查詢被卡Oracle編譯
- 自動重新啟動oracle監聽程式 for windowsOracleWindows
- Hadoop SRC重新編譯Hadoop編譯
- ORACLE 資料庫分析,重新編譯失敗過程Oracle資料庫編譯
- GraphJin:GraphQL自動編譯轉為SQL編譯SQL
- VB6 dll 自動編譯工具編譯
- 編譯無效物件編譯物件
- 從零開始學typescript— 自動編譯TypeScript編譯
- oracle 物化檢視的自動重新整理方法Oracle
- Oracle查詢並批量編譯無效物件指令碼Oracle編譯物件指令碼
- CentOS 下重新編譯 nginx 新增模組CentOS編譯Nginx
- 重新編譯python(親證有效)編譯Python
- 重編譯 invalid 物件編譯物件
- 哪些oracle物件可以重新命名Oracle物件
- Sublime Text3 自動編譯less 的配置編譯
- VS 按F5無法自動編譯編譯
- oracle 10g 10.2.0.5 資料庫重新啟動後,DIRECTORY 許可權失效Oracle 10g資料庫
- 編譯器的自展和自舉、交叉編譯編譯
- (轉)編譯Oracle中無效的物件的N中方法編譯Oracle物件
- 重編譯 invalid 物件(轉)編譯物件
- 從fdk_aac編碼器到自動靜態編譯FFmpeg編譯
- 自動重建失效index的shell指令碼Index指令碼
- ant指令碼實現的Android自動編譯指令碼Android編譯
- Eclipse自動編譯NDK/JNI的三種方法Eclipse編譯
- oracle自動生成編譯所有函式、儲存過程、觸發器的語句Oracle編譯函式儲存過程觸發器
- 前端開發模式:被動編譯和主動編譯前端模式編譯
- nginx 重新編譯 lua 模組不停服(記錄)Nginx編譯
- Unity3D-重新編譯Mono加密DLLUnity3D編譯Mono加密
- Oracle編譯器警告Oracle編譯
- javascript 返回,自動重新整理JavaScript