Give 'em the finger

Saw an interesting piece of technology this week. You know those little cards you get at the supermarket that give you the sale price on advertised items? Well, the local Piggly Wiggly has gone high tech. When you sign up for that card you can tie it to a debit/credit card and they'll also scan your fingerprint. You pick up your groceries, proceed to the checkout, and scan you fingerprint to checkout.

Traffic and Taxes

I've been in traffic hell the last two days. Friday I left work to go home, pack the car, and take off for 10 days on Kiawah Island near Charleston, South Carolina. Kiawah is our most favorite spot on Earth and we always look forward to winding down on the beach. I headed for home, but it took me 75 minutes to go 15 miles. I passed three fender benders which caused the traffic. Outside of Washington DC, we hit three more accidents that made the long drive even longer.

As I was sitting in traffic, I came up with two ideas. First, I think the driver at fault in a fender bender should have to pay the state a $3000 fine. You could call this a nuisance tax, a traffic tax, or a whatever tax. Either way, it comes directly out of the driver's pocket and would not be covered by insurance. This money, in turn, goes directly into the state's transportation budget and the state reduces the amount everybody else pays on gas by reducing the taxes. The person that caused the accident then gives a little back to the rest of us that were stuck in traffic while they were doing their lipstick going 75mph. If no fault can be assessed, then that's OK too.

Another idea that I came up with was I should be able to take the time I am sitting in traffic off my income taxes. Think about it for a second. The state AND federal government are responsible for having inadequate roadways that can't handle the amount of traffic that is on them. If every state were like New Jersey with its ten and twelve lane highways then nobody would take time off their taxes. If every state was like Connecticut or California and people deducted $10k off their taxes every year, the roads would get fixed. In a hurry.

Then again, maybe I had been breathing too much exhaust.

Anything But Windows Revolution

So much for the Linux revolution. Only 4% of my readers use Linux as their desktop of choice (me, counting as 1%). That only slightly beats out MacOS X. How depressing.

Arrr, Hardware

Got home last Friday night and I couldn't connect to my wireless network. It sometimes happens and I have to reboot the WAP and/or router, so I didn't think anything about it. Once everything came back up, my laptop could see the WAP, but I couldn't aquire a network address through DHCP. There was no activity on the WAP so I thought something was screwy with my laptop. I tried my Wife's laptop, saw the WAP, but wasn't able to get a network address. Since both laptops are running Windoz, I figured I'd reboot everything and see what happens. Nothing happens.

I fired up my Linux box and started looking at the network. All seemed fine. I could ping the other hosts on the network, but couldn't ping the WAP (I think I can do that, but by now I am second guessing myself). So I reset both the router and WAP back to factory settings and changed all the static IP numbers on my network. Still no dice. At this point I figured my WAP is toast and I'll have to replace it. I have my eye on that little 4 port router with WAP attached anyway, so it will be a good chance to upgrade my technology. It's 23:30 and I need to go to a wedding tomorrow, so I give up.

While we were away Saturday the power went out at the house. Now, both the router and WAP are on a UPS, so they shouldn't have been affected. My wife fires up her laptop while I am on the desktop and says "When did you fix the wireless network?". WTF? Now it's working. I wonder if the WAP is running embedded Windoz?

Mr. & Mrs. Johnson

Saturday we went on a road trip to Clifton Park, New York (outside Albany) for the wedding of our long-time friend, Denise and her soon-to-be, Randy. It looked like the weather was not going to cooperate as it rained just about the entire way. About 15 minutes before we pulled into Clifton Park, the clouds started breaking and the sun came out. What a perfect day for a wedding.

Denise and I worked together at the computer center when we were in school. Always bubbly and upbeat, you just knew she was one of those special people in the world. Denise also lived with my wife (then gf) when we were in school. I've always known Denise as a strong, independent, easygoing woman. When we met Randy about a year ago, the two seemed inseparable and make each other very happy.

All had a wonderful time at the wedding. The service was very unostentatious with a small bridal party, best man, and Pastor Chuck presiding over the nuptials. The reception was elegant and afforded lots of time to catch-up with friends. My only regret is that I would have liked to see Ann (another college friend) doing the YMCA, but alas, the DJ didn't serve that one up. Maybe that's just an '80s thing.

