Invalid Objects Issue
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Invalid Objects Issue

  1. #1
    Join Date
    Mar 2008
    Posts
    140

    Invalid Objects Issue

    Hi, Iam facing frequent problem of getting DB objects Invalid. The database is Oracle 10g RAC on Solaris OS. The application is under development stage and the schema is having all the necessary previleges. Executing utlrp.sql temporarily resolves this problem.


    The parameter, PLSQL_CODE_TYPE is set to INTERPRETED.

    My question is, Can I set this parameter to NATIVE in RAC environment. I came to know that by setting this parameter to NATIVE resolves the Invalid objects issue.

    If this is true, what are the other parameters to be set. Like

    PLSQL_NATIVE_LIBRARY_DIR, does the directory path should be on shared storage as this is RAC environment?


    thanks,

  2. #2
    Join Date
    Mar 2008
    Posts
    140
    kind help is appreciated.

  3. #3
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    The parameter, PLSQL_CODE_TYPE is set to INTERPRETED.

    My question is, Can I set this parameter to NATIVE in RAC environment. I came to know that by setting this parameter to NATIVE resolves the Invalid objects issue.
    The above parameter is to decide compilation mode. I am sure it is nothing to do with object status.

    Oracle changes the object status to invalid if the referenced object(s) are deleted, altered, moved, or any change in the privileges. However, if the conditions are met, Oracle automatically compile the invalid object(s) during next execution time.

    Please list out what objects are getting invalid frequently. As it is the development environment there is a good chance that developers are playing with objects which changes the object status to invalid.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  4. #4
    Join Date
    Mar 2008
    Posts
    140
    Hi, I agree that the parameter is for compilation mode. But the developers are complaining that the same application with same privileges on other clients does not create this problem.

    The objects getting invalid are: Procedures, Functions, Views and Packages.

    Moreover When i have given schema the DBA privilege this problem is resolved to maximum extent, I can say no invalid objects just 3 to 6 invalid objects. Previously the number was going to >600.


    thanks,
    Last edited by iris; 01-03-2011 at 09:04 AM.

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Who owns those 900 invalid objects?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Mar 2008
    Posts
    140
    Hi, the objects are owned by the same schema. I could not understand I have given the privileges as requested by the application developers, which produces frequent Invalid objects and same on giving DBA privileges then executing utlrp.sql the objects becomes valid.

    So my question is, Objects updates makes them invalid but given DBA privilege on same schema all works fine, Iam confused?


    Kind help is appreciated.

    thanks,

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by iris View Post
    Hi, the objects are owned by the same schema. I could not understand I have given the privileges as requested by the application developers, which produces frequent Invalid objects and same on giving DBA privileges then executing utlrp.sql the objects becomes valid.

    So my question is, Objects updates makes them invalid but given DBA privilege on same schema all works fine, Iam confused?
    1- Objects are getting invalidated 'cause of developers are working. Each time developer changes an object all dependant objects go invalid.
    2- Objects get valid after running utlrp.sql 'cause that's what utlrp.sql does - compile invalid objects.
    3- What's the problem about having invalid objects in a development schema? Oracle will compile them as needed.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Mar 2008
    Posts
    140
    I agree objects gets invalid upon update in test environment, but the concern is when given DBA privilege in same environment no invalid objects occurs.

    My question is: Does Objects don't get Invalid on giving DBA privilege?


    Kind help is appreciated.


    thanks,

  9. #9
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Quote Originally Posted by iris View Post
    I agree objects gets invalid upon update in test environment, but the concern is when given DBA privilege in same environment no invalid objects occurs.

    My question is: Does Objects don't get Invalid on giving DBA privilege?
    No, object state is not related with DBA privilege.

    Read this to know more about object dependencies.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  10. #10
    Join Date
    Mar 2008
    Posts
    140
    Hi, appreciate your help.

    Gone through the document, but did not find the solution.

    Can you please help me in finding what may be the root cause for this?


    thanks,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width