I have a system that involves a rather large and complex oracle database, and while the system as a whole is easy enough (for me) to understand, the database feels more like a black box of mysterious powers that I need some assistance wrapping my head around.

Are there any analytical tools to help with this? Ideally, I’d like a tool that would connect to the database and make a diagram over which tables connect to which other tables, as well as naming any stored procedures relevant to each. If such a tool exists, that’d be grand, but any other tools that can help breaking down this rube goldberg machine into something more digestible would be great.

And yes, it is much overdue for a postgresql successor. Getting the current contraption mapped out is the first step in building its replacement.

EDIT: Oh, and this database also interacts a lot with orasched, as well as external processes. A lot of things that should have been a cron job is now a procedure stored in the scheduler. A lot of things that should have been an external script/program is a stored procedure. And most offensive of all: many things that should have been in a config file is part of an SQL table. Whoever built this contraption must’ve been paid to write SQL and nothing else.

  • PhilipTheBucket@ponder.cat
    32·
    7 days ago

    Dump every single schema, paste the whole thing to claude.ai, ask it to generate graphviz markup showing the whole thing as you requested. Give the result to graphviz, see what happens.

    It might not work or might not work perfectly, but that is what I would try and it definitely has the potential to be better than staring at the text trying to make sense of it all. Depending on the size of the beast, you might have to do it in pieces with a defined mapping of table names to graphviz node names so that piecing it out works smoothly.