Category Archives: Internals

Strange behavior in MERGE with sequence NEXTVAL

Today we learned the following: “When using a sequence in a MERGE statement, the nextvalue is generated for all rows both updated and inserted (hence all rows in the source).” Our initial reaction was that this must be a bug, but is it really?

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

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)”.

Isolation Levels by Tom Kyte

I just reread the Oracle Magazine article by Tom Kyte named “On Transaction Isolation Levels” from November 2005. Great article that really explains why your relational database can not be treated as a black box. From the article: “The ANSI/ISO SQL standard defines four levels of transaction isolation, with different possible outcomes for the same […]

Oracle foreign key ≠ referential integrity

I used to believe that implementing a foreign key in Oracle enforced referential integrity. Until today! Until reading this article by Tom Kyte, showing that this isn’t necessarily the truth. And I don’t think very many is aware of this. Yes – it does involve foreign keys allowing NULL values, but is’t that rather common?

Notes on blockdumps reading “Oracle Core” (by Jonathan Lewis)

First of all – This book (Oracle Core – by Jonathan Lewis) is great if you really want to know the Oracle internals. And who doesn’t??? It does take some effort reading, but considering the content it’s very well written. Finally I can throw away my old compendium (from same author – Jonathan Lewis) which […]