Database Upgrade

This year our project is finally upgrading the database to Oracle 10g. Our DBAs initially decided to go with version 10.2.0.3 for the back end. However we have found some problems during testing. The database is automatically gathering statistics at inopportune times. This is causing some performance issues. I would think you could control this with some parameter.

There are some other problems with bulk loading of data. Our DBAs have been in contact with Oracle Corporation. And they have identified the issues as bugs. The resolution is to go to version 10.2.0.4. That sounds find and dandy. However we now have to go through our performance testing again. I hope this patch resolves these problems. And I pray that there are no new problems introduced with the patches.

You have to keep up with current versions of software. I understand this. Being a software developer myself, I know our users need upgrades all the time. However it hurts being the consumer of some products that need upgrading. There is pain involved with the upgrade itself. Part of this pain is dealing with things that are broken. You also do not want to stay on the bleeding edge. Otherwise you might bleed yourself with the results.

I would like to think that Oracle 10g is stable enough at this point. Don’t they already have an Oracle 11g out there now?

Password Reset Script

The project I work on implements a number of security rules to match our client’s policies. One of them is that you cannot reuse a password unless you have chosen a number of intermediate passwords in between. I won’t go into the exact number of different passwords you must choose before being able to reuse one. Let’s just assume it is 50 intermediate passwords before reusing one.

I have a lot of database accounts in the development and test environment. It is a challenge to remember the passwords for all of them. So I choose a passwords format that makes it easy for me to remember them. This plan gets foiled every time I am forced to change my password for whatever reason. At that point I cannot change my password back to the well known format. I must have a lot of different passwords before reusing the one I know.

Since I am a developer, I figure I can write a script to help me with this problem. I loop through 50 iterations. I use a sequence to generate a random sequence. Then I ALTER USER to change the password in the loop. At the end, I change the password back to the one I want to reuse. I bang out this code really quickly. I am pleased when the script executes to completion. However, the script fails when it tries to reuse my password. I get an ORA-28007: “the password cannot be reused” error.

This perplexes me for a while. I am successfully changing my password to a bunch of unique values for 50 times. So I should be able to reuse the old one. I guess that maybe somebody has messed up the profile which restricts password reuse. No. The profile still says I can reuse after 50 different passwords are used. Then I see the problem. In addition to the PASSWORD_RESUSE_MAX parameter that makes me choose 50 intermediate passwords, there is also a PASSWORD_REUSE_TIME parameter that prevents me from reusing the same password too soon. I had forgot about that rule.

A true hacker does not give up so easily. I was trying to emulate a user switching the password a bunch of times. That was the wrong approach. Instead I need to switch my profile to a different one, such as the DEFAULT. Then I can bypass all the password rules. This feels wrong. But you have to do what you have to do. These are development accounts anyway.

Oracle Instant Client

Installing the Oracle client can cause a lot of headaches. As the author of the Software Maintenance blog can attest, the Oracle client is sometimes very tricky. You need the client to access an Oracle database. Historically Oracle has provided a fat client. However there is now an Instant Client that simplifies matters greatly.

The Instant Client is free. It has support for OCI, OCCI, and JDBC connectivity. There are versions of the Instant Client for Microsoft Windows, Mac, Solaris, Linux, and many other operating systems. SQL*Plus is available, but it comes packaged separately.

The great thing about the Instant Client is that you just have to unzip it. There is not Oracle Universal Installer to configure it. Just point your ORACLE_HOME to the directory where you have unzipped the files. The Instant Client is the ultimate thin client for Oracle connectivity. I might try to get our project to use this. We have our own set of issues with the fat Oracle 10g client.

Oracle Open World

I saw an advertisement in a magazine recently for Oracle Open World. It is being held in San Francisco from September 21 through 25. It has big sponsors such as Intel and HP. It also has smaller sponsors like Computer Sciences Corporation. However there was one problem with the ad. I was not exactly sure what this conference was about.

