Oracle – for when it was like that when you got there

Main menu

Post navigation

A couple of years ago, I wrote an application to reverse-engineer a CRUD matrix for tables in an Oracle database.
I’ve since used it quite a lot for impact analysis and have refined it a fair amount. I’m now happy enough with the new version to let it take it’s first steps into the wider world….where doubtless people will be able to find some of the bugs that I’ve missed.

UPDATE October 2015 – the latest ( and let’s face it, least buggy) version of this application is now avaiable on GitHub.

At this point, if you’re wondering what a CRUD matrix is, you can have a look at the original post here.

Enhancements from the first version

I’ve made a few changes in this version, as well as sorting out a couple of bugs that are just too embarrassing to mention…

Searching has been refined with the use of regular expressions to improve accuracy

Application Components

The application comprises scripts to create the CRUD_OWNER database schema and tables.
There is a database procedure, which trawls through the code to work out the relationships.
Finally there is an xml file we can use to deploy the SQLDeveloper Extension.

A word about where to install

I’m sure that you’re far too sensible to take an unsolicited piece of software off the web and dump it straight into a production environment. You’ll probably also have noticed that, to get an accurate picture of your dependencies, all you need to do is run this code in an environment that has the same structure (i.e. tables and database code) as your production instance.

Database Installation

The first step is to create our application owner – CRUD_OWNER. As part of this, we need to grant SELECT CATALOG ROLE to enable the use of DBMS_METADATA. Therefore, we need to connect as SYS as SYSDBA….

The runtime will obviously depend on how many tables are being processed and how many dependencies there are on each of those tables.
In my experience, this can be anything from a couple of seconds ( on an XE database) to around 15 minutes on a large database ( 850 table, 1200 stored program units).
If you want to monitor progress whilst the procedure is running, you can find the session id before executing the procedure.

Then, once it’s running, you can open another session and check progress :

SELECT module, action
FROM gv$session
WHERE audsid = 1346
/

Running in Debug

If you want to get a bit more information, you can take activate the Conditional Compilation blocks in the code by compiling with the appropriate flags set. If we take one of these blocks as an example :

Running an incremental update

If you want to just capture any changes since the last run, without doing the whole lot again…simply pass the refresh parameter a value of ‘N’ and the procedure will just look for stuff with a LAST_DDL_TIME greater than the last timestamp in crud_run_log.
For example, let’s now create a new function in the HR schema :

Open SQLDeveloper and select the Tools/Preferences menu.
In the tree on the left of the dialog box, open the Database node and select User Defined Extensions.

Defining a new User Defined Extension in SQLDeveloper

Click the Add Row button.
Click in the Type field and select Editor from the drop down.
Click Browse and select the crud.xml file
Click Save, then Click OK.

Enter the location of the xml file

Now re-start SQLDeveloper, when you go back in, you should see a new tab in the tables view …

CRUD, glorious CRUD !

I did wonder whether I should put this onto SourceForge, but, given the distinct absence of comments from my first post, I suspect it’s not exactly widely used and to do so would therefore be something akin to vanity publishing.
If you do feel inclined to give it a whirl please let me know how you get on.
Right now, I’m going to get back to the cricket. England seem to be beating everyone they should lose to and losing to everyone they should beat. At least it’s not dull !

I think there’s a bug in the REGEXP_INSTR. I have a table LOG_FORECAST_UNIT and a separate table UNIT. Your crud matrix for procedure X has the delete from = Y for both tables but there is only a delete statement for LOG_FORECAST_UNIT. I haven’t fully manually parsed your regular expression logic but I’m guessing it is ignoring the underscore. This same error seems to occur in all the REGEXP_INSTR evaluations but I’m just focusing on this one delete to make it simpler.

This is very nice but 1 comment – This gives only direct dependency of all objects to the table. What about following:

Function 1 depends on Table A – This is captured

1) Function 2 Calls Function 1 and thus indirectly dependent of Table A and need to be reported.
2) Package X which has procedure Y that calls Function 2
and this type of nested dependency needs to capture and reported to be complete.

It seems my problem is going to be filtering out false references generated as a result of packages. If I want to generate a CRUD matrix for application workflow X which invokes program unit A.1 in package A, I need to be able to filter out the CRUD generated for package A resulting from program units A.2, A.3 etc. which aren’t called by workflow X. Any thought given to this at all ?

it gets a bit tricky for the simple reason that packages are treated as a single entity in the data dictionary.
Searching through the package code programatically to isolate the individual procedures and functions would be problematic and subject to error.
This is especially true if you do not include the program unit name in the END statement for a particualar procedure and you have nested blocks in the procedure.
In the example you quote, the fact that workflow X invokes a member of package A would show up in the CRUD matrix.
Yes, you do still have to do a bit of work to figure out which particular package member it is, but it does give you a starting point.

I have a got a chance to look at this link, when I am searching for something related to …
Is there any way to get table level & index level CRUD details based on their access to figure out which tables & indexes are in use and not in-use, in a given period?.

Indexes can be tracked usiing “Index usage” option (in Oracle), but again we need to enable this option for all the indexes, that too in production is quite difficult job.

From my understanding, your CRUD matrix will provide all dependencies for a given (or all) tables and what kind of CRUD operation it perform on the table. How this will be extended to know, which tables are actually accessed for what operation and which is not all for a given duration. OR is there any other way to get these details?.

I think that your best bet is probably Auditing ( although I’m not sure if this covers indexes).
There’s a couple of good articles on this topic on the Oracle Base site.
10gR2 auditing is covered here.
The 11g enhancements are here.

If you’re looking at activity within a given timeframe then you may want to consider ASH/AWR capabilities ( provided of course that you have the appropriate licenses).

To this point, I’ve not found line breaks to cause a problem.
As for Dynamic SQL however, this app takes the DBA_DEPENDENCIES tables as it’s starting point.
Dependencies via dynamic sql statements are not recorded in this table.
To demonstrate ( with apologies for the Dynamic Duo theme ) :

Is there any common pattern to the Dynamic SQL you have in mind ? For example, are table names specified as literals in the source code ? Do you use Native Dynamic SQL exclusively (i.e. EXECUTE IMMEDIATE) or do you also have some DBMS_SQL statements ?