Top 60 Oracle Blogs

Recent comments


How much free space can be reclaimed from a segment?

By Franck Pachot

You have the feeling that your table takes more blocks than it should? Here are the queries I use to quickly check the free space. The idea is to call DBMS_SPACE.SPACE_USAGE and infer the minimum space from the percentages. For example, a block in FS3 (defined as having at least 50 to 75% free space) is supposed to have at least 50% of free space. Of course it can have more, but you don’t know.

Conferences 2018

Here are the conferences where I speak this end of year

Join Methods: Nested Loop, Hash, Sort, Merge, Adaptive

This is an explanation of the join methods, with live demos on very simple cases, reading execution plan statistics, looking at how it is executed. And how to improve the query performance: hints, partitioning, indexing. The goal is to understand, with modern tracing tools (dbms_xplan and SQL Monitoring), the basic core stuff that is involved in all query tuning: the join methods, the estimations that drive the choice between full scan and index access, the transformations that avoid unnecessary joins.

  • POUG 2018 September 07, 2018

POUG 2018

CQRS, Event Sourcing and the Oracle Database

By Franck Pachot

This blog post relates my thoughts when reading about Command Query Responsibility Separation and Event Sourcing, in the context of the Oracle Database (but it can probably apply to any database). We see those terms in the new software architecture diagrams, but they are actually quite old:

Command-Query separation

Command-Query separation was defined by Bertrand Meyer 15 years ago, not for the database but for the Eiffel language. See page 22-44 of Eiffel: a language for software engineering.

Oracle 18c preinstall RPM on RedHat RHEL

By Franck Pachot

The Linux prerequisites for Oracle Database are all documented but using the pre-install rpm makes all things easier. Before 18c, this was easy on Oracle Enterprise Linux (OEL) but not so easy on RedHat (RHEL) where the .rpm had many dependencies on OEL and UEK.
Now that 18c is there to download, there’s also the 18c preinstall rpm and the good news is that it can be run also on RHEL without modification.

This came to my attention on Twitter:

Extended Histograms – 2

Following on from the previous posting which raised the idea of faking a frequency histogram for a column group (extended stats), this is just a brief demonstration of how you can do this. It’s really only a minor variation of something I’ve published before, but it shows how you can use a query to generate a set of values for the histogram and it pulls in a detail about how Oracle generates and stores column group values.

We’ll start with the same table as we had before – two columns which hold only the combinations (‘Y’, ‘N’) or (‘N’, ‘Y’) in a very skewed way, with a requirement to ensure that the optimizer provides an estimate of 1 if a user queries for (‘N’,’N’) … and I’m going to go the extra mile and create a histogram that does the same when the query is for the final possible combination of (‘Y’,’Y’).

Extended Histograms

Today’s little puzzle comes courtesy of the Oracle-L mailing list. A table has two columns (c2 and c3), which contain only the values ‘Y’ and ‘N’, with the following distribution:

select   c2, c3, count(*)
from     t1
group by c2, c3

C C   COUNT(*)
- - ----------
N Y       1994
Y N      71482

2 rows selected.

The puzzle is this – how do you get the optimizer to predict a cardinality of zero (or, using its best approximation, 1) if you execute a query where the predicate is:

where   c2 = 'N' and c3 = 'N'

Here are 4 tests you might try:

All about headroom and mandatory patching before June 2019

This post was triggered upon rereading a blogpost by Mike Dietrich called databases need patched minimum april 2019. Mike’s blogpost makes it clear this is about databases that are connected using database links, and that:
– Newer databases do not need additional patching for this issue (,, 12.2 and newer).
– Recent PSU patches contain a fix for certain older versions (, and
– This means versions and earlier 11.2 versions, and earlier and anything at version 10 or earlier can not be fixed and thus are affected.

But what is the actual issue?

Release Version On-Premises binaries

By Franck Pachot

Good news, the latest Patchset for Oracle 12cR2 (which is not named patchset anymore, is actually called release 18c and numbered is available for download on OTN. It is great because OTN download does not require access to Support and Software Updates. It is available to anybody under the Free Developer License Terms (basically development, testing, prototyping, and demonstrating for an application that is not in production and for non-commercial use). We all complained about the ‘Cloud First’ strategy because we were are eager to download the latest version. But the positive aspect of it is that we have now on OTN a release that has been stabilized after a few release updates. In the past, only the first version of the latest release was available there. Now we have one with many bug fixed.

That demned elusive archive log!


With apologies to Emma Orczy again for stealing a line from “The Scarlet Pimpernel” … </p />

    	  	<div class=

That demned elusive archive log!


With apologies to Emma Orczy again for stealing a line from “The Scarlet Pimpernel” … </p />

    	  	<div class=