Just to show how thoughtful the newlyweds are, when we checked in to the Hotel there was a gift-basket containing snacks and drinks.

Congratulations to Mr. & Mrs. Johnson. May your new life be filled with happiness forever.

A New High

$2.899/gal this morning for gas. Over $33 to fill the Honda.

Installing Standard Edition

I installed 10.2.0.1 Enterprise Edition on RHEL 4 a couple weeks ago and got it working with a couple small tweaks to the OS. Yes, I know 10gR2 is not supported on RHEL 4 yet, but by the time I got to production it will be.

A couple days later I got a new project that will require 10.2.0.1 Standard Edition on RHEL 4. Not thinking about it, I used the installer that I downloaded from OTN to install. I chose the custom install (like I always do), de-selected the "Enterprise Options" and click,click,click, I was done.

Once I started the database things got interesting. My banner said:

sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 26 20:31:32 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options

OK, that's not cool. I de-install and re-install thinking maybe I screwed something up. Nope, same thing.

"Surely, someone has run into this problem before", I think to myself. I searched metalink for a while and noticed some bugs with the install so I created a TAR.

Me: I installed SE and came out with this banner. I did this, this, and this. I am seeing this, this, and this, which I think is incorrect.
TAR: Did you install from part # XYZ? There have been issues with that set of disks.
Me: No, I downloade from OTN.
TAR: Oh, install from CD.
Me: Isn't it the same software?
TAR: You downloaded from one of the first OTN downloads. There might have been a problem.
Me: But, I don't have the CD. I was told in TAR ### that CD's aren't ready for shipment.
TAR: create another TAR and tell them you need CDs because you're working on TAR ###.
Me: OK.

I create another TAR and get the CDs shipped to me. I copy from the CD to my local box and install from there. Same thing.

Me: OK, I installed from CD and got the same thing.
TAR: Ship us the installActions log and these other logs.
Me: Here they are.

Wait 3 days.

TAR: The log shows you installed EE. Install SE.
Me: No, I chose "Custom", unchecked the EE options, and installed.
TAR: Install SE.
Me: But that's the way I've done it before with no problems.
TAR: Install SE.
Me: But I don't want OEM, Apache, or any of the other stuff installed.
TAR: That's the way it's been done since Oracle7. That other stuff won't hurt, just install it.
Me: That's not true, but OK.

I saw this was going nowhere, so I closed the TAR. A few minutes later I get an email from Oracle Support saying my TAR has been updated.

TAR: Yeah, you're right. But the way you get around it is install everything for SE and then de-install the options you don't want.

Before Insert

One of my developers came to me with this problem. He is trying to manipulate the data coming into a table by using an INSERT TRIGGER. For the life of me, I can't figure out why this doesn't work (I must be missing something simple):

SQL> drop table xyz
 2  /

Table dropped.

SQL> create table xyz
 2     (
 3      x number(10),
 4      y varchar2(20),
 5      z varchar2(4))
 6  /

Table created.

SQL>
SQL> create or replace trigger xyz_bi
 2     before insert on xyz
 3     for each row
 4  begin
 5
 6     :new.z := '777';
 7
 8  end;
 9  /

Trigger created.

SQL>
SQL> insert into xyz values (1, '123456789','123');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xyz;

        X Y                    Z
---------- -------------------- ----
        1 123456789            777

SQL>

OK, so far so good. The trigger populated the Z value like I expected. However, when I pass in a string that is longer than the Z field, I get:

SQL> insert into xyz values (1, '123456789','123456789');
insert into xyz values (1, '123456789','123456789')
                                      *
ERROR at line 1:
ORA-12899: value too large for column "JEFFH"."XYZ"."Z" (actual: 9, maximum: 4)


SQL>
SQL> commit;

Commit complete.

I can't explain why in a BEFORE INSERT trigger Oracle would care what the length of the string is before the trigger even fires. Any hints?

Before Insert, Part II

Heath Sheehan said...

