DBA_OBJECTS Tree – Modelled As A Graph in NEO4J, Visualized With Gephi


The structure for Oracle objects is a tree structure. Not a table structure in rows and columns.

As I wrote in my posts on the parents and children of database objects, a tree structure of Oracle object might be:

TYPE
   TABLE
      VIEW
         FUNCTION
            PROCEDURE

In another post, there is a query I wrote to show object dependencies. DBA_DEPENDENCIES will show the object’s parents. Or the children. But only one level up, or down.

Trying to see the tree structure using SQL queries is problematic. The reason being, the output from SQL is not a tree at all. And, as I wrote before, the very same object can be seen multiple times in the output.

NEO4J And Gephi:

Recently, I’ve been using the graph database, NEO4J. It is perfectly suited to create tree structures and store them in a database.

In Oracle 11.2, I snagged the data from DBA_OBJECTS and DBA_DEPENDENCIES, created Cypher commands to insert the nodes into NEO4J, and created the relationships/vertexes between the nodes. Then I used Gephi to visualize the graph with different layouts. See some different output.

All the parents and children of the View, SYS.DBA_OBJECTS, and the Synonym, PUBLIC.DBA_OBJECTS.

Yifan Hu Layout of DBA_OBJECTS

Yifan Hu Layout of DBA_OBJECTS

Using the Yifan Hu layout radiates all the nodes. 

Using the DAG layout shows the objects more like a tree.

DBA_OBJECTS - DAG LAYOUT

DBA_OBJECTS – DAG LAYOUT

As you can see, with so many nodes, adding the labels for the owner.object_name becomes very crowded.

The same graph can be rotated.

DBA_OBJECTS - DAG Layout, Rotated

DBA_OBJECTS – DAG Layout, Rotated

Highlighting the parents of the View, SYS.DBA_OBJECTS:

Parents of the View, SYS.DBA_OBJECTS, DAG Layout

Parents of the View, SYS.DBA_OBJECTS, DAG Layout

Highlighting the parents and the children of the Synonym, PUBLIC.DBA_OBJECTS. DAG layout.

Parents and children of the Synonym, PUBLIC.DBA_OBJECTS. DAG layout.

Parents and children of the Synonym, PUBLIC.DBA_OBJECTS. DAG layout.

or:

Parents and children of the Synonym, PUBLIC.DBA_OBJECTS. DAG layout.

Parents and children of the Synonym, PUBLIC.DBA_OBJECTS. DAG layout.

Using my script, fchild_tree.sql, in SQLPLUS, for DBA_OBJECTS, I get the children:

CHILD_LVL_OBJID_ROWNUM
——————————————————————
SYNONYM  PUBLIC.DBA_OBJECTS    1   3193   1
PACKAGE BODY  WMSYS.LTUTIL     2   13453   2
PACKAGE BODY  WMSYS.LTDTRG     2   13458   3
PACKAGE BODY  WMSYS.WM_DDL_UTIL   2   13460   4
PACKAGE BODY  CTXSYS.DRICON   2   55733   5
PACKAGE BODY  CTXSYS.DRIUTL   2   55758   6
PACKAGE BODY  OLAPSYS.CWM2_OLAP_UTILITY   2   65353   7
VIEW    SYS.DBA_INVALID_OBJECTS   1   3194   8
SYNONYM  PUBLIC.DBA_INVALID_OBJECTS   2   3195   9
PROCEDURE  SYS.VALIDATE_SDO   2   65640   10

What do you think? Does the graph look better than the text? Which communicates more?

About these ads

2 Responses to DBA_OBJECTS Tree – Modelled As A Graph in NEO4J, Visualized With Gephi

  1. […] Here's how you can visualize a tree of DBA_OBJECTS (“@rodgersnotes: Using NEO4J and Gephi. After retrieving data dictionary metadata from Oracle.  […]

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: