Why can't I add a unique index?

Adding unique indexes once data is already loaded into a table can prove to be a bit of a headache. The question of how to clean up duplicate keys is one that I have had posed to me many times and it seems somewhat simple. I guess anything is simple if you already know how to do it, so to that end, I thought I would write my solution.

This became an issue again recently because the UNIX admin where I work was creating a database to store system statistics. He has daily flat files that he wants to load and knew the basics of creating a database. As he was loading the data it occurred to him that he did not want to be able to load the same file twice and he decided to try to add a unique index. Funny thing was that what he was trying to prevent had already happened and he received an error trying to create the unique index. His next stop was my desk to ask why the error was happening and how to fix it.

The first thing we needed to do was to figure out which rows were duplicated. The following SQL gave us the date that was loaded twice:

select collection_date, server, count(*)
from system_stats
group by collection_date, server
having count(*) > 1;

This gave us a list of machines for one date with 2 rows each. Now we knew what date was loaded twice, but since they were exactly the same, how do we get rid of one row for each key? This took creating a temporary table to hold the records we didn't want to delete.

create table hold_records as
select distinct * from system_stats
where collection_date = ;

Checking the hold_records table shows us that we have exactly half the number of records for that date and each server only has one record for that date. Now we clean up the data for that date from the main table.

delete system_stats where collection_date = ;

Now we put the data back:

insert into system_stats (select * from hold_records);

Voila! One record per server for that date and the unique index could be created. Again, this is not really rocket science, but I am surprised at how many times I have been asked to do something similar. Hopefully it will save someone else some time in the future.

More Manageable or Different Management?

With every new release of the Oracle Database, we are told that there are new manageability features to make our lives easier. Larry Ellison even told managers at one point that they wouldn't need a DBA anymore. I have been working on implementing the PeopleSoft Plug-in for Enterprise Manager and wrote a paper about it for Collaborate. I am presenting the paper again at OpenWorld and have been updating it with new information. This is where the question that is the title of this entry comes in. Do these tools make things more manageable or just force us to focus on a different kind of management?

Implementing management tools is only half the story. If monitoring was as easy as run setup and you're done, I wouldn't have needed to write a paper or do two versions of the presentation. What to monitor, what to alarm on, what level to alarm at, and who needs to be notified based on what the alarm is... All this functionality is built into the tools, but figuring out exactly how to set it all up is the trick. On some level I feel like writing shell scripts might be easier. Tools become a better bet when there are changes to the environment. Having one location to go to add new environments or take care of changes, does start to make tools worth while.

As with most application implementations, that is only half the story. Security has also been a challenge to set up because of the normal implementation of Enterprise Manager. Extending one tool, that is usually used for one purpose, in this case the database monitoring, to include other purposes, such as application monitoring, brings to light many short comings in the original implementation. In our case, our DBA's were Super Adminstrators with access to all targets in Enterprise Manager. Of course, from an application adminstration perspective, this causes separation of duties issues. So back to the security drawing board. Once this is designed properly, it is not that difficult to maintain, but building the proper design takes time.

Bottom line for me is that I still believe that monitoring tools and new manageability features make our lives easier in the long run. The moral of the story (and my presentation) is to plan up front and make sure you have a good design in place. I'm still working on my implementation because I didn't know what I didn't know when I started. Hopefully my paper and presentation can save someone some time. Even more interesting will be the new manageability features introduced in Oracle Database 11g Release 2. Hopefully more on those to come...

IOUG at Oracle OpenWorld

So we are about nine weeks out from Oracle OpenWorld 2009 and planning is well under way for the Users Groups. Several years ago, the Sunday before OpenWorld opens became Users Group Sunday. This year there is more participation than ever and an even higher level of technical content. The Independent Oracle Users Group has seven dedicated rooms and two shared rooms with the Oracle Development Tools User Group. Topics include 11g upgrade, Business Intelligence, Exadata, RAC, Content Management, Spatial Technologies, SAP and much more. Most of the Oracle Users Groups are represented on Sunday so make sure you include the Sunday sessions as you are making your travel reservations for OpenWorld.

Besides the Sunday program, the Users Groups also had an opportunity to choose some of the non-Oracle employee technical content for the regular conference sessions. Hinting, compression, Critical Patch Updates, 11g new features, 11g upgrades, Warehouse Builder and many more sessions were actually chosen by IOUG. Seasoned presenters, authors and Oracle Aces are all included in the line up.

And to top it all off, if you would like to get all of this for free, here is an option if you are lucky enough! Review the new Oracle Support here and be entered to win a free pass.

More details about some sessions of interest as we get closer. Hope to see you all in San Francisco in October!

Funny T-shirt or Life Lesson?

I think most men have that one t-shirt in their dresser that their wife has said, "that t-shirt is falling apart. Why don't you get rid of it?" I think I have three or four, but one of my favorites is an old Embarcadero Technologies shirt that has a picture of a woman dressed in black crying over a casket with someone tapping her on the shoulder. The caption says something to the effect of, "I know this is a bad time, but did he happen to tell you his database admin password?" I always thought it was kind of a funny shirt given how that is always the worst case scenario that we as database administrators can think of. I used to have a manager that would tell us to document our processes and procedure just in case someone got hit by the "chicken truck".

Unfortunately, I experienced this in real life last week. One of my coworkers passed away unexpectedly last weekend. I had just begun to work with him recently and so I really didn't know him that well. He was actually at an Oracle class when he got sick and had to come home. I had been working with him to help him better understand the Oracle database. In fact, I was supposed to be helping him on a new project when he came back to the office. Not surprisingly, there were some tearful eyes and heavy hearts when we all got the news on Monday morning.

Once I got over the initial shock of wondering how someone roughly my same age would go from relatively healthy to passing away, the inevitable question became, "what about supporting the organization during this sad time?" Our organization is split up so the databases that my coworker was supporting, I had never seen before. For the most part, I was able to figure out what was going on and make sure there was enough disk space, backups were running, and most things seemed in order. What we did figure out though is how much documented standards, processes, and procedures would have made the transition easier. One thing I will be discussing with management is consolidation of tasks and a set of standards that can be documented and shared with the team.

The moral of the story is pretty much what most of us already know. Document everything and make sure that the documentation and passwords are in a safe, secure place that is accessible to those that need them.

God speed Kurt... you will be missed.