Toad for Oracle - "Used by Tab" is not pulling dependency from other schemas

Toad for Oracle & DevOps Toolkit
1

When I select a table and then head to Used By Tab, I do not see dependencies from other schemas.

I have noticed this behavior only on my machine, other developer machines show the entire dependency hierarchy, across different schemas.

I have

  1. Reset all the filters

  2. Cleared the Schema browser filters

  3. Cleared the history

  4. Cleared the Table/View/MView Data Filters

  5. Refreshed the Right side pane (which contains the Used By tab)

  6. Restored to the default options, while configuring the RHS

  7. Compiled the dependencies on that table

  8. Restarted toad / my machine several times hoping to fix the issue

But in vain. Has anyone every faced this issue and found a solution ?

I am using Toad for Oracle Base Version 11.5.1.2. I am hoping to find the text file on my machine (if any), where the Used By Tab gets its query from.

Is there a way to override the Query that this tab uses and use a more updated query ?

Thanks.

2

Sorry for not answering this one sooner. Would SYS happen to be the “other schemas”? If so, go to Options -> Schema Browser -> Right Hand Side -> “Omit SYS objects from procedure dependencies list”.

Looks like this option is a bit mis labeled, but it applies to all dependency listings in the Schema Browser.

FYI, there is no file on your machine where Toad gets its queries from. They are all compiled into Toad.exe. However, to see the query that Toad is running, go to the main menu -> Database -> Spool SQL -> Spool to Screen.

3

Thanks John for your response.

  • SYS is not the other schema. Its a custom schema.

(I tried going via Main menu to, View > Toad Options > Schema Browser > Right Hand Side; but it does not apply in this case)

The issue is that let’s say you have two schemas : ABC and DEF; such that in DEF schema, the DEF.Views are using dbObjects from ABC schema. When I head to ABC schema and for that dbObject, in the Used By tab, I do not see dependencies for DEF related views. However, we see the ‘DEF’-related dependencies on other developer’s machines.

4

Do a spool SQL on your machine when the Used by tab is loaded. Repeat on your co-workers machine. Post them both here. We’ll get it figured out.

5

Thanks for the suggestion.

We found out that on my machine, the queries are getting pulled from ALL_* objects

ex.

Select owner, object_type, object_name, object_id, status

from sys.ALL_OBJECTS where object_id in (

Select object_id

from public_dependency

connect by prior object_id = referenced_object_id

start with referenced_object_id = (

Select object_id from sys.ALL_OBJECTS

where owner = :Owner

and object_name = :name

and object_type = :type ))

:Owner(VARCHAR[9],IN/OUT)=‘ABC’

:name(VARCHAR[17],IN/OUT)=‘SampleTable’

:TYPE(VARCHAR[5],IN/OUT)=‘TABLE’

whereas, on other developer’s machines, the queries are getting pulled from 'DBA_* objects

ex.

Select owner, object_type, object_name, object_id, status

from sys.DBA_OBJECTS where object_id in (

Select object_id

from public_dependency

connect by prior object_id = referenced_object_id

start with referenced_object_id = (

Select object_id from sys.DBA_OBJECTS

where owner = :Owner

and object_name = :name

and object_type = :type ))

:Owner(VARCHAR[9],IN/OUT)=‘ABC’

:name(VARCHAR[17],IN/OUT)=‘SampleTable’

:TYPE(VARCHAR[5],IN/OUT)=‘TABLE’

6

how to fix this issue - should I be reinstalling oracle toad on my end ? or is this like a configuration issue that is easy to fix ?

7

Ah, yup, that could do it. Assuming that you are logging in as the same user, or that you at least both can select from the DBA_ views, go to Options -> Startup and check “Check for access to DBA views”

8

Don’t reinstall Toad. Just check the option.

9

wow … that fixed it …thanks for the help !

You rock !