Dealing with Oracle timestamp with timezone conversion

The Oracle scheduler stores dates as “timestamp with time zone”. This can cause some people to get confused when querying the data based on time. Here’s how you can help them.


Oracle RAC install reboot after Clusterware install

As a matter of course I’ve always stopped clusterware and rebooted each node after doing the clusterware install. I do this just to know that everything restarts properly before I move on with the rest of the installs. I was in a rush on a R&D system and I skipped that step. I ran into countless unusual issues that I’d never seen before from nodes not showing up in the node list for rdbms install, db installer reporting listeners weren’t there, and other odd things. Finally at dbca db create I hit and error about the listeners that I just couldn’t get past. As a last resort I stopped clusterware and rebooted. Then dbca worked fine. I will never skip that step again, in fact, I’m wondering if I’d just forgotten that is a required step (might have to check the docs).

Creating an Oracle job that runs an OS executable

There are several sites that explain quickly how to run an external OS job from the Oracle Job Scheduler, but I found several things that none of them covered that I needed for it to work in the real world. Click here to see what I did and some extras.

Report on OEM Alert metrics

My director asked for metrics on alerts that our team gets from Oracle Enterprise Manager Grid Control. Oracle definitely has an interesting, if not obfuscated, schema design in the Enterprise Manager repository. I was able to find enough information to gather a fair bit of useful information and what I needed for the request. Read more…

How to reference a shell variable named by another shell variable

Have you ever had the need to get the value of a shell variable, but the name was all, or partly, determined by another shell variable. That happened to me this week and once you know how to do it it is pretty simple, but it is hard to figure it out.

I’ll try to clarify. I have a shell variable that has been set with a password. To find that variable I have another variable. The second variable is the User Name and is called USER. The first is the password and is called USERPW. (Note: I’m not saying this is the best way to handle passwords, it is what I had to work with.)

In a given situation I’ll need the password for a user that is provided by the variable USER. I thought I could get this in a variety of ways:



To end the story and give you what you came looking for. Here is the solution (in a test script):

->cat deref.ksh
export USERPW=
. /somewhere/ # this script sets environment variables with passwords
echo echo deference
echo ${USER}
echo ${USERPW}
sqlplus -s ${USER}/${USERPW} << eof
show user

Here is the output that shows it worked. The last line is the output from the Oracle database.

echo deference

Oracle Shrink Space, don’t do it just once

I’ve always noticed shrinking a table more than once can benefit you, this was the first I’ve seen it makes things worse, and then better, read on here…

Unique constraints, NULL, and where SQL got it wrong (imho)

Most databases allow NULL values in a unique constraint. I like this because sometimes you really do need to do that. The problem is that they don’t consider NULL as a unique value in that constraint. That means that you can have multiple rows with the same “values” (I know, NULL is not really a value), but in some uses it is.

I have a table with four columns that need to be maintained as unique, but where some of the columns could be NULL indicating a total lack of value for that column. Due to the proprietary nature I can’t give the exact table details.

The columns are parent_id, metric_id, object_id, item_id

parent_id and metric_id are NOT NULL, they must have a value. object_id and item_id could be NULL indicating a “default” value – in other words if you want to lookup something and you can’t find a match on all four columns, you can search for a partial match where object_id and/or item_id are NULL. Objects and items have foreign keys to their parent tables so they have to have valid values, I can’t just put an empty value in there.

This means I can have

parent_id    metric_id       object_id    item_id
1            10              2123         39392
1            11              NULL         NULL
1            11              NULL         NULL

and that is not valid because I now have two “duplicate” values. I’m trying to think of a reason you’d ever want to allow that type of duplicate.

From what I’ve read SQL Server would not allow this, so kudos to SQL Server.

Feel free to add your thoughts or ideas.