Oakies Blog Aggregator

Data Modeling with SQL Developer

Unlike Open World 2007 there were many database oriented sessions at Oracle Open World 2008. There were many good performance oriented sessions, so many in fact that there were several conflicts in the schedule, and I had to pick one in several time slots that had multiple choices.

One of the more interesting sessions (for me anyway) at OOW 2008 was a session not on database performance, but on data modeling.

The SQL Developer team has been hard at working creating a data modeling plugin for SQL Developer.

This appears to be a very full featured tool, and appears to be the answer to the question "What will replace Oracle Designer?"

While Designer is much more than a data modeling tool, that is one of the core features of the tool, and many folks have used it just for its data modeling capabilities.

The new ERD tool is no lightweight, it is quite full featured from a database modeling and design standpoint.

Some of the features included:

  • Domains generated from data
  • Real logical and physical modeling, not just one model with 2 different names.
  • The ability to reverse engineer several schemas at once and have them appear not only as a master model, but each individually as a sub model.
  • Sub model views may be created on sets of objects as well.
  • The tool can determine all tables related to a table through FKs and create a sub model based on that set.
  • Two forms of notation: Barker and IE
  • Many options for displaying sub/super types (D2k fans rejoice!)
  • Glossary - a predefined set of names. These can be used to enforce naming conventions for entities, tables and relations.
  • Schema comparison with DDL change generation

Also of note, in addition to Oracle schemas can be imported from SQL Server, DB2, or any ODBC connected database.

The repository can be either file based, or database based.
There are two versions of the tool, a plugin to SQL Developer, and a stand alone version. The stand alone version will use only the file based repository.

Now for the bad news.

The release date has not been established. The only release information given was 'sometime in the 2009 calendar year'. As the database repository has not yet been designed, the long time to release is understandable.

And finally, licensing has not been established. It might be free, it might not. If not, at least we can hope for reasonably priced. Personally I thinking having a decent data modeling tool that comes free of charge with SQL Developer would contribute to higher quality databases, as more people would use a real database designer rather than a drawing tool.

There was probably more that didn't make it into my notes.
Suffice it to say this is a great development for data modelers and database designers.

Following a few screen shots taken during the presentation.

Partition-wise dependencies in 10g release 2

Modify partitions without invalidating dependant objects. October 2007 (updated August 2008)

AWR Usage Poll

A number of recent threads in the Oracle-L list have made it pretty clear that Automated Workload Repository (AWR) is a tool that you are expected to use when troubleshooting a database problem.

Never mind the fact that AWR is still a product that is licensed separately from the database, and that a large segment of the Oracle DBA population doesn't seem to realize that. Or that Active Session History (ASH) is part of AWR, and falls under the same license restrictions.

So I conducted a poll regarding the use of AWR. AWR Usage Poll. If you haven't in the AWR Poll, please do so.

While the web site does provide a chart of results, those results don't include the extra comments made by poll takers. You may are may not be able to download all the results, I'm not sure if that is restricted to the poll owner.

Nonetheless, I have compiled the results from a 100 or so respondents in to an Excel workbook, along with a few charts. You may find some of the additional comments of interest as well. AWR Usage Results

Draw your own conclusions regarding these results. I think it interesting to that AWR appears to be quite widely used. Personally I fall into the category of not using it because of the expense. I may work on changing that for a couple of key servers, as AWR is not that expensive, but in a small shop, spending $20k on feature that is not often needed is sometimes a hard sell.

One question I purposely left out was "Do you use AWR even though you have not licensed it"? While it might satisfy the curiosity of some (including me) I didn't want to give any Oracle sales people (or Oracle attorneys for that matter) any reasons to contact me regarding the poll.

In retrospect a good question would have been: "Did you realize AWR/ASH is a separately licensed product?". Too late to add that now, but bringing that up quite often leads to lively discussion.

Another interesting bit was that a few people have extended STATSPACK in some way, even using it on Oracle 10g+. One even mentioned the excellent repository of statspack scripts assembled by Tim Gorman. Tim Gorman's Statspack Scripts

Effective Testing

