Category Archives: SQL

The NUMBER datatype without precision

Lately I have seen projects using the NUMBER datatype for table columns without setting precision and scale. When I see this I always question “Is this intentional or did they just happen to forget to set the values?”. This is the problem with using implicit configuration/settings. You don’t really know if it was a mistake […]

Redundant NOT NULLs in DDL

I often see DDLs with NOT NULL definitions which is really redundant. When I see such DDLs I really question “do the writer really know what he or she is doing”. They might not hurt you application, but why set them if they are redundant? (If you know I reason please comment) Example: CREATE TABLE […]

Nice blog about DBMS_METADATA from Alex Nuijten

This blog post has been laying in my TO_READ folder for a long time. Finally found some time to catch up, and I especially liked the little piece of PL/SQL for remapping the schema.

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! […]

Oracle 11g: New Locking behavior (modes) … an unexpected behavior. Or?

I just noticed some strange locking behavior in one of our production databases. It took me a while to reproduce this in test. I’ve based my example on Richard Foots blog “Oracle11g: New Locking Modes When Policing FK Constraints (A Wolf at the Door)”.

Article: Faster Batch Processing and Logging by Mark rittman

I just read this article about efficient error handling when running batch jobs. This is somethink many DBAs and developers should put on their mind, and consider using next time they’re planning to do dataloads or batch jobs.

Oracle and Hibernate: Not always best friends

I’m definitely not a Java or Hibernate expert. But I have worked with Oracle for the last 15 years since version 7.3.4. I’ve also spend the last 10 years focusing and working mainly with performance issues within Oracle databases or within the code accessing Oracle databases. Lately many of the projects I’ve been working on […]

Select on comma-separated list

I’ve been busy working on a Oracle 11g upgrade. When working on different issues you always stumble over some interesting blogs. This time I found an article about how to select on a comma-separated list. This is worth making a note of. I have not tested the performance of this yet, but will do so […]