1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Vaild objects showing status as 'invalid' in user_objects.,

Discussion in 'SQL PL/SQL' started by Vicky, Jan 29, 2016.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi all,

    While executing the query below, I'm getting the list of object with status as 'INVALID'.,

    Code (SQL):

    SELECT * FROM USER_OBJECTS WHERE STATUS='INVALID';
     
    When I checked the status of the objects individually, they are in compiled state.,

    Even after dropping those objects, they're still showing up when I execute the same query.,

    Any clue, why it is behaving soo?
     
  2. Naveen Reddy

    Naveen Reddy Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Italy
    They are might be reasons like

    ---->IF compiled , it should be valid
    --->Example there is PKG A & PKG B

    IF B is dependent on A

    And something errored out in A you modified the error & runned it ,
    then it will compile but B will show still in In valid
    because unless some one go & modify it.

    -->Or you said after dropping those objects, they're still showing up when I execute the same query.,
    See whether the dropping objects environment & query environment same i mean may be you are dropping in Prod & checking query in Test
     
  3. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Predefined view that's out of date? Bad private synonym? Are you absotively and posalutely sure you're looking at the correct USER_OBJECTS?

    CJ
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Execute the following and post the results here (if you're still having a problem after doing it):

    Code (Text):

    SELECT COUNT(*) FROM all_objects WHERE OWNER='[schema]' AND STATUS='INVALID';
    DBMS_UTILITY.COMPILE_SCHEMA ('[schema]');
    SELECT COUNT(*) FROM all_objects WHERE OWNER='[schema]' AND STATUS='INVALID';