I’ve been following a number of threads on forums.oracle.com recently – the quality of discussion seems to have improved markedly there since I gave up on it in 2001. Anyway there was a thread about interpreting the AWR report that Oracle provides (at extra cost) and which is very smilar to the Statspack report. The [...]

How I got picked for special attention in Denver International Airport

So my wife Anette and I are on our way home from Tim's wedding.

We flew British Airways both ways. In Copenhagen I told a lady at the BA check-in counter that I might have discovered a way for terrorists to put bombs on planes without being on the plane themselves.

You see, the last couple of times where I have had to change terminals in Heathrow and there's been approximately two hours or less between the flights my bags haven't made it.

So if the bags are onboard a plane but the passenger doesn't show up, they'll pull the bags. But if the bags are delayed they'll let the passenger fly without his luggage.

Everyone knows there have been huge problems with luggage in Heathrow. At one point there was more than 42,000 pieces stacked up. IBM had stopped a DW/BI project without having created even indexes on the Oracle database tables, so every piece of luggage required a full table scan of a rather large table, so it took a while to get over that one.

So I told the lady at the checkin in Kastrup airport, Copenhagen, that there might be a security risk in Heathrow and she said she would relay the information.

Well, apparently she did, because the checkin guy in Denver yesterday suddenly started behaving very strange, went into the backoffice to "do a security check" and marked our boarding cars with the dreaded "ssss" code highlighted in yellow which means "pay very special attention", and which meant that both Anette and I had to go through the new machine that will blow air on you so that it can smell traces of explosives, etc etc.

We're currently in Heathrow, about to board for Copenhagen. I wonder if our bags will make it.

So much for trying to warn the folks about a security problem :-))).


Application Express and E-Business Suite 11i.

I’m just starting an installation of Oracle APEX 3.1.1 into one of our development ebs instances for 2 reasons.

To provide a small applet to a project team
To provide the infrastructure for the dbas to write their own little applets.

I chose this method for the following reasons (unordered)

it’s fairly cool
we know pl/sql much better than java
we [...]

Tim is getting married...

Anette and I are in Denver, Colorado these days, because Tim Gorman is getting married to Lori tonight (Saturday). It's a hot wedding: This is, I think, the 20th day in a row with over 90 degrees Fahrenheit, which makes it the hottest period since 1857 or something like that.

Tim is very well known in the Oracle community: He spent many years inside Oracle where I had the pleasure of communicating a lot with him on the wonderful HELPKERN list there.

He also wrote a couple of books and contributed to the Tales of The OakTable book. Here's his website: http://www.evdbt.com/

Good luck to Lori & Tim! (said the guy on his third marriage...)

Planning for Birmingham….

Or should I say ‘Brum’?

Well, I’ve just been notified that one of my abstract submissions, “Introduction to Locks and Enqueues”, has been accepted by the UKOUG for the 2008 Annual Conference, coming up in December.  I’m really looking forward to it.  This will be my 4th year attending.  It’s also the first year the conference will be expanded to a full 5-day week.  There’s bound to be a ton of great material.  I have to say, even for someone coming from overseas, this conference is well worth your time and money.

See you in Birmingham, er, Brum!

Oracle Locator Express

If you do much work with the Oracle database on Windows, and you have 1+N Oracle homes installed, you've probably lamented the fact that the Oracle Home Switcher is no longer included with Oracle.

I can't recall exactly what the tool was called or which version Oracle was at when it ceased to be a part of the installation. I do know that it doesn't work with 10g+.

A little tool called Oracle Locater Express fills this niche nicely, and it does work with 10g. Sorry, have not yet tried it with 11g.

"Oracle Locator Express"

I've used it for several months without issue, save one minor glitch.

Sometimes Oracle 10g Homes are not displayed properly in the list of Oracle Homes to choose from. Other than that, no complaints


A good day today. I was privileged enough to be at the paper selection day for the UKOUG conference in December 2008. For those who don’t know what happens, and perhaps suspect some sort of elite giving themselves presentation slots, here is roughly how it works. 
Firstly a reasonably large group of reviewers from around the world, [...]