Below is the query i have used to analyse invalid dba objects from dba_objects in database and its returning with invalid objects:
select do.STATUS as CODE_STATUS, do.OBJECT_TYPE, do.OWNER, do.OBJECT_NAME from dba_objects do
WHERE UPPER(do.OBJECT_TYPE) IN ('TABLE', 'VIEW', 'FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY') AND UPPER(do.STATUS) <> 'VALID'
AND do.owner in ('AD','BD','DR','CD')
Below is the query the i have used to analyse invalid dba objects from sys.dba_objects and its returning null:
select do.STATUS as CODE_STATUS, do.OBJECT_TYPE, do.OWNER, do.OBJECT_NAME from sys.dba_objects do
WHERE UPPER(do.OBJECT_TYPE) IN ('TABLE', 'VIEW', 'FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY') AND UPPER(do.STATUS) <> 'VALID'
AND do.owner in ('AD','BD','DR','CD')
Why the first query returns results with invalid package body and why the second query does not return any result
The rules for name resolution are described in the documentation.
When you run your query against sys.dba_objects
you are directly accessing the SYS-owned view called dba_objects
. When you run your query against the unqualified dba_objects
then you may access a table or view that you own, or an object you or someone else owns, through a private synonym (that you own) or a public synonym.
Usually there is just a public synonym for the dba_*
views, which means that if you refer to dba_objects
then you are still actually looking at sys.dba_objects
, via that default public synonym.
In your case two users have private synonyms with the same name. If you are connected as either READ_ONLY
or RM2_READ_ONLY
then those users' private synonyms will be used; so when you reference dba_objects
you will actually be looking at o2support.rm_dba_objects
, which - based on the results you're getting - is completely unrelated to the current contents of sys.dba_objects
.
To summarise: you have a private synonym which is taking precedence over the public one, and the two statements are querying different tables.
I'd guess it's an earlier snapshot of objects in the system, possibly - from the name - of objects that were going to be removed, perhaps as a reference for reinstating them if needed. Whatever it is, it's stale and you don't seem to want to be seeing its contents.
If you want to see the current data dictionary then you will have to continue to refer explicitly to sys.dba_objects
, or see if the private synonyms can be safely removed.
(It's not very useful, but you can also explicitly refer to the public synonym; but the owner has to be supplied as a quoted identifier, i.e. "PUBLIC".dba_objects
. There's no benefit in doing that over referring directly to sys.dba_objects
though.)