Books to read: “Mastering Oracle Trace Data, Second Edition” with Cary Millsap

The “Mastering Oracle Trace Data” from Cary Millsap is a great book about performance in general, and about how to work with trace data. The second edition have several new chapters adding valuable knowledge to both developers and DBAs. This is one of the books that you really should have in your bookshelf.

One of the things you just need to know about Oracle …

A couple of years ago I did ask my developers to start using the varchar2(char) option when creating new tables. In Norway we have some characters in the alphabet (‘Æ’, ‘Ø’, ‘Å’) that uses more than 1 byte of storage (when the database characterset is set to AL32UTF8). Using the default – varchar2(byte) – could then decrease the number of characters we actually end up storing in the database (when storing Norwegian text). Today I just read a tweet from Jonathan Lewis, directing me to one of his older blog posts – “Just in case”. I try to read every blog post from Jonathan, but sometimes it’s hard to keep up. This one I just happened to miss – at least until today. You better not miss it!

The article discusses why you shouldn’t increase your column width (of a varchar2) “just in case”. In my case – it thought me why it was a stupid idea to use varchar2(char) as a default approach. Many varchar2 columns in our databases doesn’t store Norwegian text, and would never contain these variable byte-length characters, hence should never be defined with varchar2(char). I should have thought: “Oracle must have made ‘byte’ the default for a reason”.

Sometimes it’s alright to be LAZY – Part 2


Thanks to my coworkers David Karlsen and Lars Johan Ulveseth for introducing me to the world of Vagrant & SALT, and letting me use some of their work on this.

Earlier post:
Sometimes it’s alright to be LAZY – Part 1

In part 1 we looked at the tools Oracle VirtualBox and Vagrant, and how we could use these tools to easily manage our test databases. In this blog post we’ll take a look at the provisioning tool SALT, and how to use this to easily build our Oracle Vagrant boxes.
Continue Reading »

Paginating over parent table (in one-to-many join)

Just spent an hour of figuring out how to do pagination on the parent table in a one-to-many join.

I did try to google it, but didn’t find any solution on this.
Then I started to play around with the analytic functions, and managed to do this:
Continue Reading »

Sometimes it’s alright to be LAZY – Part 1

Thanks to my coworkers David Karlsen and Lars Johan Ulveseth for introducing me to the world of Vagrant & SALT, and letting me use some of their work on this.

The best system administrators and developers I know are down right lazy. In a good way of course. They are experts in finding ways to work smarter. If they have to do it more than twice they consider to automate the task. I have worked with Oracle more than 15 years, and I don’t know how many times and hours I have spent installing operating systems, Oracle software and creating new databases. Not because I thought it was so darn fun, but because I needed the installation for some reason.

Thanks to tools like VirtualBox, Vagrant, and/or provisioning tools like SALT, the time I have to spend on these task is almost down to zero. If you haven’t heard about these tools yet, you better keep on reading. I’m also doing a presentation at the Tech15 conference in Birmingham, England, in December this year. If you find this interesting and you’re there – please drop by my presentation and say hello!

Continue Reading »

SQL Developer Extension: Keep Alive

A “nice to have” extension for SQL Developer:

Template for creating data owners and application users

How many times haven’t I seen applications logging onto the Oracle database as the schema owner. This is definitely not good practice. And usually by the time the DBA discovers this, it’s “to late” to change. I’ve heard managers say: “It will be to expensive and to much of a risk changing this now”. (Note! The same manager actually argued the same when I asked them to – at least – change the password to something else than the username.)

The best way to actually change this is to talk to and educate your developers. In my company I created a script the developers could use when creating new schema users. The script is meant as a template, and should of course be changed if needed.

In the top of the script the developer chooses a application name, and some other settings:
Continue Reading »

Ora-600 when setting OCSID.DBOP tag with JDBC to a value larger than 29 byte

There is now a patch (17931569) for bug 18155614: “SR 3-9692688071 : Ora-600 when setting OCSID.DBOP tag with JDBC to a value larger than 29 byte

In version the following code fails with an ORA-00600 when ran in SQLPlus:
Continue Reading »