Thanks for the question, Dinakar.

Asked: June 04, 2024 - 1:48 pm UTC

Last updated: July 30, 2024 - 5:28 am UTC

Version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Viewed 1000+ times

You Asked

We have a UI based application where users come in and setup SQL's to get excel based reports back, there are multiple options to chose on the schedule of the SQL execution and other related parameters. It's a free text box, the expectation is that the users will test their SQL in the database before they setup the SQL in this tool. One of our current requirement is to identify dependency of objects in the SQL.
if this is the SQL:
select
a.col1, b.col2, c.col3
from
table_a a,
view b,
pipe_line_function c
where a.col1 = b.col2
and b.col2 = c.col3;

As a list of dependent objects, the requirement is to get:
TABLEA
VIEW and the objects within the VIEW until we drill down to the base tables or the most granular.
pipe_line_function and the objects within until we drill down to the base tables or the most granular.

Is this possible using any new SQL functions, dependency functions etc., without creating a view of the above SQL setup by the users. We are aware about DBA_DEPENDENCIES. it is not possible to create a view and then grab the dependencies and hence this ticket.

and Connor said...

If you can't create anything (view, procedure, etc), then you're not going to be able to get *everything* because the dependency tree for a cursor/SQL is stored in memory and will be transient.

The closest you could probably get it to determine the execution plan, and then dig into the plan table, eg

SQL> create or replace
  2  view my_view as
  3  select e.*, d.dname
  4  from emp e, dept  d
  5  where e.deptno = d.deptno;

View created.

SQL> explain plan for
  2  select *
  3  from my_view, hr.employees
  4  where my_view.hiredate = employees.hire_date;

Explained.

SQL> select distinct object_owner, object_name
  2  from plan_table;

OBJECT_OWNER                   OBJECT_NAME
------------------------------ ---------------------

MCDONAC                        EMP
HR                             EMPLOYEES
MCDONAC                        DEPT_PK
MCDONAC                        DEPT

If you want more than that, you'll a database object (view etc) so that you can dig into DBA_DEPENDENCIES

Rating

(3 ratings)

Comments

Thank you

Dinakar, June 07, 2024 - 11:38 am UTC

Thank you, very helpful!
If it's a pipelined table function with multiple views and objects underneath, we can only get the function details and not the underlying objects in the Explain Plan. Probably in that case, we could directly go to DBA_DEPENDENCIES. Any thoughts, comments?

June 10, 2024 - 6:56 am UTC

Execution plan will tell you its a function, and then dig into xxx_DEPENDENCIES from there

SQL> create or replace function pipedemo return sys.odcinumberlist pipelined is
  2  begin
  3    pipe row ( 1 );
  4    return;
  5  end;
  6  /

Function created.

SQL> select * from pipedemo();

COLUMN_VALUE
------------
           1

SQL> explain plan for
  2  select * from pipedemo();

Explained.

SQL> select * from plan_table
  2  @pr
