Oracle Dependencies
-- When a table has to be enhanced in Oracle database - 
-- drop or add column(s), or rename columns or any type of
-- enhancement, its dependencies has to be analyzed.  Developer tools such as Oracle's 
-- SQL Developer can show the dependencies.  However, if one wants a script or code to
-- analyze dependencies, here is the PL/SQL block to do the analysis.


BEGIN
  DBMS_UTILITY.get_dependency(type    => p_object_type
                              ,schema => p_schema_name
                              ,name   => p_object_name);
END;
/

-- Example:

BEGIN
   DBMS_UTILITY.get_dependency('TABLE',
    'EBIRPT',
    'MFG_PROCESS_SCHEDULE');
END;
/

Oracle Dependancy

-- The output shows that the table "MFG_PROCESS_SCHEDULE" is used in the procedures 
-- update_mfg_wip_dates, 
-- update_mfg_non_wip_dates,
-- update_mfg_kpi
-- that are a part of the package edwbi_reports_pkg and 
-- trigger mfg_os_id_trig exists on this table.


-- Also ALL_DEPENDENCIES can be queried to get details of dependencies 

SELECT   name
  ,referenced_owner ,referenced_name ,referenced_type
FROM  all_dependencies
WHERE  owner = 'EBIRPT'
AND    type = 'TABLE'
;


Database Reference  19c  12c  11g


Last Revised On: December 27th, 2020

  52903