How and with what to collect database documentation?


The Oracle database is available. Over the course of about 20 years, various people have participated in writing a large amount of stored code, with non-trivial solutions and mysterious relationships. There are no documentation as well as requirements for this whole case.

I would like to automatically collect as much information as possible about the database (tables, procedures, triggers, etc.) in a readable form. Which objects are modifying which tables and which ones depend on, as well as the sequence of calls. There are many solutions for plotting charts using tables (for example, Oracle Data Modeler, ERwim, or PowerDesigner), but we managed to find little about the stored code:

  1. PLSQL Developer is able to build a simple HTML documentation based on the specified packages in an ugly way.

  2. The pldoc console utility. Knows how to build something similar to javadoc if there are comments to the code in the required format. Open Source and can be modified.

Hence the question:

What information and in what form does it make sense to collect about database objects and what other tools are there for this?


What information and in what form does it make sense to collect about database objects

  1. First, build a data model using Oracle tools. I am interested in entry endpoints that are not called from Oracle itself, but from outside. That is, we are looking for non-empty tables, as well as procedures and functions that are called not from other procedures and functions, but from somewhere outside.
  2. Analysis of user (s) applications in order to call these endpoints from it (them). That is, we are looking for which tables and columns are filled in and which are read, we are looking for calls to procedures and functions.
  3. If the endpoint is not called from anywhere, set the old_ prefix for it.
  4. Repeat old_ 1-3, excluding objects with the old_ prefix from the search until there is nothing superfluous left.
  5. If the connection is established: create a description of the connection in the form: User application – Data presentation – Data object – Internal links in the object.
  6. If cleaning the database is not included in the plans, exclude items 3-4.

To describe links in a database, it is better to write a separate application (which in itself will be another database). On its basis, in the future it will be easier to write revisions to the current database.

Unfortunately, no application I know of can fully accomplish all of these tasks. It will either have to be written or done in a semi-manual mode, in close cooperation with users. And as my personal experience shows, I will have to write an application that will work in a semi-manual mode, in close cooperation with users.

Scroll to Top