==============================
STATEMENT_ID                  :
PLAN_ID                       : 1282
TIMESTAMP                     : 10-JUN-24
REMARKS                       :
OPERATION                     : SELECT STATEMENT
OPTIONS                       :
OBJECT_NODE                   :
OBJECT_OWNER                  :
OBJECT_NAME                   :
OBJECT_ALIAS                  :
OBJECT_INSTANCE               :
OBJECT_TYPE                   :
OPTIMIZER                     : ALL_ROWS
SEARCH_COLUMNS                :
ID                            : 0
PARENT_ID                     :
DEPTH                         : 0
POSITION                      : 29
COST                          : 29
CARDINALITY                   : 8168
BYTES                         : 16336
OTHER_TAG                     :
PARTITION_START               :
PARTITION_STOP                :
PARTITION_ID                  :
OTHER                         :
OTHER_XML                     :
DISTRIBUTION                  :
CPU_COST                      : 1937344
IO_COST                       : 29
TEMP_SPACE                    :
ACCESS_PREDICATES             :
FILTER_PREDICATES             :
PROJECTION                    :
TIME                          : 1
QBLOCK_NAME                   :
==============================
STATEMENT_ID                  :
PLAN_ID                       : 1282
TIMESTAMP                     : 10-JUN-24
REMARKS                       :
OPERATION                     : COLLECTION ITERATOR
OPTIONS                       : PICKLER FETCH
OBJECT_NODE                   :
OBJECT_OWNER                  :
OBJECT_NAME                   : PIPEDEMO
OBJECT_ALIAS                  : "KOKBF$0"@"SEL$2"
OBJECT_INSTANCE               :
OBJECT_TYPE                   : PROCEDURE
OPTIMIZER                     :
SEARCH_COLUMNS                :
ID                            : 1
PARENT_ID                     : 0
DEPTH                         : 1
POSITION                      : 1
COST                          : 29
CARDINALITY                   : 8168
BYTES                         : 16336
OTHER_TAG                     :
PARTITION_START               :
PARTITION_STOP                :
PARTITION_ID                  :
OTHER                         :
OTHER_XML                     : <other_xml><info type="has_plsql">yes</info><info
type="db_version">21.0.0.0</info><info type="parse_schema"><![CDATA["MCDONAC"]]></info><info
type="plan_hash_full">1182393574</info><info type="plan_hash">2048677713</info><info
type="plan_hash_2">1182393574</info><stats type="compilation"><stat
name="bg">2</stat></stats><qb_registry><q o="18" f="y"
h="y"><n><![CDATA[SEL$F5BB74E1]]></n><p><![CDATA[SEL$1]]></p><i><o><t>VW</t><v><![CDATA[SEL$2]]></v>
</o></i><f><h><t><![CDATA[KOKBF$0]]></t><s><![CDATA[SEL$2]]></s></h></f></q><q
o="2"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[from$_subquery$_001]]></t><s><![CDATA[SEL$1]]></s></
h></f></q><q
o="2"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[KOKBF$0]]></t><s><![CDATA[SEL$2]]></s></h></f></q></
qb_registry><outline_data><hint><![CDATA[FULL(@"SEL$F5BB74E1"
"KOKBF$0"@"SEL$2")]]></hint><hint><![CDATA[OUTLINE(@"SEL$2")]]></hint><hint><![CDATA[OUTLINE(@"SEL$1
")]]></hint><hint><![CDATA[MERGE(@"SEL$2"
>"SEL$1")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$F5BB74E1")]]></hint><hint><![CDATA[ALL_ROWS]]><
/hint><hint><![CDATA[DB_VERSION('21.1.0')]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('21.1.0'
)]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data></other_xml>
DISTRIBUTION                  :
CPU_COST                      : 1937344
IO_COST                       : 29
TEMP_SPACE                    :
ACCESS_PREDICATES             :
FILTER_PREDICATES             :
PROJECTION                    : VALUE(A0)[22]
TIME                          : 1
QBLOCK_NAME                   : SEL$F5BB74E1

PL/SQL procedure successfully completed.

SQL>

dba_dependency hierarchical query

Dinakar, July 18, 2024 - 11:09 am UTC

And would you recommend using this function to grab all dependency in the tree from dba_dependency view. Performance is not a concern, but we don't want to miss any objects:
https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:2165199506829

July 22, 2024 - 1:06 pm UTC

The query at the top is missing PRIOR before referenced name & you may need to add NOCYCLE if there are dependency loops:

select lpad(' ',(level-1)*2)||name||'('|| type || ')' from dba_dependencies
where level <2
connect by nocycle prior referenced_owner=owner
and prior referenced_name=name
start with name = 'OBJECT_NAME'

This will give you all the things OBJECT_NAME depends on.

Note this only lists static dependencies. If you have dynamic SQL or PL/SQL, objects these reference are not shown by the dependencies views.

dba_dependencies

Dinakar Ullas, July 24, 2024 - 5:42 pm UTC

In the last response(pasted the query here for reference), you actually meant:

referenced_name & referenced_type in the select?
In the link, I was pointing to Tom's pipelined table function. This below query will also work.
And if I want to go deep down, use level < 20? would that have any other side effects apart from performance?

select lpad(' ',(level-1)*2)||name||'('|| type || ')' from dba_dependencies
where level <2
connect by nocycle prior referenced_owner=owner
and prior referenced_name=name
start with name = 'OBJECT_NAME'

July 30, 2024 - 5:28 am UTC

referenced_name & referenced_type in the select?

All three (owner, name, type) depending on your requirement.

And if I want to go deep down, use level < 20? would that have any other side effects apart from performance?

DBA_DEPENDENCIES is under the covers a complex view. If you want to data mine it repeatedly, it might be worth taking a copy of it into a table.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thank you

dba_dependency hierarchical query

dba_dependencies

More to Explore

PL/SQL demos

PL/SQL docs

Find objects used in a SQL

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thank you

dba_dependency hierarchical query

dba_dependencies

More to Explore

PL/SQL demos

PL/SQL docs