14 Replies Latest reply on Oct 1, 2013 3:52 PM by rp0428

    Finding Referenced Objects recursively

    user10566312

      Hi,

       

      I have some views. I would like to find out all the referenced table, functions, views inside those views. In case the views references another view then I would like to know the objects referenced inside the child view and so on. Can someone provide me the SQL for this? Would be nice if the output is nicely formatted.

       

      Also, I want to drill down to further level only in case the referenced object is a view.

       

      Message was edited by: user10566312

        • 1. Re: Finding Referenced Objects recursively
          ibney

          Use below query

           

          select name , type , referenced_name , referenced_type

          from user_dependencies where name = 'YOUR_VIEW_NAME' and type = 'VIEW' and

          referenced_type in ('TABLE','FUNCTION')

          • 3. Re: Finding Referenced Objects recursively
            Frank Kulash

            Hi,

             

            Query the data dictionary views USER_DEPENDENCIES, ALL_DEPENEDENCIES or (if you have privileges) DBA_DEPENDENCIES.

            A CONNECT BY query will work in any version of Oracle.  Starting in Oracle 11.2, you also have the option of using a recursive WITH clause.

             

            Exactly how to do it depends on exactly what you want.  If you'd like help, post a complete test case (CREATE statements for tables, views and/or functions that depend on one another) and the results you want to see from that input.

            Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

            See the forum FAQ: https://forums.oracle.com/message/9362002

            1 person found this helpful
            • 4. Re: Finding Referenced Objects recursively
              user10566312

              Hi can you make it recursive and formatted?

              1 person found this helpful
              • 5. Re: Finding Referenced Objects recursively
                Purvesh K

                Maybe something this way:

                 

                select name, lpad(' ', 4 * (level - 1), ' ') ||  referenced_name referenced_name, referenced_type

                  from user_dependencies

                where referenced_name != 'STANDARD'

                start with name = 'VW_VW_BASE_TABLE'

                connect by name = prior referenced_name;

                • 6. Re: Finding Referenced Objects recursively
                  user10566312

                  I have written the below SQL. However, the SYS_CONNECT_BY_PATH is not showing the leaf node  child node in output. I have considered object name as parent and referenced objects as children. For example for the first row the PATH should have been /BK_LOTVSCT_SUM_VW/MTD_BK_LOTVSCT_DTL as per the SYS_CONNECT_BY_PATH functionality.

                   

                   

                   

                  
                  SELECT level,   
                    type||' '||owner||'.'||name ASparent_object,
                    LPAD(' ', 2*(LEVEL - 1)) || referenced_type ||' '||referenced_owner||'.'||referenced_name ASunderlying_object,
                    SYS_CONNECT_BY_PATH( name, '/') ASPath
                  FROMall_dependencies
                  WHERE REFERENCED_OWNER      <> 'SYS'
                  START WITH NAME                          IN ('BK_LOTVSCT_SUM_VW')
                  ANDOWNER                                   = 'ADMIN
                  CONNECT BY nocycle priorreferenced_owner = owner
                  AND prior referenced_name                = name
                  AND priorreferenced_type                  = type
                  ORDER SIBLINGS BY referenced_name;
                  
                  
                  
                  
                  
                  

                   

                   

                  1VIEW admin.BK_LOTVSCT_SUM_VWVIEW admin.MTD_BK_LOTVSCT_DTL/BK_LOTVSCT_SUM_VW
                  2VIEW admin.MTD_BK_LOTVSCT_DTL  VIEW admin.MTD_BK_CTVSCT_VW/BK_LOTVSCT_SUM_VW/MTD_BK_LOTVSCT_DTL
                  3VIEW admin.MTD_BK_CTVSCT_VW    VIEW admin.MTD_BK_CT_VW/BK_LOTVSCT_SUM_VW/MTD_BK_LOTVSCT_DTL/MTD_BK_CTVSCT_VW
                  4VIEW admin.MTD_BK_CT_VW      VIEW admin.MTD_CT_VW/BK_LOTVSCT_SUM_VW/MTD_BK_LOTVSCT_DTL/MTD_BK_CTVSCT_VW/MTD_BK_CT_VW
                  5VIEW admin.MTD_CT_VW        TABLE admin.LOT/BK_LOTVSCT_SUM_VW/MTD_BK_LOTVSCT_DTL/MTD_BK_CTVSCT_VW/MTD_BK_CT_VW/MTD_CT_VW
                  5VIEW admin.MTD_CT_VW        TABLE admin.LOTRUN/BK_LOTVSCT_SUM_VW/MTD_BK_LOTVSCT_DTL/MTD_BK_CTVSCT_VW/MTD_BK_CT_VW/MTD_CT_VW
                  5VIEW admin.MTD_CT_VW        TABLE admin.LOTRUN_HOLD_RELS/BK_LOTVSCT_SUM_VW/MTD_BK_LOTVSCT_DTL/MTD_BK_CTVSCT_VW/MTD_BK_CT_VW/MTD_CT_VW
                  5VIEW admin.MTD_CT_VW        TABLE admin.PROD_CUR_PART_PRCD/BK_LOTVSCT_SUM_VW/MTD_BK_LOTVSCT_DTL/MTD_BK_CTVSCT_VW/MTD_BK_CT_VW/MTD_CT_VW

                   

                  Message was edited by: user10566312

                  • 7. Re: Finding Referenced Objects recursively
                    Purvesh K

                    If you are missing the Leaf nodes in the Path, you can use the CONNECT_BY_ISLEAF pseudo column to append it to your path:

                     

                    drop table test_table;
                    drop table test_table1;
                    drop table test_table2;
                    drop table test_table3;

                     

                    drop view vw_test;
                    drop view vw_vw_test;
                    drop view vw_vw_vw_test;

                     

                    create table test_table (col number);
                    create table test_table1 (col number);
                    create table test_table2 (col number);
                    create table test_table3 (col number);

                     

                    create or replace view vw_test as
                    select sum(a.col) a_sum, sum(b.col) b_sum, sum(c.col) c_sum, sum(d.col) d_sum
                      from test_table a, test_table1 b, test_table2 c, test_table3 d;
                     
                    create or replace view vw_vw_test as
                    select a_sum, b_sum
                      from vw_test;
                     
                    create or replace view vw_vw_vw_test as
                    select a_sum + b_sum sm_cols
                      from vw_vw_test;

                     

                    select name, path || decode(isleaf, 1, '\' || trim(referenced_name), null) path

                      from (

                            select name, lpad(' ', 4 * (level - 1), ' ') ||  referenced_name referenced_name, referenced_type,

                                   sys_connect_by_path(name, '\') path, level, connect_by_isleaf isleaf

                              from user_dependencies

                             where referenced_name != 'STANDARD'

                             start with name = 'VW_VW_VW_TEST'

                            connect by name = prior referenced_name

                           );

                     

                    NAME                           PATH
                    ------------------------------ -----------------------------------------------
                    VW_VW_VW_TEST                  \VW_VW_VW_TEST
                    VW_VW_TEST                     \VW_VW_VW_TEST\VW_VW_TEST
                    VW_TEST                        \VW_VW_VW_TEST\VW_VW_TEST\VW_TEST\TEST_TABLE
                    VW_TEST                        \VW_VW_VW_TEST\VW_VW_TEST\VW_TEST\TEST_TABLE1
                    VW_TEST                        \VW_VW_VW_TEST\VW_VW_TEST\VW_TEST\TEST_TABLE2
                    VW_TEST                        \VW_VW_VW_TEST\VW_VW_TEST\VW_TEST\TEST_TABLE3

                    6 rows selected

                     

                     

                     

                    user10566312 wrote:

                    I have written the below SQL. However, the SYS_CONNECT_BY_PATH is not showing the leaf node in output. I have considered object name as parent and referenced objects as children. For example for the first row the PATH should have been /BK_LOTVSCT_SUM_VW/MTD_BK_LOTVSCT_DTL as per the SYS_CONNECT_BY_PATH functionality.

                     

                    No, it won't be. Since you are traversing the hierarchy from the Root node to Leaf, a Parent will not know its children, until you access them. If you have only one child, it might be alright, but how do you expect it to work if there are multiple children? Hence, the Child node will get its hierarchy correct. I hope it answers your questions. If you, please let us know.

                     

                    Also, I have posted a test case, which I believe resembles your situation. I would be great if you could explain your question(s) considering the test case or modifying it to suit your scenario. This allows people to replicate it at their databases and provide correct solutions.

                    • 8. Re: Finding Referenced Objects recursively
                      user10566312

                      Slight correction in previous reply. pls have re-look.

                      • 9. Re: Finding Referenced Objects recursively
                        Purvesh K

                        Yes, but your requirement seems unjust. I have tried to explain the reasons in my previous post. Please have a look and let me know you are not satisfied with it.

                        • 10. Re: Finding Referenced Objects recursively
                          user10566312

                          As per the example given here http://www.sqlsnippets.com/en/topic-12568.html the SYS_CONNECT_BY_PATH function is supposed to append the current child name in the path.

                          • 11. Re: Finding Referenced Objects recursively
                            Purvesh K


                            No. I do not see that happening. Please read SYS_CONNECT_BY_PATH for more understanding. Documentation clearly states:- "It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition".

                            Thus, your requirement to get the child name in the parent row is incorrect. You will have to look for a workaround in this situation.

                             

                            Also, in the link you provided, I do no see any example displaying Child node information. Would you like to precisely mention which example you were mentioning about?

                             

                            I would like to give a kind suggestion, it is lot better to explain the scenario with a test case which allows volunteers to understand and visualize situation better. I have been mentioning this to you, but it looks like you choose to ignore it, thus delaying in getting a correct response.

                            • 12. Re: Finding Referenced Objects recursively
                              user10566312

                              Hi Purvesh,

                               

                              As per the first example in the URL, column parent_key is the parent column and key is the child column. So if you see the output, the value in the KEY_INDENTED (child column key) field is coming appended as part of KEY_PATH.

                               

                              And as Oracle says it return value from root to node, so the first record in my output should have /MTD_BK_LOTVSCT_DTL appended to the path. And similarly for other records.

                              • 13. Re: Finding Referenced Objects recursively
                                Purvesh K

                                Your hierarchy starts with BK_LOTVSCT_SUM_VW; Hence, the first row will have the root node populated i.e. current row. As you traverse down the hierarchy, the nodes will be appended to the Path.

                                Now, the view MTD_BK_LOTVSCT_DTL, is child of BK_LOTVSCT_SUM_VW, hence while processing BK_LOTVSCT_SUM_VW, Oracle does not have any knowledge of the Child node; Hence, no detail of MTD_BK_LOTVSCT_DTL will be appended to Path while BK_LOTVSCT_SUM_VW is being processed.

                                However, while traversing the tree, when oracle processes MTD_BK_LOTVSCT_DTL node, it knows the parent node and thus deatils of parent node are appended to the Path.

                                 

                                The term root to node, need not be taken literally. It means, Oracle will append details from Root node to the Current node being processed. but not that, it will append details of Child Nodes or the nodes that are yet to be processed.

                                • 14. Re: Finding Referenced Objects recursively
                                  rp0428

                                  Why don't you quit reinventing the wheel and use the utldtree.sql script that Oracle provides with the database?

                                   

                                  See my extensive replies in this thread from last year:

                                  https://forums.oracle.com/thread/2438703

                                   

                                  This Oracle Database doc reference describes one of the tables used by the script

                                  http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_5093.htm