3

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

Andrew
  • 3,632
  • 24
  • 64
  • 113
  • Is the result persistent? I mean are you running this query one after another and the result is same? Are you running this query under same user? – atokpas Apr 15 '16 at 09:16
  • Can you tell what gives this query: select * from all_synonyms where synonym_name = 'DBA_OBJECTS' – Mottor Apr 15 '16 at 09:34
  • @JSapkota the result is same thats why i dont understand the difference betweeen this queires. – Andrew Apr 15 '16 at 10:07
  • @Mottor the query returns with 5 rows. Its returning the result with owner DBADMIN PUBLIC READ_ONLY R2_READ_ONLY SYSTEM – Andrew Apr 15 '16 at 10:09
  • @Amit - are you sure the first query is what you are actually running then? Sounds like the first one has `dt.owner NOT in`. (Using `upper()` for the owner/name comparisons looks wrong incidentally, as it will potentially match too much if you have any quoted identifiers; not related to what you're seeing here though. Both queries are missing a closing parenthesis in what you've shown too.) – Alex Poole Apr 15 '16 at 10:12
  • @Amit what you should look where OWNER is PUBLIC. The difference between dba_objects and sys.dba_objects is that there is synonym dba_objects and behind this synonym can be a view with privileges cut. Tell me the row where owner is PUBLIC. – Mottor Apr 15 '16 at 10:19
  • @Mottor the row where owner is PUBLIC is: OWNER - PUBLIC, SYNONYM_NAME,- DBA_OBJECTS, TABLE_OWNER - SYS, TABLE_NAME - DBA_OBJECTS, DB_LINK - null – Andrew Apr 15 '16 at 10:37
  • @Alex yes i am sure and the parenthesisi was missing because i mention only small part of my query – Andrew Apr 15 '16 at 10:44
  • I see, your five rows were for Mottor's query, not your original query, I misunderstood. If you're running the query as DBADMIN, READ_ONLY or R2_READ_ONLY then the private synonym will take precedence over the public one, so (a) why do you have those private synonyms, and (b) what are they pointing at? I'd guess a snapshot of the system views from a point where everything was valid. It would help if you added the output from that query to the question, for all five rows. – Alex Poole Apr 15 '16 at 10:50
  • @Alex i really dont have that much information and i have less knowledge in oralce dba. Even theoritical if i get know the difference between sys.dba_objects and dba_objects then i will get more information.Normally using sys.dba_objects it should return all the objects but in this case its not happening – Andrew Apr 15 '16 at 11:03
  • You said it *is* returning data when you use `sys.dba_objects`, which avoids any of the synonyms. You've already run Mottor's query and said there were five rows; please add the output you got from that query to the question. – Alex Poole Apr 15 '16 at 11:07
  • i will add the output and no when i use sys.dba_objects then its not returning the result. I have added the result in my question – Andrew Apr 15 '16 at 11:10
  • I will explain it again. When you make SELECT * FROM SOME_NAME, oracle search for object with this name (table/view) in current schema, then looks for private synonym with this name, then looks for public synonym with this name. When such case like yours happens, you should check if you have table or view with this name in your (current user) schema, than check for private synonym (with owner current user in all_synonyms) and than for public synonym (owner PUBLIC). Is your current user in this 5 owner which you have shown us? – Mottor Apr 15 '16 at 11:15
  • yes its read_only user – Andrew Apr 15 '16 at 11:16
  • @Amit I see Alex has explained you, that actually when you makes select * from dba_objects, you selects from o2support.rm_dba_objects. When you use sys.dba_objects oracle looks for this object direct in SYS schema – Mottor Apr 15 '16 at 11:25
  • ohh ok now i understood and thts why when i am querying using sys.dba_objects its not checking the objects from o2support.rm_dba_objects and not returning the results right ? – Andrew Apr 15 '16 at 11:27
  • If you want to see why: SELECT * FROM sys.dba_objects WHERE owner = 'O2SUPPORT' AND object_name = 'RM_DBA_OBJECTS' , the if the object_type is 'VIEW' do this: SELECT text FROM sys.dba_views WHERE owner = 'O2SUPPORT' AND view_name = 'RM_DBA_OBJECTS' . – Mottor Apr 15 '16 at 11:32
  • @Amit is not polite to invite me in chat and not to be there. About second query: May be you dont have invalid objects. Change UPPER(do.STATUS) = 'VALID' to see the valid objects. For the first query I asked you to get the text from sys.dba_views, to see what happens – Mottor Apr 15 '16 at 13:59

1 Answers1

4

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.)

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • ohh ok now i understood and thts why when i am querying using sys.dba_objects its not checking the objects from o2support.rm_dba_objects and not returning the results right ? – Andrew Apr 15 '16 at 11:30
  • @Amit - correct, it will only look at either `sys.dba_objects` or .`o2support.rm_dba_objects`, depending on whether it's qualified or using the synonym, but not both. In your question you have it the other way round - `sys` found data, unqualified did not; but it doesn't matter, the results are still different because you're accessing different tables. – Alex Poole Apr 15 '16 at 11:33
  • Because `o2support.rm_dba_objects` has an entry for the package and shows it as INVALID; but `sys.dba_objects` either doesn't have an entry at all, or shows it as VALID. Query both tables/views using the package name to see what each shows. I can't see your tables/views so I don't know what's in them, and I don't know why you have that separate table/synonym, or when/why it was populated. Presumably it was a copy of the real `sys.dba_objects` taken when the package had been invalidated, maybe by changing or removing a dependency, but there is no way for me to know what happened in your system. – Alex Poole Apr 15 '16 at 13:57
  • I don't understand. Look at my answer again. You have a private synonym for `dba_objects` pointing to `o2support.rm_dba_objects`. When you say "the entry does not exist in dba_objects" that means "the entry does not exist in o2support.rm_dba_objects". The explanation is that **you have a private synonym** which is taking precedence over the public one, and the two statements are **querying different tables**. – Alex Poole Apr 15 '16 at 14:10
  • I haven't said the synonym is invalid. You are using the synonym `dba_objects` in your query and that means you are really querying the table the synonym points to. [Read more about synonyms](http://docs.oracle.com/cd/E11882_01/server.112/e40540/schemaob.htm#CNCPT711). – Alex Poole Apr 15 '16 at 14:24
  • Run these queries and compare the output: `select owner, object_type, object_name, status from o2support.rm_dba_objects where object_name = 'WORKFLOW_CLEAN';` and then `select owner, object_type, object_name, status from sys.dba_objects where object_name = 'WORKFLOW_CLEAN';` and compare the results. – Alex Poole Apr 15 '16 at 14:26