oracle 失效对象

SQL> select 'alter  '||a.object_type||' '||a.owner||'.'||a.object_name ||' compile;'  from dba_objects a 
           where a.object_type in('TRIGGER','PROCEDURE','FUNCTION','VIEW') and a.status='INVALID'
           and a.owner not in ('SYS' ,'SYSTEM','OUTLN','U_SYSTEM'); 


'ALTER'||A.OBJECT_TYPE||''||A.OWNER||'.'||A.OBJECT_NAME||'COMPILE;'
--------------------------------------------------------------------------------
alter  VIEW APPS.EAM_EID_WORK_ORDERS_V compile;
alter  VIEW APPS.EAM_EID_WORK_REQUESTS_V compile;


SQL> 
SQL> alter  VIEW APPS.EAM_EID_WORK_ORDERS_V compile;
Warning: View altered with compilation errors.
SQL>

SQL> @?/rdbms/admin/utlrp

TIMESTAMP-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------COMP_TIMESTAMP UTLRP_BGN 2015-12-14 14:35:51DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalidDOC> objects in the database. Recompilation time is proportional to theDOC> number of invalid objects in the database, so this command may takeDOC> a long time to execute on a database with a large number of invalidDOC> objects.DOC> Use the following queries to track recompilation progress:DOC>DOC> 1. Query returning the number of invalid objects remaining. ThisDOC> number should decrease with time.DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);DOC>DOC> 2. Query returning the number of objects compiled so far. This numberDOC> should increase with time.DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;DOC>DOC> This script automatically chooses serial or parallel recompilationDOC> based on the number of CPUs available (parameter cpu_count) multipliedDOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).DOC> On RAC, this number is added across all RAC nodes.DOC>DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallelDOC> recompilation. Jobs are created without instance affinity so that theyDOC> can migrate across RAC nodes. Use the following queries to verifyDOC> whether UTL_RECOMP jobs are being created and run correctly:DOC>DOC> 1. Query showing jobs created by UTL_RECOMPDOC> SELECT job_name FROM dba_scheduler_jobsDOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';DOC>DOC> 2. Query showing UTL_RECOMP jobs that are runningDOC> SELECT job_name FROM dba_scheduler_running_jobsDOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';DOC>#PL/SQL procedure successfully completed.TIMESTAMP-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------COMP_TIMESTAMP UTLRP_END 2015-12-14 14:35:52PL/SQL procedure successfully completed.DOC> The following query reports the number of objects that have compiledDOC> with errors (objects that compile with errors have status set to 3 inDOC> obj$). If the number is higher than expected, please examine the errorDOC> messages reported with each object (using SHOW ERRORS) to see if theyDOC> point to system misconfiguration or resource constraints that must beDOC> fixed before attempting to recompile these objects.DOC>#OBJECTS WITH ERRORS------------------- 0DOC> The following query reports the number of errors caught duringDOC> recompilation. If this number is non-zero, please query the errorDOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errorsDOC> are due to misconfiguration or resource constraints that must beDOC> fixed before objects can compile successfully.DOC>#ERRORS DURING RECOMPILATION--------------------------- 0PL/SQL procedure successfully completed.

SQL> select count(*) from dba_objects where status='INVALID'; COUNT(*)----------19SQL>

SQL> col owner for a10
SQL> select owner, object_name, object_type from dba_objects where status='INVALID';


OWNER   OBJECT_NAME OBJECT_TYPE
---------- ------------------------------ -------------------
PUBLIC   DBA_HIST_FILESTATXS SYNONYM
PUBLIC   DBA_HIST_SQLSTAT SYNONYM
PUBLIC   DBA_HIST_SQLBIND SYNONYM
PUBLIC   DBA_HIST_SYSTEM_EVENT SYNONYM
PUBLIC   DBA_HIST_WAITSTAT SYNONYM
PUBLIC   DBA_HIST_LATCH SYNONYM
PUBLIC   DBA_HIST_LATCH_MISSES_SUMMARY  SYNONYM
PUBLIC   DBA_HIST_DB_CACHE_ADVICE SYNONYM
PUBLIC   DBA_HIST_ROWCACHE_SUMMARY SYNONYM
PUBLIC   DBA_HIST_SGASTAT SYNONYM
PUBLIC   DBA_HIST_SYSSTAT SYNONYM
PUBLIC   DBA_HIST_SYS_TIME_MODEL SYNONYM
PUBLIC   DBA_HIST_OSSTAT SYNONYM
PUBLIC   DBA_HIST_PARAMETER SYNONYM
PUBLIC   DBA_HIST_SEG_STAT SYNONYM
PUBLIC   DBA_HIST_ACTIVE_SESS_HISTORY   SYNONYM
PUBLIC   DBA_HIST_TABLESPACE_STAT SYNONYM
PUBLIC   DBA_HIST_SERVICE_STAT SYNONYM
PUBLIC   DBA_HIST_SERVICE_WAIT_CLASS SYNONYM

