Why we search database object dependencies

In large companies you often observe many multi-database and multi-platform legacy systems. This setup makes it almost impossible to discover information regarding the dependencies between database objects, programs, forms and reports. Applications can include several hundred stored procedures, Oracle forms and reports using thousands of objects inside the Oracle database (tables, views, packages, synonyms and DB links).

Lack of technical documentation, or partial documentation, incompatibility between application's documentation and implementation complexity are just some of the reasons why it is almost impossible to determine the various dependencies to any given object. Legacy systems have lost a common structure with a common logic and it has become hard to analyze and identify the object's dependencies and relationships.

We can see something similar on the internet. At most you can see the logic of a website, but the structure and relationships on the internet itself are impossible to know without a web crawler.

I see some reasons for to search object's dependencies:

1. I wants to change object definition and I want to know the objects that will be invalid after the change (DB objects).
2. What's external object use DB objects. I know the some external objects work with database:
            -  Oracle* Form/Reports
            -  xNIX and sql scripts
            -  Word files (Global/Detail design documents)
            -  Flat files (Java, C, XML)
            -  Informatica workflows work against DB

3. Reverse engineering of legacy systems

Only if we search dependencies in DB we have Oracle support as dependencies information in dictionary views 

• ALL_DEPENDENCIES
• DBA_DEPENDENCIES
• USER_DEPENDENCIES
• PUBLIC_DEPENDENCIES

For  the answer to the second question we don't have any information. We use a kind of search methods for a string search if it's possible.


For the third reason it doesn't enough to know what's dependency so how did the object use. We want to know what's context of the object usage, what's a type of table usage: select/insert/update/delete.

For a big company it's desirable to receive one overall answer for all its applications.