Row level triggers fire for each row that is affected by the triggering statement. That would imply that all validity checks have to be passed before the triggers fire. Any explicit or implicit data conversions have to result in valid data. Any check constraints have to pass, etc.

If the data isn't valid, the row isn't inserted and there's nothing for which the trigger should fire.


Yeah, but if that's true, why doesn't a NOT NULL constraint elicit the same behaviour?

SQL> drop table xyz
  2  /

Table dropped.

SQL> create table xyz
  2     (
  3      x number(10),
  4      y varchar2(20),
  5      z varchar2(4) not null)
  6  /

Table created.

SQL>
SQL> create or replace trigger xyz_bi
  2     before insert on xyz
  3     for each row
  4  begin
  5
  6     IF :new.z IS NULL THEN
  7        :new.z := '777';
  8     END IF;
  9
 10  end;
 11  /

Trigger created.

SQL>
SQL> insert into xyz values (1, '123456789','123');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xyz;

         X Y                    Z
---------- -------------------- ----
         1 123456789            123

SQL>
SQL> insert into xyz values (1, '123456789',NULL);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL> select * from xyz;

         X Y                    Z
---------- -------------------- ----
         1 123456789            123
         1 123456789            777

SQL>

Job Shock

I read with interest IT Workers Confront 'Job Shock' by Thorton A. May in the latest Computerworld. Mr. May brings up a very valid point that users have extraordinary tools in their hands to do the work that IT Professionals used to do. It brought to mind one of my first programming projects way back in 1989. I was to write a program that pulled data out of an Ingres database, sort and group it in illogical ways, and spit it out on a landscape page. The tool of choice was C and it took me six weeks to write. Today, I'd dump some summarized data in a CSV file and let the user mess with it.

Mr. May goes on to wonder "What if paid IT employment was to steadily disappear?". Good question. I can tell you the people at my first job are still employed. No, they're not writing too many C programs anymore, but web interfaces. They give their users the tools to get their own data. Last I knew, there were just as many people as when I left.

Mr. May goes on to point out how to "save" your fat IT career. All his points are well taken, but the bottom line is your career is your business. If you let you business stagnate, it will die. (If this sounds familiar, thanks for reading).

dbms_stats.alter_database_tab_monitoring

While implementing my new statistics gathering procedure in production, I ran into a snag with permissions.

The very first step in my process is to setup my "analyze" user and create a job that runs every night that turns MONITORING on for any new tables. Sounds simple enough, right?

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.5.0 - Production on Tue Aug 9 21:14:18 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production

SQL> create user analyzer identified by youbetcha
2  temporary tablespace temp
3  default tablespace tools
4  quota unlimited on tools;

User created.

SQL> grant create session to analyzer;

Grant succeeded.

SQL> grant alter session to analyzer;

Grant succeeded.

SQL> grant execute on dbms_stats to analyzer;

Grant succeeded.

OK, that should be enough, right? Let's give it a try.

SQL> connect analyzer/youbetcha
Connected.

SQL> exec dbms_stats.alter_database_tab_monitoring(TRUE);
BEGIN dbms_stats.alter_database_tab_monitoring(TRUE); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 10733
ORA-06512: at "SYS.DBMS_STATS", line 10752
ORA-06512: at line 1

Hmm. I looked at the package spec and find out it's run with Invoker Rights and not Definer's rights. Ah, I must need "ANALYZE ANY" privilege.

SQL> grant analyze any to analyzer;

Grant succeeded.

That must be it, let's try it again.

SQL> connect analyzer/youbetcha
Connected.
SQL> exec dbms_stats.alter_database_tab_monitoring(TRUE);
BEGIN dbms_stats.alter_database_tab_monitoring(TRUE); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 10733
ORA-06512: at "SYS.DBMS_STATS", line 10752
ORA-06512: at line 1

OK, that ain't it. I did some research on Metalink, AskTom, and poured through the docs, but didn't really get anywhere. Then I thought, how about a Trace? I started a level 12 trace and then re-executed the procedure. Under the covers, dbms_stats.alter_database_tab_monitoring just does a "ALTER TABLE xyz MONITORING". I get it, I need "ALTER ANY TABLE".

