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 a trigger mfg_os_id_trig exists on this table.

Back


  434