oraclesys用户无效对象selectowner,object_name,replace(object_type,'','')object_type,to_char(created,'yyyy-mm-dd')ascreated,to_char(last_ddl_time,'yyyy-mm-dd')aslast_ddl_time,statusfromdba_objectswherestatus='INVALID'andowner='SYS';OWNEROBJECT_NAMEOBJECT_TYPECREATEDLAST_DDL_TIMESTATUS---------------------------------------------------------------------------SYSALL_TAB_STATISTICSVIEW2011-09-172012-05-16INVALIDSYSUSER_TAB_STATISTICSVIEW2011-09-172012-05-16INVALIDSYSALL_IND_STATISTICSVIEW2011-09-172012-05-16INVALIDSYSUSER_IND_STATISTICSVIEW2011-09-172012-05-16INVALIDSYSVALIDATE_ORDIMPROCEDURE2011-09-172012-05-16INVALIDSYSDBMS_CUBE_ADVISEPACKAGEBODY2011-09-172012-05-16INVALIDSYSDBMS_CUBEPACKAGEBODY2011-09-172012-05-16INVALID方法1：手动重新rebuiltSQL>alterviewsys.ALL_TAB_STATISTICScompile；SQL>alterviewsys.USER_TAB_STATISTICScompile；SQL>alterviewALL_IND_STATISTICScompile；SQL>alterviewsys.USER_IND_STATISTICScompile；SQL>alterproceduresys.VALIDATE_ORDIMcompile;SQL>alterpackageDBMS_CUBE_ADVISEcompilebody;SQL>alterpackageDBMS_CUBEcompilebody;方法2：oracle用户下执行$cd$ORACLE_HOME/rdbms/admin$sqlplus/assysdbaSQL>@utlprp.sql编译完成后，再次查看SQL>selectowner,object_name2,replace(object_type,'','')object_type3,to_char(created,'yyyy-mm-dd')ascreated4,to_char(last_ddl_time,'yyyy-mm-dd')aslast_ddl_time,5status6fromdba_objectswherestatus='INVALID'andowner='SYS';norowsselected方法3：以下是一个转帖的方法--创建自动编译失效过程事务记录表declaretabcntinteger:=0;beginselectcount(*)intotabcntfromdba_tableswheretable_name='RECOMPILE_LOG';iftabcnt=0thenexecuteimmediate'createtablerecompile_log(rdatedate,errmsgvarchar2(200))';endif;end;/--创建编译失效对象的存储过程createorreplaceprocedurerecompile_invalid_objectsasstr_sqlvarchar2(200);--中间用到的sql语句p_ownervarchar2(20);--所有者名称，即SCHEMAerrmvarchar2(200);--中间错误信息begin/*****************************************************/p_owner:='owner';/***用户名*************************//*****************************************************/insertintorecompile_log(rdate,errmsg)values(sysdate,'timetorecompileinvalidobjects');--编译失效存储过程forinvalid_proceduresin(selectobject_namefromall_objectswherestatus='INVALID'andobject_type='PROCEDURE'andowner=upper(p_owner))loopstr_sql:='alterprocedure'||invalid_procedures.object_name||'compile';beginexecuteimmediatestr_sql;exceptionWhenOthersThenbeginerrm:='errorbyobj:'||invalid_procedures.object_name||''||sqlerrm;insertintorecompile_log(rdate,errmsg)values(sysdate,errm);end;end;endloop;--编译失效函数forinvalid_functionsin(selectobject_namefromall_objectswherestatus='INVALID'andobject_type='FUNCTION'andowner=upper(p_owner))loopstr_sql:='alterfunction'||invalid_functions.object_name||'compile';beginexecuteimmediatestr_sql;exceptionWhenOthersThenbeginerrm:='errorbyobj:'||invalid_functions.object_name||''||sqlerrm;insertintorecompile_log(rdate,errmsg)values(sysdate,errm);end;end;endloop;--编译失效包forinvalid_packagesin(selectobject_namefromall_objectswherestatus='INVALID'andobject_type='PACKAGE'andowner=upper(p_owner))loopstr_sql:='alterpackage'||invalid_packages.object_name||'compile';beginexecuteimmediatestr_sql;exceptionWhenOthersThenbeginerrm:='errorbyobj:'||invalid_packages.object_name||''||sqlerrm;insertintorecompile_log(rdate,errmsg)values(sysdate,errm);end;end;endloop;--编译失效类型forinvalid_typesin(selectobject_namefromall_objectswherestatus='INVALID'andobject_type='TYPE'andowner=upper(p_owner))loopstr_sql:='altertype'||invalid_types.object_name||'compile';beginexecuteimmediatestr_sql;exceptionWhenOthersThenbeginerrm:='errorbyobj:'||invalid_types.object_name||''||sqlerrm;insertintorecompile_log(rdate,errmsg)values(sysdate,errm);end;end;endloop;--编译失效索引forinvalid_indexsin(selectobject_namefromall_objectswherestatus='INVALID'andobject_type='INDEX'andowner=upper(p_owner))loopstr_sql:='alterindex'||invalid_indexs.object_name||'rebuild';beginexecuteimmediatestr_sql;exceptionWhenOthersThenbeginerrm:='errorbyobj:'||invalid_indexs.object_name||''||sqlerrm;insertintorecompile_log(rdate,errmsg)values(sysdate,errm);end;end;endloop;--编译失效触发器forinvalid_triggersin(selectobject_namefromall_objectswherestatus='INVALID'andobject_type='TRIGGER'andowner=upper(p_owner))loopstr_sql:='altertrigger'||invalid_triggers.object_name||'compile';beginexecuteimmediatestr_sql;exceptionWhenOthersThenbeginerrm:='errorbyobj:'||invalid_triggers.object_name||''||sqlerrm;insertintorecompile_log(rdate,errmsg)values(sysdate,errm);end;end;endloop;end;/--创建任务计划，每天早上8点整执行该任务，且保证此任务有且只有一个declarejobcntinteger:=0;job_recompilenumber:=0;str_sqlvarchar2(200);beginselectcount(*)intojobcntfromall_jobswherewhat='recompile_invalid_objects;'andbroken='N';ifjobcnt>0thenforjobsin(selectjobfromall_jobswherewhat='recompile_invalid_objects;'andbroken='N')loopstr_sql:='begindbms_job.remove('||jobs.job||');end;';beginexecuteimmediatestr_sql;exceptionWhenOthersThennull;end;endloop;endif;--创建任务计划dbms_job.submit(job_recompile,'recompile_invalid_objects;',sysdate,'TRUNC(SYSDATE+1)+8/24');--启动任务计划dbms_job.run(job_recompile);end;/