Search code examples
sqloracle-databaseplsqlpatchoracle-ebs

Invalid Objects and What issue they can cause in application?

I just wanted to know about some invalid objects which are from Oracle ebs 12.1.3. The list is

  1. CST_LAYER_ACTUAL_COST_DTLS_V
  2. IGW_BUDGET_CATEGORY_V
  3. IGW_REPORT_PROCESSING
  4. FV_FACTS_TBAL_TRX
  5. FV_FACTS_TRX_REGISTER
  6. FV_SF133_ONEYEAR
  7. FV_SF133_NOYEAR
  8. FV_FACTS_TRANSACTIONS
  9. FV_FACTS_TBAL_TRANSACTIONS
  10. ENI_DBI_CO_OBJIDS_MV
  11. PJI_TIME_PA_RPT_STR_MV
  12. POA_MID_BS_J_MV
  13. POA_IDL_BS_J_MV
  14. POA_ITEMS_MV
  15. GL_ACCESS_SET_LEDGERS
  16. LNS_LOAN_DTLS_ALL_MV
  17. OZF_CUST_FUND_SUMMARY_MV
  18. FV_SLA_FV_PROCESSING_PKG
  19. OE_ITEMS_MV
  20. PA_DEDUCTIONS_W
  21. PA_DEDUCTIONS_PUB
  22. PA_DEDUCTIONS_PUB
  23. PA_DEDUCTIONS_W
  24. PA_DCTN_APRV_NOTIFICATION

--object types--

CST_LAYER_ACTUAL_COST_DTLS_V    VIEW 
IGW_BUDGET_CATEGORY_V   VIEW 
IGW_REPORT_PROCESSING   
PACKAGE BODY FV_FACTS_TBAL_TRX  PACKAGE BODY 
FV_FACTS_TRX_REGISTER   PACKAGE BODY 
FV_SF133_ONEYEAR    PACKAGE BODY 
FV_SF133_NOYEAR PACKAGE BODY 
FV_FACTS_TRANSACTIONS   
PACKAGE BODY FV_FACTS_TBAL_TRANSACTIONS PACKAGE BODY 
ENI_DBI_CO_OBJIDS_MV    MATERIALIZED VIEW 
PJI_TIME_PA_RPT_STR_MV  MATERIALIZED VIEW 
POA_MID_BS_J_MV MATERIALIZED VIEW
POA_IDL_BS_J_MV MATERIALIZED VIEW 
POA_ITEMS_MV    MATERIALIZED VIEW 
GL_ACCESS_SET_LEDGERS   MATERIALIZED VIEW 
LNS_LOAN_DTLS_ALL_MV    MATERIALIZED VIEW 
OZF_CUST_FUND_SUMMARY_MV    MATERIALIZED VIEW 
FV_SLA_FV_PROCESSING_PKG    PACKAGE BODY 
NIB_MV_TB   MATERIALIZED VIEW 
OE_ITEMS_MV MATERIALIZED VIEW 
PA_DEDUCTIONS_W PACKAGE 
PA_DEDUCTIONS_PUB   PACKAGE 
PA_DEDUCTIONS_PUB   PACKAGE BODY 
PA_DEDUCTIONS_W PACKAGE BODY 
PA_DCTN_APRV_NOTIFICATION   PACKAGE BODY

So I wanted to know that If I keep them invalid what problem they can cause?

Steps I took to know myself:-

I have searched over Oracle support and google by object name but the only thing i get there is patch no to resolve the issue or in some case that ignore these objects they will do nothing.

If anyone have information about these object and what problem they can cause in application. Please do share.

Thanks in Advance!!!

Solution

  • Tom Kyte, a noted Oracle expert, says

    They will fix themselves as they are executed or accessed. I never worry about some invalid objects -- you'll almost always have some somewhere.

    This is usually true as long as:

    • the database was installed correctly
    • patches and upgrades have been installed correctly
    • you have not changed oracle front end code or oracle database code
    • data that is being entered remains within expected ranges

    Invalid materialized views are not usually significant. In 9, 10 and 11 they appear to become invalid as soon as new data is added to the underlying tables. They can still be used even if they are invalid.

    Invalid views are compiled on access and if there are no errors become usable.

    The same is true of packages and package bodies: if there are no errors they are compiled on access. How your front end handles the error message before the compilation is not the same for all applications. If you try and access a package that cannot compile due to a code error then you will not get any results and a PL/SQL error is returned.

    To see what the cause of your problem is:

    • compile all invalid objects manually or use dbms_utility.compile_schema( 'YourSchema' );

    • for anything that will not compile you can find out more with the query

      select * from all_errors:

    If you find errors in Oracle supplied objects you then have to determine the source:

    • incorrect install or patching
    • known Oracle bug that should be patched
    • known Oracle bug that can be ignored
    • other cause which is beyond the scope of the question