Skip to Main Content

Object Dependencies

Target TypeTarget OwnerTarget NameStatusN TotalObjectsMb TableMb IndexMb TotalSql StatementExp Statement
PACKAGETEDSTRUIKTED_PAGE_SUMMARYVALID48[APEX]25384drop PACKAGE TED_PAGE_SUMMARY;
TABLETEDSTRUIKTED_LYRICS_ALBUMSVALID6TEDSTRUIK.TED_LYRIC (PACKAGE BODY), [APEX]25384, [FK]TEDSTRUIK.TED_LYRICS_SONGS.06.06.12drop TABLE TED_LYRICS_ALBUMS;C:\oracle\product\11.2.0\client_1\bin\exp username/password@XE file=TEDSTRUIK.TED_LYRICS_ALBUMS.dmp tables=TEDSTRUIK.TED_LYRICS_ALBUMS statistics=NONE
TABLETEDSTRUIKTED_AMAZON_PRODUCTSVALID5TEDSTRUIK.TED_AMAZON (PACKAGE BODY), TEDSTRUIK.TED_AMAZON_PRICES_MINUTES_V (VIEW), TEDSTRUIK.TED_AMAZON_PRODUCTS_BI_TRG (TRIGGER), [FK]TEDSTRUIK.TED_AMAZON_LOGS, [FK]TEDSTRUIK.TED_AMAZON_PRICES.06.13.19drop TABLE TED_AMAZON_PRODUCTS;C:\oracle\product\11.2.0\client_1\bin\exp username/password@XE file=TEDSTRUIK.TED_AMAZON_PRODUCTS.dmp tables=TEDSTRUIK.TED_AMAZON_PRODUCTS statistics=NONE
TABLETEDSTRUIKTED_LYRICS_SONGSVALID4TEDSTRUIK.TED_LYRIC (PACKAGE BODY), [APEX]253842.192.19drop TABLE TED_LYRICS_SONGS;C:\oracle\product\11.2.0\client_1\bin\exp username/password@XE file=TEDSTRUIK.TED_LYRICS_SONGS.dmp tables=TEDSTRUIK.TED_LYRICS_SONGS statistics=NONE
TABLETEDSTRUIKTED_SKYDIVE_LOCATIONSVALID4TEDSTRUIK.TED_P1093 (PACKAGE BODY), TEDSTRUIK.TED_P1093 (PACKAGE), [APEX]25384.06.06.12drop TABLE TED_SKYDIVE_LOCATIONS;C:\oracle\product\11.2.0\client_1\bin\exp username/password@XE file=TEDSTRUIK.TED_SKYDIVE_LOCATIONS.dmp tables=TEDSTRUIK.TED_SKYDIVE_LOCATIONS statistics=NONE
  • 1 - 5 of 68

Info

An improved method to determine dependencies for database objects, including dependencies in Apex applications.

Unfortunately, the dba_dependencies view doesn't include every possible object dependency:
  • Apex applications:
    This is handled here by table ted_p1109_apex_dependencies, which is filled manually using the Utilities -> Database Object Dependencies report
  • Dynamic SQL:
    This is a manual step, alas. Table ted_p1109_dynamic_sql is included in the ted_p1109_v view. The table has to be filled manually; view ted_p1109_dynamic_sql_v can be used to determine its content, but you may want to add other sources to that.
  • Foreign keys:
    I use dba_constraints in the ted_p1109_v view to add those.
  • Replicated tables, updatable materialized view tables and master tables of a materialized view for which a materialized view log has been created:
    Trying to rename these results in an ORA-26563, so I exclude these using dba_mview_logs.

The ted_p1109_v view includes columns for table/index size, plus SQL and EXP statements you can use to drop/rename/export the objects.

To get the objects to compile, you need these privileges:
Note 1: This method is better than just using the dba_dependecies view, but it is not 100% complete. Use at your own risk.
To illustrate the fact that this method is also not perfect: in the example above, some objects seem to have 0 dependencies, e.g. package ted_p1026. However this package is used in Apex, but only via a call to the ted_page_summary package (which queries some dba views to show the package source etc.). Therefore, since it is not called directly, it will not show up in the Apex Database Object Dependencies report.

Note 2: The Database Object Dependencies report is not complete. It ignores PL/SQL code in Dynamic Actions, and references to tables/view/materialized views which are used in processes like Automated Row Fetch. There may of course be even more ignored dependencies - you don't know what you don't know... :-)
This is a query to add these ignored dependencies to the ted_p1109_apex_dependencies table mentioned above:

Code

Region

Identification
Sequence
Title
Type
Source
Source Type
Region Source

Table

Table
Name
DDL

Table

Table
Name
DDL

View

View
Name
DDL

View

View
Name
DDL