Visualizing dependencies between Oracle objects (with Groovy & Graphviz)

“How can we visualize the dependencies between the database views in a complex database schema?” was a question recently asked in our team. Besides the obvious use of a tool that supports such a feature, another way is to presented below, using a script and Graphviz, a well-known, open-source graph visualization software.

Our question was about views in particular, but the solution below will also handle other types of database objects, such as tables, triggers and stored procedures.

Getting the Dependencies

This is a very easy task, in fact: Oracle maintains the relationships automatically and exposes them through three views. As such, we do not need to rely on reverse engineering DDL scripts.

  • USER_DEPENDENCIES returns the dependencies of the objects ownedby the current user.
  • ALL_DEPENDENCIES returns the dependencies of the objects visibleto the current user (so it’s a superset of the former)
  • DBA_DEPENDENCIES returns the dependencies of the all database objects. It requires SYSDBA privileges.

The most useful fields are returned by the following query:

select name, type, referenced_owner, referenced_name, referenced_type from user_dependencies

whose results can be read as “The object name of type type depends on the object referenced_name of type referenced_type owned by referenced_owner (as implied by the use of user_dependencies, the owner of the name object is the current user). The referenced_owner column is useful to filter out various Oracle objects such as DUAL.

Visualizing the Dependencies

There are several graph layout solutions out there. One of those, used below, is based on Graphviz. Another possibility is to represent the graph the XML-based GraphML and use software supporting it, such as the products from yWorks.

To use Graphviz, download it from http://www.graphviz.org/Download..php (many platforms are supported). The script described below will assume the bin subdirectory of the distribution is in the path.

Graphviz uses a graph description language called ‘dot’. You don’t need to understand it to use the script, but you can have a look at the (very simple in this case) file that is produced by the script.

The Script

The transformation of the SQL query results into the graph description language supported by the visualization tool of choice is a trivial problem so you can implement it in your preferred language. Below is a solution in Groovy. Why Groovy? Because it is considerably terser and more elegant than Java (and, if it matters, it has “agile” in the official tag line for the language 🙂

A few notes about the script:

  • before running, update the first 3 lines to match your configuration: database server hostname and port, database name, user and password
  • the resulting graph will use different colours to distinguish object types. Feel free to change the colours to match your preferences. To use names instead of RGB values, see the full list of colour names here
  • to aid in filtering the objects, the script can ignore objects of certain types. The types that areprocessed are given in the relevantTypes list. Below we are ignoring relationships involving PACKAGE and PACKAGE BODY (on either side of the dependency).
  • the script also filters out dependentobjects not owned by the current user, irrespective of their type. The WHERE clause in the SQL query handles this. The reason will become apparent if you don’t filter them: you will get references such as to the DUAL pseudo-table, DBMS_OUTPUT if a stored procedure prints out text or SYS_STUB_FOR_PURITY_ANALYSIS.
  • I used Cambria as a font, but if you don’t have it installed, you can choose another font or simply remove all occurrences of fontname=”Cambria” in the script. Fonts matter, though, especially after one reads The Elements of Typographic Style 🙂
connString = "jdbc:oracle:thin:@localhost:1521:mydb"
dbUser = "myuser"
dbPwd = "mypwd"
query = "select name, type, referenced_name, referenced_type from user_dependencies where referenced_owner='" + dbUser.toUpperCase() + "'"
typeColours = ['TABLE': 'black', 'VIEW': 'palegreen2', 'TRIGGER': 'lightcoral', 'PROCEDURE': 'lightskyblue1', 'PACKAGE BODY': 'brown', 'PACKAGE': 'brown']
static relevantTypes = ['TABLE', 'VIEW', 'TRIGGER', 'PROCEDURE']

links = ''
nodes = [:]
sql = groovy.sql.Sql.newInstance(connString, dbUser, dbPwd, "oracle.jdbc.OracleDriver")

sql.eachRow(query) {
    if (relevantTypes.contains(it.type) && relevantTypes.contains(it.referenced_type)) {
        nodes[it.name] = "${it.name} [fillcolor= ${typeColours[it.type]}]"
        nodes[it.referenced_name] = "${it.referenced_name} [fillcolor= ${typeColours[it.referenced_type]}]"
        links += "  ${it.name} -> ${it.referenced_name} n" } } s = "digraph dep{n" s += ' graph[fontname="Cambria"];n'
s += '  node[ fontname="Cambria" shape="rect" style="filled"];n'
s += '  edge[ arrowhead="inv" ];n'
nodes.values().each {
    s += "  ${it};n" } new File("dependencies.dot").write("$s $links }") "dot -Tpng -o dependencies.png dependencies.dot".execute().text

Running the Script

Save the above script into a file called GraphDependencies.groovy. Also know where the Oracle JDBC driver is, you will need in a moment. Then from a console, type the following (replacing the driver path with the correct one).

groovy -cp C:oracleproduct10.1.0Db_2jdbclibojdbc14.jar GraphDependencies

The script will create a file called dependencies.png in the same directory. Below is an example. After obfuscating the object names and removing many objects to make the graph fit, the image is somewhat bland, but you can get an idea.
Some Final Notes

  • Now that you’ve seen the script, isn’t sql.eachRow(query){} an excellent example of terseness? A single statement to execute a query, iterate through its results and access any field with it.field name. I love Groovy and how it uses closures.
  • a useful enhancement would be to remove trivial dependencies from the graph. If object A and object B are only connected with each other, the value of representing their dependency is not particularly high. The graph is more useful for complex relationships.

Hope this helps,
Razvan

It's only fair to share...
Share on FacebookGoogle+Tweet about this on TwitterShare on LinkedIn

Leave a Reply