SQL> connect / as sysdba
Connected.
SQL> grant alter any table to analyzer;

Grant succeeded.

SQL> connect analyzer/youbetcha
Connected.
SQL> exec dbms_stats.alter_database_tab_monitoring(TRUE);

PL/SQL procedure successfully completed.

Simple as that. Now all I have to do is create a dbms_job and run it every day:

SQL> declare
2     lJobNo INTEGER;
3     lJob   VARCHAR2(2222);
4  begin
5
6     lJob := 'dbms_stats.alter_database_tab_monitoring(monitoring=>TRUE);';
7     dbms_job.submit(
8        job=>lJobNo,
9        what=>lJob,
10        next_date=>SYSDATE,
11        interval=>'trunc(sysdate+1)');
12
13     dbms_output.put_line('job submitted: ' || to_char(lJobNo) || '...');
14
15  end;
16  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select job, last_date, last_sec,
2     next_date, next_sec, broken,
3     failures, what
4  from user_jobs;

     JOB LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC B   FAILURES WHAT
---------- --------- -------- --------- -------- - ---------- -------------------------
      23 09-AUG-05 21:39:31 10-AUG-05 00:00:00 N          0 dbms_stats.alter_database_tab_monitoring(monitoring=>TRUE);                                        

That's done, on to the next step!

Thanks Doug

I'd been lurking in Doug Burns' Blog for a couple of weeks and found most of his posts interesting. Interestingly, enough, that last week I put a link on my blog to his so I could share with others. Then he goes and changes blog hosts. Now I have to update my blog links to include his new site. Thanks Doug.

Oracle Job Scheduling

Managing jobs in Oracle 9i and below was a pretty straight forward process; start the job queue processes and submit a PL/SQL Block using dbms_job. If you wanted any level of detail you had to wrap the dbms_job functionality around a package and some other tables. Job scheduling was basic and it generally worked. Any complicated scheduling or interaction with the OS and you were out of luck and had to revert to cron.

I'm not sure what I expected when I started reading Oracle Job Scheduling, by Dr. Tim Hall, a few days ago. I've got a decent handle on dbms_job and have used it extensively for all sorts of maintenance tasks. How different could job scheduling in 10g be? Believe me, it's different.

Dr. Hall explains how the new dbms_scheduler packages works and the details of each call. In addition he explains in detail how the new INTERVAL type works and gives very through examples. Chapter 4 is by far the most valuable chapter as it explain four different methods to schedule dependant jobs. The examples in this chapter are an extension of Tims experience in the real-world implementing solutions. Later on, the book explains about how to monitor the new scheduler and how to view the job logs.

I've got to admit I breezed over the sections on OEM and OS Scheduling. I don't use OEM and there's nothing I really need to know about cron.

This book was a good read. Don't get me wrong, it's no A Dog Year: Twelve Months, Four Dogs and Me. It's a technical book through and through. I would definitely recommend it if you are planning on using the Oracle job scheduler to implement complex business schedules.

Oh, and by about the third time I saw:

-- ****************************************************
-- Copyright 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- ****************************************************

I was ready to barf.

Hiring Round 3

I can't freakin' believe it! You may recall my sagas about finding a new Database Administrator (here and here). Well, it's happened again.

We interviewed about 20 canidates for the open DBA position and narrowed the choice down to two very qualified candidates. Each had their own strengths and weaknesses. It was a difficult choice, but we finally chose one over the other and offered NewDBA2 the position. NewDBA2 thought about it for about an hour before he called us back to accept the position. A start date was scheduled for two weeks from the next Monday. As recently as yesterday, NewDBA2 contacted the recruiter and reiterated his excitement for the new position. In fact, they decided to go to lunch today to celebrate.

Then I got the call. NewDBA2 is sorry to say that he has been offered another position and can't start on Monday. Yes, Monday, three days away.

That's twice in a row. I had two people commit to a job and at the last minute they backed out. Personally, if I don't like something about the job, I just don't take it. Sigh, back to the recruiters.