SQL> select owner, object_name, object_type from dba_objects where status='INVALID';
no rows selected
SQL> 

有时候job会报错

重新编译下

再看status

失效视图：

SQL> /
OWNER   OBJECT_NAME   OBJECT_TYPE
---------- ---------------------------------------- -------------------
APPS   EAM_EID_WORK_ORDERS_V   VIEW
APPS   EAM_EID_WORK_REQUESTS_V   VIEW
SQL> l
  1* select owner, object_name, object_type from dba_objects where status='INVALID'
SQL> select count(*) from dba_objects where status='INVALID';
  COUNT(*)
----------
2
SQL> 

CREATE OR REPLACE VIEW APPS.EAM_EID_WORK_REQUESTS_V AS
SELECT wewr.WORK_REQUEST_NUMBER AS WR_REQUEST_NUMBER, ML_WR_STATUS.meaning AS WR_STATUS, wewr.work_request_status_id AS WR_STATUS_ID, ML_WR_TYPE.meaning AS WR_REQUEST_TYPE, bd.DEPARTMENT_CODE AS WR_DEPARTMENT, ML_WR_PRIORITY.meaning AS WR_PRIORITY, wewr.DESCRIPTION AS WR_DESCRIPTION, wewr.ORGANIZATION_ID as WR_ORGANIZATION_ID, mp.ORGANIZATION_CODE as WR_ORGANIZATION_CODE, wewr.maintenance_object_id AS MAINTENANCE_OBJECT_ID, cii.instance_number AS ASSET_NUMBER, cii.instance_description AS ASSET_DESCRIPTION, msi.concatenated_segments AS ASSET_GROUP , msitl.description AS ASSET_GROUP_DESCRIPTION , mc.concatenated_segments AS ASSET_CATEGORY, ML_ASSET_CRITICALITY.meaning AS ASSET_CRITICALITY, el.location_codes AS ASSET_AREA, ML_ITEM_TYPE.meaning as ASSET_TYPE, NULL AS wip_entity_id, NULL AS WIP_ENTITY_NAME, NULL AS WO_DESCRIPTION, NULL AS WO_STATUS, NULL AS WO_SCHEDULED_START_DATE, NULL AS WO_SCHEDULED_COMPLETION_DATE, NULL AS WO_ACTUAL_START_DATE, NULL AS WO_ACTUAL_COMPLETION_DATE, wewr.CREATED_FOR AS WR_REQUESTED_FOR, wewr.expected_resolution_date AS WR_REQUEST_BY_DATE, wewr.ATTRIBUTE_CATEGORY AS WR_ATTRIBUTE_CATEGORY, FND_EID_FLEX_PKG.get_dff_kvp('WIP_EAM_WORK_REQUESTS',wewr.rowid,msitl.language,'WIP_EAM_WORK_REQUESTS') AS WR_ATTRIBUTE_VALUE, users.USER_NAME AS WR_CREATED_BY, wewr.CREATION_DATE AS WR_CREATION_DATE, wewr.last_update_login AS last_update_login, wewr.WORK_REQUEST_NUMBER AS ENDECA_ID, greatest(wewr.last_update_date,cii.last_update_date,msi.last_update_date,msitl.last_update_date,mp.last_update_date,eomd.last_update_date) AS EID_LAST_UPDATE_DATE, EAM_COMMON_UTILITIES_PVT.get_asset_attribute (cii.instance_id,msitl.language) AS ASSET_ATTRIBUTES, 'WR' AS RECORD_TYPE, msitl.language AS LANGUAGE FROM wip_eam_work_requests wewr, bom_departments bd, csi_item_instances cii, mtl_system_items_kfv msi, mtl_system_items_tl msitl, mtl_categories_kfv mc, mtl_parameters mp, mtl_eam_locations el, eam_org_maint_defaults eomd, mfg_lookups ml_item_type, mfg_lookups ml_wr_status, mfg_lookups ml_wr_type, mfg_lookups ml_wr_priority, mfg_lookups ml_asset_criticality, fnd_user users WHERE wewr.organization_id = bd.ORGANIZATION_ID AND wewr.WORK_REQUEST_OWNING_DEPT = bd.department_id AND wewr.organization_id = mp.organization_id AND wewr.maintenance_object_id = cii.instance_id AND cii.inventory_item_id = msi.inventory_item_id AND msi.organization_id = wewr.organization_id AND msi.inventory_item_id = msitl.inventory_item_id(+) AND msi.organization_id = msitl.organization_id(+) AND cii.category_id = mc.category_id(+) AND cii.instance_id = eomd.object_id(+) AND eomd.area_id = el.location_id(+) AND wewr.wip_entity_id is NULL AND ML_ASSET_CRITICALITY.lookup_type(+) = 'MTL_EAM_ASSET_CRITICALITY' AND ML_ASSET_CRITICALITY.lookup_code(+) = cii.asset_criticality_code AND ML_ITEM_TYPE.lookup_type = 'EAM_ITEM_TYPE' AND ML_ITEM_TYPE.lookup_code = msi.EAM_ITEM_TYPE AND ML_WR_STATUS.lookup_type = 'WIP_EAM_WORK_REQ_STATUS' AND ML_WR_STATUS.lookup_code = wewr.WORK_REQUEST_STATUS_ID AND ML_WR_TYPE.lookup_type(+) = 'WIP_EAM_WORK_REQ_TYPE' AND ML_WR_TYPE.lookup_code(+) = wewr.WORK_REQUEST_TYPE_ID AND ML_WR_PRIORITY.lookup_type(+) = 'WIP_EAM_ACTIVITY_PRIORITY' AND ML_WR_PRIORITY.lookup_code(+) = wewr.WORK_REQUEST_PRIORITY_ID AND wewr.created_by = users.user_id(+) AND (fnd_profile.value('EAM_ENDECA_WR_DATA_LOAD_START_DATE') is null or wewr.creation_date > to_date(fnd_profile.value('EAM_ENDECA_WR_DATA_LOAD_START_DATE'),'DD/MM/YYYY')) UNION ALL SELECT wewr.WORK_REQUEST_NUMBER AS WR_REQUEST_NUMBER, ML_WR_STATUS.meaning AS WR_STATUS, wewr.work_request_status_id AS WR_STATUS_ID, ML_WR_TYPE.meaning AS WR_REQUEST_TYPE, bd.DEPARTMENT_CODE AS WR_DEPARTMENT, ML_WR_PRIORITY.meaning AS WR_PRIORITY, wewr.DESCRIPTION AS WR_DESCRIPTION, wewr.ORGANIZATION_ID AS WR_ORGANIZATION_ID, mp.ORGANIZATION_CODE AS WR_ORGANIZATION_CODE, wewr.maintenance_object_id AS MAINTENANCE_OBJECT_ID, cii.instance_number AS ASSET_NUMBER, cii.instance_description AS ASSET_DESCRIPTION, msi.concatenated_segments AS ASSET_GROUP , msitl.description AS ASSET_GROUP_DESCRIPTION , mc.concatenated_segments AS ASSET_CATEGORY, ML_ASSET_CRITICALITY.meaning AS ASSET_CRITICALITY, el.location_codes AS ASSET_AREA, ML_ITEM_TYPE.meaning as ASSET_TYPE, we.wip_entity_id, we.WIP_ENTITY_NAME, we.DESCRIPTION AS WO_DESCRIPTION, DECODE(ewod.pending_flag, 'Y', DECODE(ewsb.SEEDED_FLAG,'Y',NVL(ewstl.USER_DEFINED_STATUS,ML_STATUS.MEANING),ewstl.USER_DEFINED_STATUS) || ' - ' || fnd_message.get_string('EAM','EAM_PENDING_TEXT'), DECODE(ewsb.SEEDED_FLAG,'Y',NVL(ewstl.USER_DEFINED_STATUS,ML_STATUS.MEANING),ewstl.USER_DEFINED_STATUS)) wo_status, wdj.SCHEDULED_START_DATE AS WO_SCHEDULED_START_DATE, wdj.SCHEDULED_COMPLETION_DATE AS WO_SCHEDULED_COMPLETION_DATE, (SELECT ACTUAL_START_DATE FROM EAM_JOB_COMPLETION_TXNS WHERE TRANSACTION_TYPE=1 AND TRANSACTION_ID = (SELECT MAX(TRANSACTION_ID) FROM EAM_JOB_COMPLETION_TXNS EJT WHERE EJT.WIP_ENTITY_ID=we.WIP_ENTITY_ID AND EJT.ORGANIZATION_ID=we.ORGANIZATION_ID ) ) AS ACTUAL_START_DATE, wdj.DATE_COMPLETED AS WO_ACTUAL_COMPLETION_DATE, wewr.CREATED_FOR AS WR_REQUESTED_FOR, wewr.expected_resolution_date AS WR_REQUEST_BY_DATE, wewr.ATTRIBUTE_CATEGORY, FND_EID_FLEX_PKG.get_dff_kvp('WIP_EAM_WORK_REQUESTS',wewr.rowid,msitl.language,'WIP_EAM_WORK_REQUESTS') AS WR_ATTRIBUTE_VALUE, users.USER_NAME AS WR_CREATED_BY, wewr.CREATION_DATE AS WR_CREATION_DATE, wewr.last_update_login, wewr.WORK_REQUEST_NUMBER AS ENDECA_ID, greatest(wewr.last_update_date,cii.last_update_date,msi.last_update_date,msitl.last_update_date,wdj.last_update_date,ewod.last_update_date,ewsb.last_update_date,mp.last_update_date,eomd.last_update_date) AS EID_LAST_UPDATE_DATE, EAM_COMMON_UTILITIES_PVT.get_asset_attribute (cii.instance_id,msitl.language) AS ASSET_ATTRIBUTES, 'WR' AS RECORD_TYPE, msitl.language AS LANGUAGE FROM wip_eam_work_requests wewr, BOM_DEPARTMENTS bd, wip_discrete_jobs wdj, wip_entities we, eam_work_order_details ewod, eam_wo_statuses_b ewsb, eam_wo_statuses_tl ewstl, csi_item_instances cii, mtl_system_items_vl msi, mtl_system_items_tl msitl, mtl_categories_kfv mc, mtl_parameters mp, mtl_eam_locations el, eam_org_maint_defaults eomd, MFG_LOOKUPS ML_ITEM_TYPE, MFG_LOOKUPS ML_WR_STATUS, MFG_LOOKUPS ML_WR_TYPE, MFG_LOOKUPS ML_WR_PRIORITY, MFG_LOOKUPS ML_ASSET_CRITICALITY, MFG_LOOKUPS ML_STATUS, fnd_user users WHERE wewr.organization_id = bd.ORGANIZATION_ID AND wewr.WORK_REQUEST_OWNING_DEPT = bd.department_id AND wewr.wip_entity_id = wdj.wip_entity_id AND wewr.ORGANIZATION_ID = wdj.ORGANIZATION_ID AND we.wip_entity_id = wdj.wip_entity_id AND we.organization_id = wdj.organization_id AND ewsb.status_id = ewod.user_defined_status_id AND ML_STATUS.ENABLED_FLAG = 'Y' AND ML_STATUS.LOOKUP_TYPE ='WIP_JOB_STATUS' AND ewsb.SYSTEM_STATUS = ML_STATUS.LOOKUP_CODE AND ewsb.status_id = ewstl.status_id (+) AND ewod.wip_entity_id =wewr.wip_entity_id AND wewr.organization_id = mp.organization_id AND wewr.maintenance_object_id = cii.instance_id AND cii.inventory_item_id = msi.inventory_item_id AND msi.organization_id = wewr.organization_id AND msi.inventory_item_id = msitl.inventory_item_id(+) AND msi.organization_id = msitl.organization_id(+) AND (msitl.language = ewstl.language or ewstl.language is null) AND cii.category_id = mc.category_id(+) AND cii.instance_id = eomd.object_id(+) AND eomd.area_id = el.location_id(+) AND ML_ASSET_CRITICALITY.lookup_type(+) = 'MTL_EAM_ASSET_CRITICALITY' AND ML_ASSET_CRITICALITY.lookup_code(+) = cii.asset_criticality_code AND ML_ITEM_TYPE.lookup_type = 'EAM_ITEM_TYPE' AND ML_ITEM_TYPE.lookup_code = msi.EAM_ITEM_TYPE AND ML_WR_STATUS.lookup_type = 'WIP_EAM_WORK_REQ_STATUS' AND ML_WR_STATUS.lookup_code = wewr.WORK_REQUEST_STATUS_ID AND ML_WR_TYPE.lookup_type(+) = 'WIP_EAM_WORK_REQ_TYPE' AND ML_WR_TYPE.lookup_code(+) = wewr.WORK_REQUEST_TYPE_ID AND ML_WR_PRIORITY.lookup_type(+) = 'WIP_EAM_ACTIVITY_PRIORITY' AND ML_WR_PRIORITY.lookup_code(+) = wewr.WORK_REQUEST_PRIORITY_ID AND wewr.created_by = users.user_id(+) AND (fnd_profile.value('EAM_ENDECA_WR_DATA_LOAD_START_DATE') is null or wewr.creation_date > to_date(fnd_profile.value('EAM_ENDECA_WR_DATA_LOAD_START_DATE'),'DD/MM/YYYY'));

