Automatic Documenation

How can you create an Entity Relationship diagram automatically from the structure of the Oracle database? Other databases have tools to do this such as PostgresSQL Autodoc and MySQL Workbench.

I got some advice from some people for products to try. One of them is Schema Spy. Another is Enterprise Architect. A final one is DB Visualizer. Right now I use tools to execute SQL. Somebody else on the database team generates documentation from the database. However I don’t think they use any specialized tools. I think they either user Oracle tools, or some custom written scripts to do the work.

Our documentation is mainly text information on the database. This is normally based on the data dictionary. But it would be nice to have some fancier ER diagrams to use and show off. It might be time to ask for some new software to be purchased.

Oracle Alert Log

The other day, our customer reported that some functions in our system were slow. Others were just plain hung. This issue went to our DBA Team. They asked me about some of the functionality that was involved. I provided them the overall flow, as well as the major database tables involved. Then I went back to my normal work.

Our manager held a conference call to work the issue. I share a room with the manager, so I overheard some of the talk. My manager was interested in the DBAs looking at the Oracle Alert Log. Now I have heard this term before. But I did not know exactly what it was. Time for a little research.

The alert log is a text file containing messages and errors. Specifically it will contain ORA-00600 deadlock errors. It also contains database startup and shutdown events. The filename itself is alert_.log. The location of the file is governed by parameter background_dump_test. Go figure.

Apparently the alert log is the first place a DBA should look when there are database problems. In fact, a proactive DBA will be monitoring this file a couple times a day. One interesting fact is that you can write your own messages to the alert log using the ksdwrt procedure in the dbms_system package.

If you have Oracle 11g, the alert log can be directly queried using the X$DBGALERTTEXT fixed table. However you must connect as SYSDBA to access this table like you would any other fixed table. Finally I should mention that Oracle itself computes a number of metrics based on the contents of the alert log. Now I am at the beginning of my journey to becoming a DBA. I just don’t know if that is where I want to go.

Disabling Constraints

My boss told me to go help a teammate out in replicating a problem found by our customer. I worked with her to look at the code. We needed to set up some very specific data to make the condition occur. I told her we should execute some SQL to generate the new records required.

At first we wanted to clone an existing record. She told me that some triggers were preventing that. So I had her disable all the triggers on a few tables. The syntax for that command was straight forward:

ALTER TABLE my_table DISABLE ALL TRIGGERS;

Then it came to disable the constraints. She tried a similar command. However we could not figure out the syntax to disable all constraints in a single commend. Later I found out that the command does not exist. You need to disable each constraint manually, one at a time. Or you could write a script that finds all the constraints and disables them one at a time automatically. Come on Oracle. Can't you make it easy for us? We were in a hurry.

Stored Procedures

Recently I read two blog posts about stored procedures. Personally I like writing stored procs. However I am sometimes hesitant to do so because of the difficulty in releasing the software to clients. It is simple to write all the code in C++. We have an single installer which deploys the application on the workstation. If I include part of my solution in a stored procedure, I need to make sure the client and back end stored procedures are in sync. That is one extra headache that makes me choose the easier route more times than not.

But let’s get back to the debate on stored procedures. One author stated that placing code in a stored procedure does not give you a performance advantage per se. That is because normal queries from a client get cached and have equal performance. I am not sure if I agree. A stored procedure which has been compiled into the database is going to have some inherent benefits for performance, especially if there is a lot of SQL involved.

Stored procedures stop injection attacks. They also enforce data integrity if you choose to do so. I recall from my Oracle programming class that you should use constraints to enforce integrity, not stored procs. However that might be a personal decision. One author said that you should not code business logic within a stored procedure. Again I don’t think I agree. Your business layer can be at the stored procedure level.

When you do code logic in a stored procedure, you are normally locked into a particular database vendor. That’s not a problem on my current project. We are an Oracle shop, and will most likely be so until the end of time. One benefit with stored procedures are that you can have fine control over the permissions on who can execute a stored procedure. That is one ability that I like.

Export Import

Our testing team found some problems with our latest software delivery. I needed to fix those problems quickly. The first step to correcting a problem is to replicate it. Unfortunately the testing team does all their work in their own database. Luckily I have an account in that database.

I wanted to replicate a bug discovered by a senior tester. So at first I spied on the data for the transaction. My eye could not detect anything unusual. Therefore I decided to copy the data into my own development database. I figured the best way to do this was to export the data of interest as SQL insert scripts, which later could be run.

My PL/SQL Developer tool supported the export of data as SQL script. I selected the tables I wanted. After inputting the WHERE clause, PL/SQL Developer generated SQL insert scripts for me. However when I tried to execute them with Oracle SQL*Plus, I kept getting errors. Apparently there is a 256 character limit per line of script using SQL*Plus. PL/SQL Developer had created scripts with very long lines. I manually broke those lines up into short lines to placate SQL*Plus.

There has to be an easier way than that. Perhaps I could execute the SQL inserts from select statements right in SQL*Plus. Normally I don't do things that affect more than one database at a time. However I can do things through database links I guess. There should be an option within PL/SQL Developer that controls output line length, right? Let me know if you have any other ideas for this problem. It stinks to have to manually format script files.