ODA - Oracle object Dependencies Analyzer

Monday, April 7, 2014

ODA Oracle object dependencies analyzer. Part 1

I want to familiar you with the ODA. It's a tool for impact analysis of program code on the one side and the database objects on the other side.
You can download the ODA from our ODA site.
The ODA is Java Swing application and requires Java Runtime Environment (JRE 1.6 Version and higher).
Further, you create an Oracle user, which will be created the ODA repository.
The user repository owner must have "CREATE TABLE" and "CREATE SEQUENCE" and unlimited quota size permissions.
Here's the sample of a script:
  -- Create the user
  create user ODA_OWNER
  default tablespace USER
  temporary tablespace TEMP
  profile DEFAULT;
  -- Grant/Revoke role privileges
  grant connect to ODA_OWNER;
  grant resource to ODA_OWNER;
  grant select_catalog_role to ODA_OWNER;
  -- Grant/Revoke system privileges
  grant select any table to ODA_OWNER;

  grant unlimited tablespace to ODA_OWNER;
For the ODA starting you run command:

java -Xms512m -Xmx1256m -jar oda.jar admin

In Login prompt you connect to Oracle user - owner of ODA repository(8 tables and 1 sequence).
At first time the ODA repository created automatically in Login ODA user schema. In future you can drop or create new repository in Login window too.
ODA user must see DBA views. 

You can see the installation details there.

Dependency is always a relationship between objects and a few objects:

  • The program unit use tables, views, others program units
  • The form trigger use DB objects
  • The view look to few tables or views
  • The view used in few program units
For relationships search I can use Oracle dictionary views if this relation there is between DB objects or simple string search in the program units(PL/SQL) or just text(Word,scripts).
Well, that in both these cases, there is something that can help us.
In first case there are DB views(PUBLIC_DEPENDENCIES).
In second case the search more difficult, but we have all database object's list downloaded into the ODA repository and if text search found database object name in the text we know what's type of this object and we can understand what's kind of dependency we found.

So, our first step - to download  metadata of database objects.

No comments:

Post a Comment