用命令重新编译失效对象：

SQL> select 'alter  '||a.object_type||' '||a.owner||'.'||a.object_name ||' compile;'  from dba_objects a 
           where a.object_type in('TRIGGER','PROCEDURE','FUNCTION','VIEW') and a.status='INVALID'
           and a.owner not in ('SYS' ,'SYSTEM','OUTLN','U_SYSTEM'); 


'ALTER'||A.OBJECT_TYPE||''||A.OWNER||'.'||A.OBJECT_NAME||'COMPILE;'
--------------------------------------------------------------------------------
alter  VIEW APPS.EAM_EID_WORK_ORDERS_V compile;
alter  VIEW APPS.EAM_EID_WORK_REQUESTS_V compile;


SQL> 
SQL> alter  VIEW APPS.EAM_EID_WORK_ORDERS_V compile;
Warning: View altered with compilation errors.
SQL>

同义词失效：

SQL> select * from CISUSER.SYS_EXPORT_SCHEMA_06  where rownum<10;
select * from CISUSER.SYS_EXPORT_SCHEMA_06  where rownum<10
                      *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid




SQL>  select owner, object_name, object_type from dba_objects where status='INVALID';


OWNER   OBJECT_NAMEOBJECT_TYPE
---------- -------------------- -------------------
CISREAD    SYS_EXPORT_SCHEMA_06 SYNONYM
CISUSER    CM_AJJH_PR SYNONYM
CISREAD    CM_AJJH_PR SYNONYM
CISREAD    CMUPBXFLGPROC SYNONYM
CISREAD    QSXJ SYNONYM
CISUSER    QSXJ SYNONYM


6 rows selected.


SQL>
SQL> @?/rdbms/admin/utlrp
SQL> select count(*) from dba_objects where status='INVALID';


  COUNT(*)
----------
0


SQL> select * from CISUSER.SYS_EXPORT_SCHEMA_06  where rownum<10;
select * from CISUSER.SYS_EXPORT_SCHEMA_06  where rownum<10
                      *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid




SQL>  select owner, object_name, object_type from dba_objects where  object_name='SYS_EXPORT_SCHEMA_06';


OWNER   OBJECT_NAMEOBJECT_TYPE
---------- -------------------- -------------------
CISUSER    SYS_EXPORT_SCHEMA_06 SYNONYM
CISREAD    SYS_EXPORT_SCHEMA_06 SYNONYM


SQL> select count(*) from CISUSER.SYS_EXPORT_SCHEMA_06;
select count(*) from CISUSER.SYS_EXPORT_SCHEMA_06
                             *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid




SQL>