CODEWITHSUNDEEP
sqloracle-sqldeveloper
Catalin
Catalin

Reputation: 263

Oracle SQL Developer window view with table dependencies

In Oracle SQL developer GUI, I opened a table and a window with attributes appear. Here is a window tab named Dependencies.

I found this query:

select    OWNER
        , name
        , type
        , referenced_name
        , referenced_type
  from all_dependencies;

But didn't show output for all owners like as Oracle SQL Developer.

How can I obtain these results via a query SQL with all owners?

Thanks!

Upvotes: 0

Views: 10795

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

With a query like:

select owner, name, type, referenced_owner, referenced_name, referenced_type
from dba_dependencies
where referenced_owner = user and referenced_name = 'YOUR_TABLE_NAME';

or using bind variables:

var object_owner varchar2(30);
var object_name varchar2(30);
exec :object_owner := user;
exec :object_name := 'YOUR_TABLE_NAME';

select owner, name, type, referenced_owner, referenced_name, referenced_type
from dba_dependencies
where referenced_owner = :object_owner and referenced_name = :object_name ;

You can actually see the queries that SQL Developer runs under the hood. If you go to to the View menu and choose Log, or hit CtrlShiftL (assuming you're using Windows) you'll get a docked window which by default is titled "Messages - Log". At the bottom of that are two tabs, with "Messages" selected. If you click "Statements" instead you can see the statement and bind variables used.

In this case there are three statements issued when you refresh the dependencies tab, two of which are variations on what I've shown above - they get a few more columns, and check dependencies going both ways:

select owner, name, type, referenced_owner, referenced_name, referenced_type ,
  owner sdev_link_owner, name sdev_link_name, type sdev_link_type
from Dba_DEPENDENCIES
where referenced_owner = :OBJECT_OWNER and referenced_name = :OBJECT_NAME

select owner, name, type, referenced_owner, referenced_name, referenced_type ,
  referenced_owner sdev_link_owner, referenced_name sdev_link_name, referenced_type sdev_link_type
from Dba_DEPENDENCIES
where owner = :OBJECT_OWNER and name = :OBJECT_NAME

They are a good place to start if you want to figure out how replicate what you can see.

If you are connected as a user that doesn't have the privileges necessary to see the dba_dependencies table, SQL Developer instead falls back to all_dependencies:

select owner, name, type, referenced_owner, referenced_name, referenced_type ,
  owner sdev_link_owner, name sdev_link_name, type sdev_link_type
from ALL_DEPENDENCIES
where referenced_owner = :OBJECT_OWNER and referenced_name = :OBJECT_NAME

select owner, name, type, referenced_owner, referenced_name, referenced_type ,
  referenced_owner sdev_link_owner, referenced_name sdev_link_name, referenced_type sdev_link_type
from Dba_DEPENDENCIES
where owner = :OBJECT_OWNER and name = :OBJECT_NAME

which will only show information about objects you have select/execute privileges against. In the first query I showed above you can just change dba_dependencies to all_dependencies to see the equivalent (visible) results.

If you run the SQL manually as the same user you're connected to SQL Developer as, you'll see the same results.

Upvotes: 3

Related Questions