база-данных – How and with what to collect documentation on the database?

Question:

Oracle database available. For about 20 years, different people have contributed to writing a lot of stored code, with non-trivial solutions and puzzling relationships. There is no documentation as well as requirements for this whole case.

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

PLSQL Developer is able to build a simple HTML documentation based on the specified packages in a ugly way.
Console utility pldoc . Able 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 exist for this?

Answer:

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

First build the data model using Oracle tools. 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.
Analysis of the user application(s) for calling these endpoints from it(them). That is, we are looking for which tables and columns are filled in and which ones are read, we are looking for calls to procedures and functions.
If the endpoint is not called from anywhere, set the old_ prefix for it.
Repeat steps 1-3, excluding objects with the old_ prefix from the search until there is nothing left.
If the connection is established: create a description of the connection in the form: User application – Data representation – Data object – Internal links in the object.
If cleaning the base is not included in the plans, exclude points 3-4.

To describe relationships in a database, it is better to write a separate application (which itself will be another database). Based on it, in the future it will be easier to write improvements to the current database.

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

Post Views: 0