Sure it is somehow revolving around Oracle. But what does the “open” refer to? Is it open source? I doubt it. I went to the web site and found the conference covered applications, databases, middleware, and industries. This did not narrow the field down much. I also saw some information that I would be able to connect with both experts and peers at the conference. Unfortunately this is still pretty generic.

I did get the impression that this conference was not specific to Oracle developers. I really wish they had a buzz line for the conference. Something like “Oracle Open World – Running Oracle on Linux System” would be helpful.

Oracle has hopped onto the environmental trend by having a Green Program within the conference. They are going to review green components, if that makes any sense. There shall be a review of green practices in general. Their motto is to rethink, reduce, reuse, and recycle. At this point I cannot tell for sure. However I suspect this is really just a lot of lip service to capitalize on the popularity of the environmental movement.

The cost for Oracle Open World is between $2000 and $2500, depending on how early you register. I have an interest in the Oracle database. Our customer has huge amount of data stored in one. Some of our code is PL/SQL stored procedures. I might be a good candidate to attend this conference. The promoters of this conference have not done a good job of telling what exactly this conference is about. So they have lost at least one customer.

Trigger Problems

I read this month’s Tom Kyte column in Oracle magazine with interest. He focused on his strong recommendation for PL/SQL developers to skip triggers. This is because triggers cause maintenance problems. They contain side effects. And people usually forget about the logic contained in triggers when reviewing system behavior. Tom believes that triggers are almost always implemented incorrectly. They make systems hard to understand.

One of Tom’s main cautions is to not perform operations in a trigger than cannot be rolled back. For example, pretty must all of the util_file package operations cannot be rolled back. So if you have then in a trigger, you are waiting for a problem to happen. Tom says that the Oracle mutating table constraint is there to protect the programmer against themselves. Tom believes that a trigger which enforces referential integrity is very suspicious.

The system I work on has a lot of triggers in it. This alone should not be reason for caution. However when a respected figure such as Tom Kyte warns you about a certain database technology, I figure we should take heed. Most of the triggers in our system perform some type of logging or auditing. However there are some triggers which propagate copies of data to tables which are not normalized. And we have other triggers which keep meta data (like counts) up to date.

Luckily all of the operations performed in our triggers can be rolled back. We do not normally fool around with autonomous transactions in our triggers. Nor do we perform file operations that also cannot be rolled back. In essence, you do not have to study the triggers to figure out what most of our system does.

SQL Developer Update

I was reading the latest Oracle magazine. Sue Harper’s column discussed version 1.5.1 of Oracle SQL Developer. She states that this version adds a number of features such as:

* Export utilities
* Generates HTML documentation from schema
* Ability to copy objects between schemas

As you probably already know, SQL Developer is a graphical tool from Oracle for database development. It allows you to browse database objects, run SQL, and debug PL/SQL among other things. It requires an Oracle database version of 9.2.0.1 or greater. There are versions of this application for Microsoft Windows, Linux, and the Mac. It is a free tool.

Version 1.5 of SQL Developer added a number of new features to the product:

* Store code templates for reuse
* Refactor the code
* View Java code
* Perform schema difference
* Import Excel spreadsheet to create tables
* Open trace files for performance tuning
* Run a report to monitor sessions
* Use CVS and Subversion source code control

Version 1.5.1 is a patch. It is available for Windows XP and Vista. It ships with and requires the JDK version 1.5. This patch makes SQL Developers support newer versions of Sybase.

I have to say that I already use an old version of PL/SQL Developer from Allround Automations, which is a different product than Oracle’s PL/SQL Developer. And for some tasks I actually use Oracle SQL*Plus. My first impression of Oracle’s PL/SQL Developer was that it did not feel natural to use. However I have heard some good things on the street about the product. When I decide to upgrade to a new tool, I will definitely be giving this one another look since it comes from Oracle and is free.