Dependencies between a table and database stored program units can be found in the DBA_DEPENDENCIES dictionary view. However, this only records the fact that there is a dependency.
Say you have a long-running report that will benefit from an index on a table. It would be good to know if there’s a packaged procedure that does a large update on that table and may therefore take longer with a new index to populate. How can you figure out if there are any such procedures ?
Well, you’re in luck.
CRUDO is not, as you may think, a domestic cleaning product. It is, in fact, a PL/SQL application for generating CRUD matrices for Stored Program Units against the tables that they reference.
I’ve been playing around with something like this for a while now, and
this time, I’ve posted the resulting code on GitHub.
CRUDO enables you to :
- Determine a CRUD Matrix for each database stored program unit against a given table ( or synonym on that table)
- generate Matrices for all tables in a schema
- record the results in a table for fast querying
- update existing records only for program units that have been changed since the last update of it’s record
- specify override records for situations where the dependency may not be recorded in DBA_DEPENDENCIES(e.g. when using dynamic SQL statements)
- view runtime information on what it is doing
- amend logging levels to provide more detailed information on large runs
- laugh at my inability to come up with a decent name for it
I developed CRUDO on Oracle 11gR2 and it will run on any Oracle Database Edition ( XE, Standard One, Standard, Enterprise).